Recalculating a cell with the DAYS function whose arguments are cell references throws NullReferenceException

[billwolckenlmco]

Read and complete the full issue template

Do not randomly delete sections. They are here for a reason.

Do you want to request a feature or report a bug?

• [x] Bug
• [ ] Feature
• [ ] Question

Did you test against the latest CI build?

• [X] Yes
• [ ] No

If you answered No, please test with the latest development build first.

Version of ClosedXML

0.95.4 (also in CI version 0.95.999.2326)

What is the current behavior?

When a cell using the DAYS function whose arguments are cell references is recalculated, a NullReferenceException is thrown:

An unhandled exception of type 'System.NullReferenceException' occurred in ClosedXML.dll: 'Object reference not set to an instance of an object.'
   at ClosedXML.Excel.CalcEngine.Functions.DateAndTime.Days(List`1 p)
   at ClosedXML.Excel.CalcEngine.FunctionExpression.Evaluate()
   at ClosedXML.Excel.CalcEngine.CalcEngine.Evaluate(String expression)
   at ClosedXML.Excel.XLWorksheet.Evaluate(String expression)
   at ClosedXML.Excel.XLCell.RecalculateFormula(String fA1)
   at ClosedXML.Excel.XLCell.Evaluate(Boolean force)
   at ClosedXML.Excel.XLWorksheet.<>c.<RecalculateAllFormulas>b__334_0(XLCell cell)
   at ClosedXML.Excel.EnumerableExtensions.ForEach[T](IEnumerable`1 source, Action`1 action)
   at ClosedXML.Excel.XLWorksheet.RecalculateAllFormulas()
   at ClosedXML.Excel.XLWorkbook.<>c.<RecalculateAllFormulas>b__217_0(IXLWorksheet sheet)
   at ClosedXML.Excel.EnumerableExtensions.ForEach[T](IEnumerable`1 source, Action`1 action)
   at ClosedXML.Excel.XLWorkbook.RecalculateAllFormulas()

In my use case, I am creating a pivot table whose data uses the DAYS formula. Saving a workbook containing a new pivot table automatically triggers a recalculate of the cells with the DAYS formula, so the recalculate is not something I can avoid.

What is the expected behavior or new feature?

Not throw an exception, but fetch the values from the referenced cells and evaluate DAYS with them.

Is this a regression from the previous version?

0.94.2 throws a different exception:

An unhandled exception of type 'ClosedXML.Excel.CalcEngine.ExpressionParseException' occurred in ClosedXML.dll: 'Unknown function: ClosedXML.Excel.CalcEngine.CellRangeReference'
   at ClosedXML.Excel.CalcEngine.CalcEngine.Throw(String msg)
   at ClosedXML.Excel.CalcEngine.CalcEngine.Parse(String expression)
   at ClosedXML.Excel.CalcEngine.ExpressionCache.get_Item(String expression)
   at ClosedXML.Excel.CalcEngine.CalcEngine.Evaluate(String expression)
   at ClosedXML.Excel.XLWorksheet.Evaluate(String expression)
   at ClosedXML.Excel.XLCell.RecalculateFormula(String fA1)
   at ClosedXML.Excel.XLCell.Evaluate(Boolean force)
   at ClosedXML.Excel.XLWorksheet.<>c.<RecalculateAllFormulas>b__322_0(XLCell cell)
   at ClosedXML.Excel.EnumerableExtensions.ForEach[T](IEnumerable`1 source, Action`1 action)
   at ClosedXML.Excel.XLWorksheet.RecalculateAllFormulas()
   at ClosedXML.Excel.XLWorkbook.<>c.<RecalculateAllFormulas>b__208_0(IXLWorksheet sheet)
   at ClosedXML.Excel.EnumerableExtensions.ForEach[T](IEnumerable`1 source, Action`1 action)
   at ClosedXML.Excel.XLWorkbook.RecalculateAllFormulas()

Reproducibility

This is an important section. Read it carefully. Failure to do so will cause a 'RTFM' comment.

Without a code sample, it is unlikely that your issue will get attention. Don't be lazy. Do the effort and assist the developers to reproduce your problem. Code samples should be minimal complete and verifiable. Sample spreadsheets should be attached whenever applicable. Remove sensitive information.

Code to reproduce problem:

using System;
using ClosedXML.Excel;

class Program
{
    static void Main(string[] args)
    {
        var wb = new XLWorkbook();
        var sheet = wb.Worksheets.Add("Test");
        sheet.Cell(1, 1).SetValue(new DateTime(2020, 5, 1));
        sheet.Cell(1, 2).SetValue(new DateTime(2020, 3, 1));
        var cell3 = sheet.Cell(1, 3);
        cell3.FormulaA1 = "_xlfn.DAYS(A1,B1)";

        wb.SaveAs("DaysTest1.xlsx");  // succeeds - writes a valid workbook

        wb.RecalculateAllFormulas();  // throws exception

        wb.SaveAs("DaysTest2.xlsx");
    }
}

• [ ] I attached a sample spreadsheet.

Code sample builds the spreadsheet from scratch, so no input file is needed.

Some debugging

The exception in ClosedXML.Excel.CalcEngine.Functions.DateAndTime.Days was on the line:

type = p[0]._token.Value.GetType();

p[0]._token.Value was null. But p[0].Value was an instance of ClosedXML.Excel.CalcEngine.CellRangeReference, so perhaps the code should be using it instead? (I don't know the working of the CalcEngine to say.)