How do excel cells have to be formatted to be read as cell_type [:datetime] by Roo?

Question

I am trying to read DateTime objects from an Excel spreadsheet using the Roo gem. I am able to successfully read cells that are formatted as Dates with the type *3/14/15 (with the asterisk denoting that the format is based on my regional date and time settings). When I check the cell_type of those cells as they are imported, the type is [:numeric_or_formula, "mm-dd-yy"]. I would expect these to have a type of [:datetime], but I am still able to get a value in mm-dd-yy format from cell.formatted_value.

When I try to read cells that are formatted as dates with type 3/14/15 (that is, the format is not based on regional date and time settings) or 3/14/15 13:30 I get that the cell_type is [:numeric_or_formula, "m/d/yy;@"]. When I call cell.formatted_value I get "0/d/yy;@".

Is there any way to format my excel cells such that they will be read by Roo as being of type :datetime? And if not, why is the type of cell in that Roo creates dependent on the regional date and time settings?

For reference, here's the code I have now for reading cells in from the spreadsheet:

xlsx = Roo::Spreadsheet.open(self.file)

raw_key_list = xlsx.sheet('index_upload').row(4)
key_list = raw_key_list[4..raw_key_list.length].compact

xlsx.sheet('index_upload').each_row_streaming(offset: 6, pad_cells: true) do |row|

  key_list.each_with_index do |key, idx|

    next unless row[idx + 4].try(:cell_value)

    if row[idx + 4].cell_type == [:numeric_or_formula, "mm-dd-yy"]
      context_lookup.context_value_date = row[idx + 4].formatted_value
    end
  end
end

Answer 1

Looks like I can better determine the type of a cell by checking class rather than cell_type. Also for the value I set, I found it worked just as well to use the value of the cell rather than formatted_value - that returns the the DateTime object itself rather than a formatted string of the DateTime.

Here's what the code looks like now:

xlsx = Roo::Spreadsheet.open(self.file)

raw_key_list = xlsx.sheet('index_upload').row(4)
key_list = raw_key_list[4..raw_key_list.length].compact

xlsx.sheet('index_upload').each_row_streaming(offset: 6, pad_cells: true) do |row|
key_list.each_with_index do |key, idx|

      next unless row[idx + 4].try(:cell_value)

      if row[idx + 4].class == Roo::Excelx::Cell::DateTime
        context_lookup.context_value_date = row[idx + 4].value
      end
end