I am writing C# code to read a sheet from a workbook. A couple of cells in the sheet are of Date type. However, when I check out the xml generated for the sheet, it contains numbers, for e.g. 38018 instead of 1 Feb 2005. As a result my code ends up treating
this as number and I am not able to recreate a date object.
Bhaskar Rao....
Member
2 Points
16 Posts
Reading Date values from Spreadsheets using OpenXml
Dec 22, 2010 06:01 AM|LINK
Hello All,
I am writing C# code to read a sheet from a workbook. A couple of cells in the sheet are of Date type. However, when I check out the xml generated for the sheet, it contains numbers, for e.g. 38018 instead of 1 Feb 2005. As a result my code ends up treating this as number and I am not able to recreate a date object.
Any ideas?
</div>Bachi
Lannie
Contributor
3736 Points
727 Posts
Reading Date values from Spreadsheets using OpenXml
Dec 26, 2010 12:33 AM|LINK
Yes.. the hidden headaches of Excel appear now and then.
Dates are really stored a numbers (just like a database does, but different rules).
Open new sheet, enter 12/31/2010 (US culture) or (31/12/2010 European culture) for 31 Dec 20010.
Well. it looks like a date, but press CTRL+~ (CTRL plus TILDE keys) and it toggles to the number 40543,
the total number of days from 00 Jan 1900 with 01 Jan 1900 being the value of 1 day.
Press CTRL+~ again to restore the FORMATTED appearance of the number back to a date looking data.
Often I find the FORMAT is not exported, but the underlying value is exported
when exporting XLS sheets to TEXT or XML... sometimes unpredictably at least for a person with average Excel skills like me.
Now if you move to another CELL and enter '12/31/2010 (Single Quote + 12/31/2010), the date is stored as TEXT
and still looks like a date, now it can be exported to XML and then converted into date format in a database,
applying the date mask 'MM/DD/YYYY' in SQL statement on data type conversion from XML Text to database DATE.