CODEWITHSUNDEEP
rubyxlsxtime-format
Santosh Bt
Santosh Bt

Reputation: 71

Reading time values in Excelx in ruby

I am using 'roo' gem, to read and parse the xlsx file. The file contains a start_time, which may have the user input values in the format of 06:00 AM. When the file is uploaded, I am getting the parameters converted to Sat, 30 Dec 1899 or "1899-12-30". I am wondering whether in any way, we can get the actual time format, the user mentioned in the file or can we convert the available parameter, to the actual file cell value.

Or is there any format or validations for xlsx file cell.

Thanks in advance.

Upvotes: 3

Views: 906

Answers (3)

Fabian Fernandes
Fabian Fernandes

Reputation: 1

Changing excel cell format to text is the option only if you want to convert/parse the excel file once. What if you want the permanent solution? here you go - 

xlsx.excelx_value(row,col)

This will give you the time value as representation of day i.e.

0.25 = 06:00 AM 0.50 = 12:00 PM 0.75 = 18:00 PM and so on

you just need to convert this to minutes i.e. 0.25 * 1440 = 360 minutes = 06:00

You can even convert that into seconds 0.25 * 1440 * 60 * 60 = 21600 seconds and then 

Time.at(seconds).strftime("%H:%M:%S")

Upvotes: 0

Woahdae
Woahdae

Reputation: 5051

I've found that the xlsx reading libraries in Ruby, including Roo, get dates wrong. This is unfortunate but understandable, since excel uses both 'type' xml attributes and formatting rules that are awkward at best, and user-generated at worst.

Still, with effort, it is possible to get right most if not all of the time, which is why I wrote simple_xml_reader. If possible, use it, otherwise your solution will be the most reliable, since by my experience trying to use the previously-existing Ruby gems, they will only get it right a fraction of the time, if at all.

Upvotes: 1

Santosh Bt
Santosh Bt

Reputation: 71

This got resolved, as I changed the excel cell format to Text.

Upvotes: 1

Related Questions