CODEWITHSUNDEEP
c#exceldatetimeopenxmlopenxml-sdk
Nikhil
Nikhil

Reputation: 604

How to Distinguish date and other numeric formats (like number/currency) in open xml sdk SpreadSheet?

I am reading xlsx file using OpenXML SDK. There is no issue while reading shared string values but I don't know how to distinguish the actual numbers from the dates as both of them have DataType of null and they are stored as number.

Using DateTime.FromOADate() I can convert numberic value into date format but first I need to identify whether cell format is date or not, otherwise number/currency formatted cell value would also get converted into date.

I tried solution mentioned in this post How to distinguish inline numbers from OLE Automation date numbers in OpenXML SpreadSheet?. but it is not working for currency format,number format (negative number formatting please see attached screenshot) and some custom formats number and currency format screenshot.It would convert currency and number formats into date

I am looking for

  1. Generic solution to distinguish date from all other numeric formats so that all the build in formats (like number,currency,Scientific) and custom formatted numeric values would not be converted into date

  2. Is there generic way to format cell values as per formatting applied in excel sheet.Right now we are handling each formatting separately

Upvotes: 1

Views: 1586

Answers (1)

Israel Cortés
Israel Cortés

Reputation: 69

Each cell has 2 properties r (CellReference) and s(StyleIndex)

StyleIndex for numbers is 2 and for date is 3

33938

Upvotes: 2

Related Questions