Get raw value of the cell

[iuri-gg]

I have xlsx file that has a date column. The underlying value is a float (has a time component). I want to get the raw value of the cell to convert it to datetime even though the cell is formatted as Date.

I looked through code and could not find a good was to access the cell's raw value. Is there way to return cell's raw value or override the formatting?

[martijn]

There is currently no way of doing this. By design Xsv returns arrays of parsed primitives instead of proxy objects. The parsing for a 'd' type cell happens in

xsv/lib/xsv/sheet_rows_handler.rb

Lines 109 to 110 in e5d18c6

	when "d"
	DateTime.parse(@current_value)

Perhaps you can reverse the effect of DateTime.parse in your code?

[iuri-gg]

I cannot reverse it because I am getting the Date object, not DateTime. Even though the underlying raw value has a time component saved, I am not getting datetime back because the cell is formatted as Date in xlsx. If I open xlsx file and change cell format to DateTime, then I get datetime value from xsv too. However those files are exported from a different system that I don't have control of so I need a way to either to override cell style/format or get access to raw value and convert it to datetime myself.

[martijn]

Ah, I forgot that exists. Indeed what you have is a number formatted as a date. The conversion is done by adding the number value to an epoch.

    EPOCH = Date.new(1899, 12, 30).freeze

    def parse_date(number)
      EPOCH + number
    end

This can be reversed:

irb(main):018:0> excel_value = 123.45
irb(main):019:0> parsed = parse_date(excel_value)
=> #<Date: 1900-05-02 ((2415142j,38880s,0n),+0s,2299161j)>
irb(main):020:0> (parsed-EPOCH).to_f
=> 123.45

I hope that helps! It would be interesting idea to add a middleware pipeline to Xsv to customize the parsing, but I have no time to build that. PR's are always welcome, though!