locked
Reading Date values from Spreadsheets using OpenXml RRS feed

  • Question

  • User662694675 posted

    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?


    Wednesday, December 22, 2010 2:01 AM

Answers

  • User269602965 posted

     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.

     

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 25, 2010 8:33 PM