1

I'm making a library that uses OpenXML in C# to read excel files. I can read a cell text and numbers just fine, but when it comes to dates there's a problem. There's the type "date" for the cells, but apparently Excel 2007 doesn't save the dates in that type, so I can't tell if the value I'm reading is a date or not, instead it appears to use styles.

How could I detect if it is a date and return the string representation of it (ex: 29-12-2010)?

Matt Ellen
  • 11,268
  • 4
  • 68
  • 90
jpiolho
  • 1,192
  • 11
  • 12

1 Answers1

4

Excel stores dates as a float value... the integer part being the number of days since 1/1/1900 (or 1/1/1904 depending on which calendar is being used), the fractional part being the proportion of a day (ie the time part)... made slightly more awkward by the fact that 1900 is considered a leap year.

The only thing that differentiates a data from a number is the number format mask. If you can read the format mask, you can use that to identify the value as a date rather than a number... then calculate the date value/formatting from the base date.

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • But doesn't the attribute "s" for dates has always the value of "1"? I know it defines style, but maybe? ;) – brovar Jan 04 '11 at 09:24
  • 2
    The s attribute references a style xf entry in styles.xml, and it won't always be entry 1 for dates... it all depends how many different styles are being used in the workbook. The style xf in turn references a number format mask. To identify a cell that contains a date, you need to perform the style xf -> numberformat lookup, then identify whether that numberformat mask is a date/time numberformat mask (rather than, for example, a percentage or an accounting numberformat mask) – Mark Baker Jan 04 '11 at 09:37
  • One more question - I'm now looking at the style.xml's content and in the section I see elements like: "", "", etc. but there is no section... Are there any "standard" formats? Or am I just missing something? – brovar Jan 10 '11 at 08:57
  • 1
    @brovar, In ECMA-376, Second Edition, Part 1 - Fundamentals And Markup Language Reference section 18.8.30 page 1964 there's a list. Also partially available here: http://stackoverflow.com/questions/4730152/what-indicates-an-office-open-xml-cell-contains-a-date-time-value – Samuel Neff Jan 22 '11 at 04:17
  • The Number formats 0-163 are built-in formats. 164 and above are custom defined formats. The Format Code is used to determine the column formatting. The OpenXML built-in number format Id list is on page 1786-1793 (Footer shows 1776-1783) ECMA-376 Fifth Edition Part-1-Fundamentals And Markup-Language Reference https://www.ecma-international.org/publications-and-standards/standards/ecma-376/ – nimblebit Aug 25 '22 at 15:46