Using LAMBDA functions (original) (raw)
EPPlus supports LAMBDA functions from version 8.2. LAMBDA lets you define custom, reusable formulas directly in the workbook — without needing VBA or external code. The EPPlus calculation engine supports the full range of LAMBDA capabilities, including defined names, recursion, currying and eta reduction.
Lambda in defined names
A LAMBDA function can be stored as a defined name, making it reusable across the entire workbook just like a built-in function.
using var package = new ExcelPackage(); var sheet = package.Workbook.Worksheets.Add("Sheet1");
// Define a reusable division function package.Workbook.Names.AddFormula("Divide", "LAMBDA(x, y, x/y)");
sheet.Cells["A1"].Formula = "Divide(12, 3)"; sheet.Calculate(); var result = sheet.Cells["A1"].Value; // result is 4
Recursion
A LAMBDA can call itself, which allows you to implement iterative algorithms directly in a formula. The example below defines a recursive factorial function as a defined name.
// Factorial(n) = n * Factorial(n-1), base case at n = 0 // Note: the sheet name prefix is required when a defined name calls itself recursively ws.Names.AddFormula("Factorial", "LAMBDA(input, LET(n, input, IF(n = 0, 1, n * Sheet1!Factorial(n - 1))))");
ws.Cells["A1"].Formula = "Factorial(4)"; ws.Calculate(); // Result: 24
Currying
A LAMBDA can return another LAMBDA, allowing you to build up a calculation by supplying one argument at a time. This technique is known as currying.
// Each call supplies one argument and returns a new function // until all three arguments are provided sheet.Cells["A1"].Formula = "LAMBDA(a, LAMBDA(b, LAMBDA(c, a + b + c)))(1)(2)(3)"; sheet.Calculate(); // Result: 6
Eta Reduced Lambda
In higher-order functions like MAP, REDUCE or SCAN, you normally wrap a function in a full LAMBDA to pass it as an argument. Eta reduction is a shorthand from lambda calculus that lets you pass the function directly instead, omitting the wrapper entirely.
Excel represents this using the _xleta prefix — this is part of the OOXML specification, not an EPPlus-specific construct. EPPlus supports this syntax from version 8.2.
// Standard approach — wrap UPPER in a full LAMBDA sheet.Cells["B1"].Formula = "MAP(A1:A3, LAMBDA(x, UPPER(x)))";
// Eta reduced — pass UPPER directly using the _xleta prefix sheet.Cells["B2"].Formula = "MAP(A1:A3, _xleta.UPPER)";
Both formulas produce the same result. The eta reduced form is more concise and is the notation Excel itself uses internally when saving certain formulas to the file format.