Reading time values in Excelx in ruby

Question

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.

Answer 1

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

Answer 2

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.