Reading a date from xlsx using open xml sdk

问题:

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

回答1:

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.

回答2:

you can use DateTime.FromOADate(41690)

回答3:

I had same issue - switched to EPPlus http://epplus.codeplex.com/

Note that it has LGPL license. So if you need your code base to be safe from GPL issue, simply use the library as is and your original code base license is safe.

回答4:

Adding my 2 pence worth. I am processing a template, so I know a given cell is meant to be a DateTime. So I end up in this method with a string parameter excelDateTime containing the cell value, which typically will be a OADate number like "42540.041666666664".

public static bool TryParseExcelDateTime(string excelDateTimeAsString, out DateTime dateTime)
{
    double oaDateAsDouble;
    if (!double.TryParse(excelDateTimeAsString, out oaDateAsDouble)) //this line is Culture dependent!
        return false;
    //[...]
    dateTime = DateTime.FromOADate(oaDateAsDouble);

My problem is that the end user is in Germany, and because this is a website, we've set the Thread.CurrentThread.CurrentCulture and Thread.CurrentThread.CurrentUICulture to "DE-de". And when you call double.TryParse, it uses the culture to parse the number. So this line: double.TryParse("42540.041666666664", out oaDate) does indeed work, but it returns 42540041666666664 as in Germany the dot is a group separator. DateTime.FromOADate then fails because the number is out of range (minOaDate = -657435.0, maxOaDate = +2958465.99999999).

This make me think that:

1. regardless of the locale on a user's machine, the OpenXML document contains numbers formatted in a default locale (US? invariant? in any case, with the dot as a decimal separator). I've searched, but not found the spec for this.
2. when doing double.TryParse on a potential OADate string, we should do it with double.TryParse(excelDateTimeAsString, NumberStyles.Any, CultureInfo.InvariantCulture, out oaDateAsDouble)). I'm using CultureInfo.InvariantCulture, but it should be whatever point 1 is, which I don't know for sure.

回答5:

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

StyleIndex for numbers is 2 and for date is 3

Date it is in ODate and you can convert to string format

value = DateTime.FromOADate(double.Parse(value)).ToShortDateString();