How to Use the OfficeCLI FormulaEvaluator for Excel Formulas (350+ Functions, Spilling, and LAMBDA)

OfficeCLI's built-in FormulaEvaluator class evaluates over 350 Excel functions—including modern dynamic-array spills and LAMBDA expressions—directly against OpenXML sheet data without requiring Excel installation.

The FormulaEvaluator lives in the OfficeCli.Core namespace and ships as a partial class split across multiple implementation files. It provides a complete, standalone Excel formula engine that parses, computes, and returns results through a unified FormulaResult wrapper. Whether you need to calculate financial models, statistical regressions, or complex array transformations, this evaluator handles everything from scalar values to spilled arrays.

Creating a FormulaEvaluator Instance

To begin evaluating formulas, instantiate the evaluator with the target worksheet's SheetData and the WorkbookPart from the OpenXML document. This initialization allows the engine to resolve cell references, cross-sheet lookups, and named ranges.

using OfficeCli.Core;

// sheetData and workbookPart come from your OpenXML document
var evaluator = new FormulaEvaluator(sheetData, workbookPart);

The constructor stores references to the underlying OOXML objects, enabling the evaluator to read current cell values and write computed results back to the worksheet. According to the source code in ExcelHandler.View.cs, this is the standard pattern OfficeCLI uses internally when rendering formula results for display.

Evaluating Basic Formulas

Call EvalFormula() with any valid Excel formula string. The method returns a FormulaResult object that encapsulates scalars, arrays, errors, or ranges.

FormulaResult result = evaluator.EvalFormula("=SUM(A1:A10)");

if (!result.IsError)
{
    double total = result.AsNumber();
    Console.WriteLine($"Total: {total}");
}
else
{
    Console.WriteLine($"Error: {result.ErrorValue}");
}

The FormulaResult class (defined in FormulaResult.cs) provides strongly-typed accessors:

• AsNumber() returns a double
• AsString() returns the text representation
• IsArray indicates if the result spilled into multiple cells
• ArrayValue provides the underlying 2D array for spilled results

Working with Dynamic Arrays and Spill Behavior

Modern Excel functions like SEQUENCE, SORT, UNIQUE, and FILTER return arrays that automatically spill into surrounding cells. The FormulaEvaluator implements this behavior in FormulaEvaluator.SpillLambda.cs through generic array-spilling logic.

Detecting Spilled Results

When a formula returns an array, the evaluator sets IsArray = true and populates ArrayValue:

FormulaResult seq = evaluator.EvalFormula("=SEQUENCE(3, 2, 1, 10)");

if (seq.IsArray)
{
    double[,] matrix = seq.ArrayValue;
    // matrix dimensions: 3 rows × 2 columns
    // [ [1, 11],
    //   [2, 12],
    //   [3, 13] ]
    Console.WriteLine($"Spilled {matrix.GetLength(0)} rows × {matrix.GetLength(1)} columns");
}

Array Functions

The evaluator supports lifting scalar functions to operate element-wise across arrays. Functions such as MAP, REDUCE, and BYROW are implemented in FormulaEvaluator.SpillLambda.cs via EvalMap, EvalReduce, and EvalByRow respectively:

// Double each value in a generated sequence
FormulaResult mapped = evaluator.EvalFormula(
    "=MAP(SEQUENCE(5, 1, 1, 1), LAMBDA(x, x * 2))");

// Result: {2, 4, 6, 8, 10}

Using LAMBDA Functions

The FormulaEvaluator parses LAMBDA tokens into executable Lambda objects and invokes them via InvokeLambda. This functionality resides in FormulaEvaluator.SpillLambda.cs.

Defining and Calling LAMBDAs

You can define custom functions inline and invoke them immediately or pass them to higher-order functions:

// Define and immediately invoke a LAMBDA
FormulaResult lambdaCall = evaluator.EvalFormula("=LAMBDA(x, y, x + y)(3, 5)");

// lambdaCall.AsNumber() == 8

// Store LAMBDA in a cell reference then invoke (advanced usage)
// The evaluator handles the binding automatically when parsing the formula

LAMBDA with Spill Functions

Combine LAMBDA with dynamic-array functions for powerful transformations:

// Calculate squares of a sequence
FormulaResult squares = evaluator.EvalFormula(
    "=MAP(SEQUENCE(1, 5, 1, 1), LAMBDA(x, x^2))");

// Returns: {1, 4, 9, 16, 25}

Handling Errors and Special Functions

The evaluator mimics Excel's error propagation rules. In FormulaEvaluator.Functions.cs, the EvalFunction method filters error values unless the function is marked as error-transparent in the ErrorTransparentFns list.

