ExcelWorksheet.Calculate(string Formula) uses wrong-scope defined names – minimal repro script attached

[nfcampos]

EPPlus usage

Commercial use (I have a commercial license)

Environment

macos, linux

Epplus version

8.4.0

Spreadsheet application

Excel

Description

using OfficeOpenXml;

ExcelPackage.License.SetNonCommercialPersonal("witan repro");

static (ExcelPackage pkg, ExcelWorksheet ws1, ExcelWorksheet ws2) CreateWorkbook(bool includeSheetScopedName)
{
    var pkg = new ExcelPackage();
    var ws1 = pkg.Workbook.Worksheets.Add("Sheet1");
    var ws2 = pkg.Workbook.Worksheets.Add("Sheet2");

    // Workbook-scoped name "MyTable" (should be used by formulas on Sheet2)
    ws2.Cells["A1"].Value = 1;
    ws2.Cells["B1"].Value = 2;
    ws2.Cells["A2"].Value = 10;
    ws2.Cells["B2"].Value = 20;
    pkg.Workbook.Names.Add("MyTable", ws2.Cells["A1:B2"]);

    if (includeSheetScopedName)
    {
        // Sheet-scoped name "MyTable" on Sheet1 (should NOT affect formulas on Sheet2)
        ws1.Cells["A1"].Value = 1;
        ws1.Cells["B1"].Value = 2;
        ws1.Cells["A2"].Value = 0;
        ws1.Cells["B2"].Value = 0;
        ws1.Names.Add("MyTable", ws1.Cells["A1:B2"]);
    }

    // Put the same formula in multiple cells so we can test different calculation APIs
    // without accidental caching/order effects between tests.
    ws2.Cells["C1"].Formula = "HLOOKUP(1,MyTable,2,FALSE)"; // used for string-eval + address-eval
    ws2.Cells["C2"].Formula = "HLOOKUP(1,MyTable,2,FALSE)"; // used for range.Calculate()
    ws2.Cells["C3"].Formula = "HLOOKUP(1,MyTable,2,FALSE)"; // used for workbook.Calculate()
    return (pkg, ws1, ws2);
}

static void RunTest(string name, Func<(ExcelPackage pkg, ExcelWorksheet ws1, ExcelWorksheet ws2), string> run)
{
    Console.WriteLine($"\n=== {name} ===");
    var ctx = CreateWorkbook(includeSheetScopedName: true);
    using (ctx.pkg)
    {
        Console.WriteLine(run(ctx));
    }
}

Console.WriteLine("Expected (Excel semantics): 10");

RunTest("Mode A: ws.Calculate(formula-string) is wrong", ctx =>
{
    object? inWs2;
    object? inWs1;
    try { inWs2 = ctx.ws2.Calculate(ctx.ws2.Cells["C1"].Formula); }
    catch (Exception ex) { inWs2 = $"EXCEPTION: {ex.GetType().Name}: {ex.Message}"; }
    try { inWs1 = ctx.ws1.Calculate(ctx.ws2.Cells["C1"].Formula); }
    catch (Exception ex) { inWs1 = $"EXCEPTION: {ex.GetType().Name}: {ex.Message}"; }
    return $"ws2.Calculate(formula) => {inWs2}\nws1.Calculate(formula) => {inWs1}";
});

RunTest("Mode B2: range.Calculate() is right", ctx =>
{
    var before = ctx.ws2.Cells["C2"].Value;
    try { ctx.ws2.Cells["C2"].Calculate(); }
    catch (Exception ex) { return $"Before => {before}\nEXCEPTION: {ex.GetType().Name}: {ex.Message}"; }
    return $"Before => {before}\nAfter  => {ctx.ws2.Cells["C2"].Value}";
});

RunTest("Mode B3: worksheet.Calculate() is right", ctx =>
{
    var before = ctx.ws2.Cells["C2"].Value;
    try { ctx.ws2.Calculate(); }
    catch (Exception ex) { return $"Before => {before}\nEXCEPTION: {ex.GetType().Name}: {ex.Message}"; }
    return $"Before => {before}\nAfter  => {ctx.ws2.Cells["C2"].Value}";
});

RunTest("Mode B: workbook.Calculate() is right", ctx =>
{
    var before = ctx.ws2.Cells["C3"].Value;
    try { ctx.pkg.Workbook.Calculate(); }
    catch (Exception ex) { return $"Before => {before}\nEXCEPTION: {ex.GetType().Name}: {ex.Message}"; }
    return $"Before => {before}\nAfter  => {ctx.ws2.Cells["C3"].Value}";
});

RunTest("Mode C: ws.Calculate(address) is right", ctx =>
{
    object? fromWs2;
    object? fromWs1;
    try { fromWs2 = ctx.ws2.Calculate("'Sheet2'!C1"); }
    catch (Exception ex) { fromWs2 = $"EXCEPTION: {ex.GetType().Name}: {ex.Message}"; }
    try { fromWs1 = ctx.ws1.Calculate("'Sheet2'!C1"); }
    catch (Exception ex) { fromWs1 = $"EXCEPTION: {ex.GetType().Name}: {ex.Message}"; }
    return $"ws2.Calculate(\"'Sheet2'!C1\") => {fromWs2}\nws1.Calculate(\"'Sheet2'!C1\") => {fromWs1}";
});

RunTest("Sanity: removing sheet-scoped name fixes formula-string eval", ctx =>
{
    // Demonstrate the fix within one workbook instance.
    object? before;
    object? after;
    try { before = ctx.ws2.Calculate(ctx.ws2.Cells["C1"].Formula); }
    catch (Exception ex) { before = $"EXCEPTION: {ex.GetType().Name}: {ex.Message}"; }

    ctx.ws1.Names.Remove("MyTable");

    try { after = ctx.ws2.Calculate(ctx.ws2.Cells["C1"].Formula); }
    catch (Exception ex) { after = $"EXCEPTION: {ex.GetType().Name}: {ex.Message}"; }

    return $"Before removing ws1-scoped name => {before}\nAfter  removing ws1-scoped name => {after}";
});

Expected (Excel semantics): 10

=== Mode A: ws.Calculate(formula-string) is wrong ===
ws2.Calculate(formula) => 0
ws1.Calculate(formula) => 0

=== Mode B2: range.Calculate() is right ===
Before =>
After  => 10

=== Mode B3: worksheet.Calculate() is right ===
Before =>
After  => 10

=== Mode B: workbook.Calculate() is right ===
Before =>
After  => 10

=== Mode C: ws.Calculate(address) is right ===
ws2.Calculate("'Sheet2'!C1") => 10
ws1.Calculate("'Sheet2'!C1") => 10

=== Sanity: removing sheet-scoped name fixes formula-string eval ===
Before removing ws1-scoped name => 0
After  removing ws1-scoped name => 10

[JanKallman]

Yes , the worksheet scope is not taken into account correctly when using the Worksheet.Calculate method. We will provide a fix.