CODEWITHSUNDEEP
javadateapache-poixlsx
Rinat Tainov
Rinat Tainov

Reputation: 1489

Parsing large xlsx file, Date format case

Because xlsx file may contain million rows, I decided to use poi event model, took example from here(ExampleEventUserModel), but found some problem parsing Date format cells.

For example I have in excel date 01.10.2011 in cell A15, but xml has:

<c r="A15" s="11"><v>40817</v></c>

It is not in date format, even not in millis for example.

How to parse date for event model?

Best regards.

Upvotes: 3

Views: 3355

Answers (2)

jmcnamara
jmcnamara

Reputation: 41644

In relation to your follow-up question about how to determine if the cell contains a date value or not the answer is that there isn't any easy way.

The cell, <c>, element has a type attribute t but it isn't used for this Excel serial date type. As such the only thing that distinguishes a cell that contains a date encoded as 40817 and a cell with the value 40817 is the format that is applied to the number (that is referenced via the s cell attibute).

In order to determine that format you would have to look up the numFmtId attribute of the referenced style xf in the associated styles.xml and try to determine if that format is a date format. And in order to do that you would have to apply some heuristics.

All in all this is a huge pain when you wish to differentiate dates from other numeric data and to me this is an oversight in the SpreadsheetML format.

P.S. The OOXML standard does define a d date value for the t type attribute but that is only used for ISO 8601 dates which aren't actually produced by Excel. (Update: this date format is now used in Excel 2013 in strict mode).

Upvotes: 3

Jim Garrison
Jim Garrison

Reputation: 86774

Excel stores dates as a floating point number of days (and fractions of day) from a fixed starting point. The value 40817 is the number of days from that starting point to 1 October 2011.

POI's HSSFCell has a getDateCellValue() method that returns a Java Date, and will do what you want.

Upvotes: 4

Related Questions