Repository navigation
Description
Version: EPPlus 8.7.2 (also 8.7.1), .NET 10, Windows.
Summary
Calculating a package one worksheet at a time (foreach (var ws in wb.Worksheets) ws.Calculate();) can return a wrong
value for a named LAMBDA whose body uses LET. When a later sheet's calculation recalculates a cell on an earlier sheet,
that LAMBDA resolves one of its own parameters from a scope left by a different LAMBDA's call on another sheet, if the two
LAMBDAs share the parameter name. ExcelWorkbook.Calculate() on the same package returns Excel's value.
Minimal repro
using OfficeOpenXml;
ExcelPackage.License.SetNonCommercialPersonal("repro");
using var p = new ExcelPackage();
var wb = p.Workbook;
var a = wb.Worksheets.Add("A");
var b = wb.Worksheets.Add("B");
var c = wb.Worksheets.Add("C");
wb.Names.AddFormula("Kf", "LAMBDA(t, 2*t)");
wb.Names.AddFormula("Loss", "LAMBDA(Dh_in, K, LET(v, 1/Dh_in, v*K))");
wb.Names.AddFormula("Slip", "LAMBDA(K, Dh_in, K+Dh_in)");
a.Cells["A1"].Formula = "Kf(5)"; // 10
wb.Names.Add("K", a.Cells["A1"]);
a.Cells["A2"].Formula = "Loss(4,K)"; // Excel: 1/4*10 = 2.5
b.Cells["A1"].Formula = "Slip(K,8)"; // 18
c.Cells["A1"].Formula = "A!A2";
foreach (var ws in wb.Worksheets) ws.Calculate();
Console.WriteLine(a.Cells["A2"].Value); // 1.25 - Loss read Dh_in = 8 from Slip(K,8)
wb.Calculate();
Console.WriteLine(a.Cells["A2"].Value); // 2.5Each of these makes the defect disappear: a single wb.Calculate(); removing sheet C (nothing recalculates A!A2 after B);
putting C's =A!A2 on sheet B instead; Loss without LET (LAMBDA(Dh_in, K, 1/Dh_in*K)); K as plain arithmetic
instead of a LAMBDA result (=2*5); renaming Slip's Dh_in.
Expected: 2.5 (Excel 365, and ExcelWorkbook.Calculate()).
Actual: 1.25.
Where it seems to come from (decompiled 8.7.1/8.7.2)
ParsingContext.VariableStorageis oneVariableStorageManager(aStack<VariableStorageScope>) per package, reused
acrossCalculate()calls.FormulaExecutor.CompileExpressionsresolves a variable token that no enclosingLET/LAMBDAon its own expression
stack declares againstscope ?? parsingContext.VariableStorage.Peek()whenever the storage stack is not empty, i.e.
against whatever scope is on top at that moment, not the scope of the call being compiled.CompileExpressionspushes a scope perLET/LAMBDAand pops it at the function token, and clears the storage at the
end;RpnFormulaalso clears it on reset. A worksheet calculation that recalculates a precedent on another sheet
while a LAMBDA call's scope is live (here, C's=A!A2pullingLoss(4,K), whoseKargument is itself a LAMBDA result)
compilesLoss'sLETbody whileSlip's scope from sheet B is still on the stack, soDh_inbinds to 8.
We work around it by calculating with one ExcelWorkbook.Calculate() pass, which 8.7.2 makes possible by fixing the
named-LAMBDA "(" crash in the workbook-wide calculation.
Second repro: the same leak inside one ExcelWorkbook.Calculate()
using var p = new ExcelPackage();
var wb = p.Workbook;
var a = wb.Worksheets.Add("A");
wb.Names.AddFormula("Out", "LAMBDA(ID_in, X, LET(v, ID_in*10, v+X))");
wb.Names.AddFormula("In", "LAMBDA(ID_in, ID_in+1)");
a.Cells["A1"].Formula = "Out(2, In(5))"; // Excel: 2*10 + 6 = 26
wb.Calculate();
Console.WriteLine(a.Cells["A1"].Value); // 56 - Out read ID_in = 5 from In(5)Out(2, B1) with B1 = In(5) gives 56 too (B1 is calculated while Out's arguments are). It gives 26 when: Out has no
LET (LAMBDA(ID_in, X, ID_in*10+X)); Out's parameter is renamed (Dout); or B1 is calculated before the cell that
calls Out. The variable lookup appears to be the same one as above: the LET body's ID_in resolves against the top of
the shared scope stack, which at that point is In's call.
Metadata
Metadata
Assignees
Labels
Type
Projects
- StatusShow more project fieldsBacklog
Activity
#2606: lexical LET/LAMBDA variable scoping, balanced scope stack (#2607)
swmal commentedon Nov 16, 2026
@AntonBaluev - Thanks for reporting this and for the minimal repro. A fix will be included in the next version.