NPOI fails on second read in ASP.NET Core app

Question

In my ASP.NET Core app, I'm reading an Excel file using NPOI. Workbook is created this way:

public void ImportFile()
{    
    using var fileStream = new FileStream("path_to_file.xlsm", FileMode.Open,
            FileAccess.Read, FileShare.ReadWrite);
    var workbook = WorkbookFactory.Create(fileStream);

    // all the reads and persistence stuff
}

And all the reads are performed like that:

public static DateTime ReadDateTimeAt(IRow row, int columnIndex)
    {
        var cell = row.GetCell(columnIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK);
        try
        {
            return cell.DateCellValue;
        }
        catch
        {
            // handling logic
        }
    }

On the first call to method ImportFile() it all works just fine. Then, each subsequent call to it results in an error - NullReferenceException is thrown in ReadDateTimeAt function by cell.DateCellValue. Only restart of the whole app helps.

Why can it be? Am I not releasing some resources properly (I suppose I do, but the only one I think I should release is the FileStream, which is packed up in using - I've also checked with ResourceManager and file is not accessed between calls)?

I can't really tell which reference is null. It's not the Cell object itself - I can access it's properties through debugger. Whole workbook also seems to be loaded properly (again, accessing it through a debugger).

If it's relevant, cell that fails to be read holds reference to cell on another Sheet, it's type is set to Date.

Answer 1

On the first call to method ImportFile() it all works just fine. Then, each subsequent call to it results in an error - NullReferenceException is thrown in ReadDateTimeAt function by cell.DateCellValue. Only restart of the whole app helps.

I did reproduce your error. This should be an internal error of NPOI.

You can use the following methods to solve it.

 public static string GetStringValue(ICell cell)
        {
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return DateTime.FromOADate(cell.NumericCellValue).ToString();
                        //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;
            }
        }

ReadDateTimeAt method:

public static DateTime ReadDateTimeAt(IRow row, int columnIndex)
        {
            var cell = row.GetCell(columnIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); 
            return DateTime.Parse(GetStringValue(cell));

        }