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;
        }
    }
}

Answer 1

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;
    }
}

Answer 2

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;
        }