Reading a date from xlsx using open xml sdk

c# date datetime openxml openxml-sdk
Santhos picture Santhos · Nov 1, 2012 · Viewed 26.9k times · Source

I have a date in format "4/5/2011" (month/day/year) in a xlsx file in one of the cells. Im trying to parse the file and load those data in some classes.

So far the part where I parse the cell looks like this:

string cellValue = cell.InnerText;
if (cell.DataType != null)
{
    switch (cell.DataType.Value)
    {
        case CellValues.SharedString:
            // get string from shared string table
            cellValue = this.GetStringFromSharedStringTable(int.Parse(cellValue));
            break;
    }
}

I hoped that date would be a cell.DataType. The truth is when parsing the cell with the date "4/5/2011", the value of cell.DataType is null and the value of the cell is "40638" and it is not an index to the shared string table. (I have tried that before and it ended up with an exception.)

Any ideas? Thanks

Answer

Vincent Tan picture Vincent Tan · Nov 1, 2012

Open XML stores dates as the number of days from 1 Jan 1900. Well, skipping the incorrect 29 Feb 1900 as a valid day. You should be able to find out algorithms to help you calculate the correct value. I believe some developers use DateTime.FromOADate() as a helper.

Also, the Cell class has the DataType property as Number by default. So if it's null, it's a number, which includes dates in our case.

You only go to the shared strings table when the date stored is before the epoch (1 Jan 1900 in this case). And then in that case, the CellValue of the Cell class holds the index to the shared string table.

Related questions

Datetime in C# add days

I want to add days in some date. I have a code like this: DateTime endDate = Convert.ToDateTime(this.txtStartDate.Text); Int64 addedDays = Convert.ToInt64(txtDaysSupp.Text); endDate.AddDays(addedDays); DateTime end = endDate; this.txtEndDate.Text = end.ToShortDateString(); But this …

Read More
DateTime.TryParse issue with dates of yyyy-dd-MM format

I have the following date in string format "2011-29-01 12:00 am" . Now I am trying to convert that to datetime format with the following code: DateTime.TryParse(dateTime, out dt); But I am alwayws getting dt as {1/1/0001 12:00:00 AM} , Can you …

Read More
Get the previous month's first and last day dates in c#

I can't think of an easy one or two liner that would get the previous months first day and last day. I am LINQ-ifying a survey web app, and they squeezed a new requirement in. The survey must include all …

Read More