Npoi ICell.DateCellValue return NullReferenceException

Question

I have this Excel column:

Formatted as date data format:

And I get NullReferenceException if I try to read the DateTime value.

Do you know what is wrong here and how to fix it? Is it possible to convert the number into DateTime somehow? For example 31/12/9999 is 2958465 when I change to number format.

ICell into string extension

public static class NpoiExtension
{
    public static string GetStringValue(this ICell cell)
    {
        switch (cell.CellType)
        {
            case CellType.Numeric:
                if (DateUtil.IsCellDateFormatted(cell)) 
                {
                    try
                    {
                        return cell.DateCellValue.ToString();   
                    }
                    catch (NullReferenceException)
                    {
                        // https://stackoverflow.com/questions/15040567/c-xlsx-date-cell-import-to-datatable-by-npoi-2-0
                        //var prevCulture = Thread.CurrentThread.CurrentCulture;
                        //CultureInfo customCulture = new CultureInfo("en-GB", false);
                        //Thread.CurrentThread.CurrentCulture = customCulture;

                        string dateOutput = cell.DateCellValue.ToString();

                        //Thread.CurrentThread.CurrentCulture = prevCulture;
                        return dateOutput;
                    }
                }
                else
                {
                    return cell.NumericCellValue.ToString();
                }
            case CellType.String:
                return cell.StringCellValue;

            case CellType.Boolean:
                return cell.BooleanCellValue.ToString();

            default:
                return string.Empty;
        }
    }
}

I tried your code and got the date value without error. Is your problem related to the [question](https://stackoverflow.com/questions/15040567/c-xlsx-date-cell-import-to-datatable-by-npoi-2-0) commented in your code? What culture do you have in `Thread.CurrentThread.CurrentCulture`? — krlzlx, Feb 01 '19 at 09:34
By default, I have "en-GB" in `Thread.CurrentThread.CurrentCulture`. Normally i dont have issues with datetimes, but this column has exception in `DateCellValue`. So the workaround was to use `DateTime.FromOADate()` — Muflix, Feb 01 '19 at 10:09
I have "en-GB" too. Maybe related to the NPOI version? Which version do you use? I'm using version 2.4.0. — krlzlx, Feb 01 '19 at 10:49
I dont know what can be reason for this error and why it is working in your script. — Muflix, Feb 01 '19 at 13:47

score 13 · Accepted Answer · edited Feb 01 '19 at 10:43

13

I found solution here How do I convert an Excel serial date number to a .NET DateTime? So I add it into my scenario.

public static string GetStringValue(this ICell cell)
{
    switch (cell.CellType)
    {
        case CellType.Numeric:
            if (DateUtil.IsCellDateFormatted(cell)) 
            {
                try
                {
                    return cell.DateCellValue.ToString();
                }
                catch (NullReferenceException)
                {
                    return DateTime.FromOADate(cell.NumericCellValue).ToString();
                }
            }
            return cell.NumericCellValue.ToString();

        case CellType.String:
            return cell.StringCellValue;

        case CellType.Boolean:
            return cell.BooleanCellValue.ToString();

        default:
            return string.Empty;
    }
}

edited Feb 01 '19 at 10:43

krlzlx

5,752
14
47
55

answered Feb 01 '19 at 09:10

Muflix

6,192
17
77
153

3

This solution works like a charm. The odd thing in my scenario was I was having this NullReferenceException sometimes and other times it would run completely normal, crazy stuff. It appears to me, when running for the first time would work normal but when reading for the second time and so on, it would throw the NullReferenceException for no apparently reason. That's why some people say it is normal when in fact we are dealing with some instable behavior that it can't be reproduced at first try. – Felipe Jul 04 '20 at 12:56
@Felipe I also noticed that the NullReferenceException is thrown on the second time I read a file. First read goes through. This is odd. The solution worked for me! For those who wonder how to get a DateTime out of the string: `var dateValue = DateTime.Parse(cell.GetStringValue()))` – Kheder Nov 03 '21 at 09:13

score 1 · Answer 2 · answered Nov 23 '20 at 01:08

Another neat option to get the DateTime whenever the cell is defined as numeric is getting the cell's numeric value:

DateTime.FromOADate(cell.NumericCellValue);

A complete example:

        private ICell GetCellValue(string position)
        {
            var cr = new CellReference(position);
            var row = m_Sheet.GetRow(cr.Row);
            return row.GetCell(cr.Col);
        }

        public DateTime? GetCellDateValue(string position)
        {
            ICell cellValue = GetCellValue(position);

            if (cellValue == null)
            {
                // Cell doesn't have any value
                return null;
            }

            if (cellValue.CellType == CellType.Numeric)
            {
                return DateTime.FromOADate(cellValue.NumericCellValue);
            }

            return cellValue.DateCellValue;
        }

Npoi ICell.DateCellValue return NullReferenceException

2 Answers2