How do I convert a date into a time when parsing an .xls doc using Rails?

Viewed 1686

I'm using Rails 5. I want to parse an .xls (not to be confused with .xlsx doc) using the code below

  book = Roo::Spreadsheet.open(file_location)
  sheet = book.sheet(0)
  text = sheet.to_csv
  csv = CSV.parse(text)

  arr_of_arrs = csv
  text_content = ""
  arr_of_arrs.each do |arr|
    arr.map!{|v| v && v.to_f < 1 && v.to_f > 0 ? TimeFormattingHelper.time_as_str(v.to_f * 24 * 3600 * 1000) : v}
    text_content = "#{text_content}\n#{arr.join("\t")}"
  end

Here is the method I reference above

  def time_as_str(time_in_ms)
    regex = /^(0*:?)*0*/
    Time.at(time_in_ms.to_f/1000).utc.strftime("%H:%M:%S.%1N").sub!(regex, '')
  end

One area I'm having trouble is that a cell that appears in my .xls doc as

24:08:00

is processed as

1904-01-02T00:08:00+00:00

with the code above. How do I parse the value I see on the screen? That is, how do I convert the date value into a time value?

As an example from another Excel doc, the cell that appears as

24:02:00

is getting parsed by my code above as

1899-12-31T00:02:00+00:00
4 Answers
Related