How do Excel recognize Date cells and how to do the same using OpenXml and C#

Question

I have spent a few hours but still have no satisfying answer.

When I click cell in excel document it is marked as Date field. However, if I use OpenXml to investigate DateType - I will receive Cell object with DataType NULL. I have noticed, that Cell.StyleIndex in this case is set to "116" but I would like to have guarantee, that I will always recognize Date-cell in the same way as Excel can do it. How to get complete list of Data style indexes which indicates Date field?

    public bool IsDateCell(Cell cell)
    {
        switch (cell.StyleIndex.Value)
        {
            case 116: return true;
            case 98: return true;
            case 283: return true;
            case 282: return true;
            default: return false;
        }
    }

i have not worked with c# - but just note that Excel internally stores Dates as normal numbers, i.e. as floats - as the number of days passed since Jan 1st, 1900. So maybe you need to look for this instead... — Peter Albert, Feb 25 '13 at 13:10
But what if DataType is null and value type in cell is not a Date? For example 4000 could not be a time since Jan 1st, 1900 but a normal number? — Borysław Bobulski, Feb 25 '13 at 13:12
I don't know if there's any "this is a date" flag is somewhere, don't think so. you can check, if the number is in a valid range (e.g. 41330 (today) +/- 1000) - and if the format of the cell is date... — Peter Albert, Feb 25 '13 at 13:18

score 0 · Answer 1 · edited May 23 '17 at 10:27

I think that the problem is with how the OPENXML decides what is the type of the cell. At leat I know of a similar problem in OLEDEB. Normally it does so by reading the first few values from the column.

what you can do is either define the format as date before entering the values to excel, or play with HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ -->TypeGuessRows. see:

OleDB & mixed Excel datatypes : missing data

DBNull in non-empty cell when reading Excel file through OleDB

How do Excel recognize Date cells and how to do the same using OpenXml and C#

1 Answers1