Error Transparency

Functions like IF and IFERROR evaluate lazily and handle errors gracefully:

// Returns "fallback" instead of #DIV/0!
FormulaResult safe = evaluator.EvalFormula("=IFERROR(1/0, \"fallback\")");

Most functions abort on the first error argument, while error-transparent functions can suppress or trap errors. The implementation checks the error-transparent flag before deciding whether to propagate errors to the result.

Complete C# Implementation Example

The following example demonstrates evaluating multiple formula types and handling both scalar and array results:

using OfficeCli.Core;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

// Load workbook
using (SpreadsheetDocument doc = SpreadsheetDocument.Open("data.xlsx", false))
{
    WorkbookPart wbPart = doc.WorkbookPart;
    SheetData sheetData = wbPart.Workbook.Descendants<SheetData>().First();
    
    // Initialize evaluator
    var evaluator = new FormulaEvaluator(sheetData, wbPart);
    
    // 1. Statistical function
    FormulaResult avg = evaluator.EvalFormula("=AVERAGE(B2:B100)");
    Console.WriteLine($"Average: {avg.AsNumber()}");
    
    // 2. Financial function with solver (uses FormulaEvaluator.Solver.cs)
    FormulaResult irr = evaluator.EvalFormula("=IRR(C1:C10)");
    Console.WriteLine($"IRR: {irr.AsNumber():P4}");
    
    // 3. Dynamic array with spill
    FormulaResult unique = evaluator.EvalFormula("=SORT(UNIQUE(D2:D50))");
    if (unique.IsArray)
    {
        double[,] values = unique.ArrayValue;
        for (int i = 0; i < values.GetLength(0); i++)
        {
            Console.WriteLine($"Rank {i+1}: {values[i, 0]}");
        }
    }
    
    // 4. Custom LAMBDA calculation
    FormulaResult hypotenuse = evaluator.EvalFormula(
        "=LAMBDA(a, b, SQRT(a^2 + b^2))(3, 4)");
    Console.WriteLine($"Hypotenuse: {hypotenuse.AsNumber()}");
}

Summary

• The FormulaEvaluator class in OfficeCli.Core provides a standalone Excel formula engine with no dependency on Excel installation.
• Instantiate the evaluator with SheetData and WorkbookPart to enable cross-reference resolution and OOXML integration.
• Access results through the FormulaResult wrapper, checking IsArray to handle spilled dynamic-array outputs.
• Implementations in FormulaEvaluator.SpillLambda.cs enable full support for LAMBDA definitions and modern spill functions like SEQUENCE, MAP, and REDUCE.
• The function dispatch system in FormulaEvaluator.Functions.cs covers 350+ built-in functions across statistical, financial, and mathematical domains.
• Error handling follows Excel semantics, with special treatment for error-transparent functions defined in the evaluator's internal lists.

Frequently Asked Questions

What Excel functions does FormulaEvaluator support?

The evaluator supports over 350 built-in functions spanning mathematics, statistics, finance, text manipulation, and date/time calculations. According to the source code in FormulaEvaluator.Functions.cs, this includes modern dynamic-array functions like SEQUENCE, SORT, FILTER, and UNIQUE, as well as legacy functions like VLOOKUP, INDEX, and MATCH. Financial iterative functions such as XIRR and IRR utilize numerical solvers defined in FormulaEvaluator.Solver.cs.

How does automatic array spilling work in the evaluator?

When a formula returns multiple values, the evaluator automatically spills the result into a 2D array accessible via result.ArrayValue. The spill logic is implemented in FormulaEvaluator.SpillLambda.cs through methods like EvalMap and generic array-spilling handlers. The FormulaResult.IsArray property indicates whether the result represents a spilled range, allowing your code to handle scalar and array outputs appropriately.

Can I define and reuse custom LAMBDA functions programmatically?

Yes. The evaluator parses LAMBDA expressions into executable objects via InvokeLambda as implemented in FormulaEvaluator.SpillLambda.cs. You can define LAMBDAs inline within formula strings and immediately invoke them, or pass them as arguments to higher-order functions like MAP and REDUCE. The evaluator handles parameter binding and recursion according to Excel's LAMBDA specification.

Do I need Microsoft Excel installed to use FormulaEvaluator?

No. The FormulaEvaluator is a pure .NET implementation contained entirely within the OfficeCli.Core namespace. It operates directly on OpenXML SheetData objects without COM interop or external Excel processes. This makes it suitable for server-side document processing, CLI automation, and environments where Excel desktop is unavailable.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}