Reading the cell display data using Roo gem

Question

I am developing a 508 html table parsing the excel files. I want to read the cells data which is being displayed (not the float values as roo spits out). When there are formulas roo gem is not just reading the data after the format is applied. Like here I get float values ex: "90.909090909090907" but the number being displayed on the excel is "91". Its fine if I just get string values of the excel data being displayed.

Thank you in advance.

Answer 1

To get the displayed value(formatted value), use this method

xlsx.formatted_value(row,col)

http://www.rubydoc.info/gems/roo/frames

Answer 2

Same problem as well. Just as an addition to Magesh's answer, if you don't know which cells will be integers you can check by:

s.cell(i, j).to_i if s.cell(i,j).is_a?(Float) && s.cell(i,j).denominator == 1

Obviously you can have some false positives but for the most part it will turn the cells that are supposed to be integers into integers and keep those that are supposed to be floating points.

Answer 3

Check out Roo::Base.cell_to_csv (private) method to get a picture on how to handle spreadsheet's data types.

Answer 4

I had the same problem when i first tried using 'roo' gem. You can fix that by using .to_i,

row = spreadsheet.row(1)
row[0].to_i

Here, the .to_i will change the number from a float representation to normal integer like 91 instead of 91.0 or 91.03030 or whatever it maybe