Some address expression references to an empty cell should return zero

[colbybhearn]

Excel and EPPlus behave differently when assigning one cell to the value of an empty cell. I attempted to make a fix on my own, but my fix broke 6 existing (and previously passing) tests. Looking into why they broke, the context of the empty cell reference matters, but I don't know how to properly detect or communicate that where needed.

Given A1 is empty or blank... Example 1: In formula ISBLANK(A1), A1 should evaluate to null so that ISBLANK(null) can evaluate to true. [working 👍 ] Example 2: In formula A1, A1 should evaluate to 0. [not working 👎 ] Example 3: In formula A1\*3, A1 should evaluate to 0 so that 0*3 can evaluate to 0. [working 👍 ] Example 4: In formula IF(TRUE,A1), A1 should evaluate to 0, so that IF(TRUE,0) evaluates to 0. [not working 👎 ]

In my very rough understanding of the code base, it's as though ExcelAddressExpression needs more information as to the context in which it is referenced in order to be compiled appropriately.

I will upload a sample shortly.

[colbybhearn]

Here's sample code and a sample workbook issue559.xlsx.

    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    var package = new ExcelPackage();
    var ws = package.Workbook.Worksheets.Add("sample");
 
    var b1 = ws.Cells["B1"];
    b1.Formula = "ISBLANK(A1)";

    var b2 = ws.Cells["B2"];            
    b2.Formula = "A1";

    var b3 = ws.Cells["B3"];
    b3.Formula = "A1*3";

    var b4 = ws.Cells["B4"];
    b4.Formula = "IF(TRUE,A1)";

    ws.Calculate();

    void printCell(ExcelRange e)
    {
        Console.WriteLine($"{e.Formula} => {e.Value}");
    }

    printCell(b1);
    printCell(b2);
    printCell(b3);
    printCell(b4);

    Console.Read();

One workaround is to, where necessary, multiply by 1 any reference to a potentially empty cell, so that EPPlus resolves it to a zero.

[colbybhearn]

Since this is a critical defect for my customer, I took a stab at fixing this: #561 It's not pretty IMO, but perhaps it might save you some time while preparing a fix. Please let me know if there's any further information I can provide. Thanks!

[colbybhearn]

I had to amend PR #561 as NamedValueExpression's compile method also had to resolve references to empty cells.

[OssianEPPlus]

Closed due to apparent completion by commit it https://github.com/EPPlusSoftware/EPPlus/commit/a33540ea431e18a8b9dcd957540f840ac8b87c5c. As seen in PR #561