How OfficeCLI's Core Formula Engine Supports 350+ Excel Functions with Dynamic Arrays

OfficeCLI's FormulaEvaluator class implements a recursive-descent parser and multi-phase evaluation pipeline that tokenizes Excel syntax, dispatches to 350+ function implementations, and natively supports dynamic array spill behavior through RangeData objects.

The iOfficeAI/OfficeCLI repository provides a complete spreadsheet processing toolkit built around a robust formula calculation engine. At the heart of this system lies the OfficeCli.Core.Formula namespace, which implements Excel-compatible evaluation logic capable of handling everything from legacy financial functions to modern dynamic array formulas. The engine's architecture separates tokenization, parsing, and evaluation into distinct phases to ensure accurate calculation semantics.

Tokenizer and Recursive-Descent Parser

The evaluation pipeline begins in FormulaEvaluator.cs with the Tokenize method (lines 73-114), which scans formula strings into a stream of Token objects. This lexer recognizes numbers, strings, cell references, ranges, function names, and special operators including the new dynamic-array tokens and LET and LAMBDA keywords.

Following tokenization, a recursive-descent parser converts the token stream into a syntax tree. The parser implements Excel's operator precedence through dedicated methods: ParseExpression, ParseComparison, ParseConcat, ParseAddSub, ParseMulDiv, ParsePower, ParseUnary, and ParsePostfix (lines 41-66). This design handles complex syntax including the % postfix operator, unary +/-, exponentiation, and the concatenation operator (&), while managing implicit intersection and array/range coercion rules.

Expression Evaluation and Result Wrapping

The EvaluateFormula method processes the parsed syntax tree into a FormulaResult object that unifies scalars, arrays (double[]), and two-dimensional ranges (RangeData). When arrays appear in scalar contexts, the engine applies implicit-intersection rules to collapse ranges to single cells, matching Excel's behavior.

To prevent crashes from deeply nested formulas, the implementation includes stack-overflow guards using MaxSameSheetDepth limits and RuntimeHelpers.TryEnsureSufficientExecutionStack (lines 48-50 in FormulaEvaluator.cs). These safeguards allow the engine to handle complex dependency chains without risking process termination.

Function Dispatch for 350+ Excel Functions

Concrete function implementations reside in FormulaEvaluator.Functions.cs, where the EvalFunction method maps over 350+ Excel functions. Each dispatch case returns a FormulaResult containing numeric, string, boolean, error, array, or range values.

The dispatch table covers legacy statistical functions (SUM, AVERAGE, MEDIAN), lookup utilities (VLOOKUP, HLOOKUP), and modern dynamic-array helpers (SEQUENCE, SORT, UNIQUE, FILTER, XMATCH, XLOOKUP). Specialized calculation logic for financial, statistical, and regression functions is segmented across companion files including FormulaEvaluator.Securities.cs, FormulaEvaluator.Statistics.cs, and FormulaEvaluator.Regression.cs.

Dynamic Array and Spill Range Support

Dynamic array functions that return spill ranges create RangeData objects stored in FormulaResult.RangeValue. The engine implements spill-aware functions including SEQUENCE, SORT, UNIQUE, FILTER, EXPAND, HSTACK, VSTACK, WRAPROWS, WRAPCOLS, TEXTSPLIT, MAP, BYROW, BYCOL, SCAN, and MAKEARRAY.

Each spilled range preserves its origin coordinates (BaseRow, BaseCol, BaseSheet) through the ParseArrayConstant helper and array-returning cases in EvalFunction. This metadata enables dependent functions like ROW, COLUMN, and ADDRESS to report correct references relative to the spill origin.

LET, LAMBDA, and Higher-Order Functions

User-defined functions and variable binding are implemented in FormulaEvaluator.SpecialFunctions.cs. The EvalLet method binds named variables to intermediate results, while MakeLambda captures parameter names and tokenized bodies. The InvokeLambda method evaluates the body with arguments bound, enabling higher-order operations such as REDUCE, MAP, and SCAN.

Lambda values are stored in FormulaResult.LambdaValue using the Lambda record defined in FormulaEvaluator.cs (lines 28-30). This architecture allows the engine to treat functions as first-class values, supporting complex functional programming patterns within Excel formulas.

Cross-Sheet Resolution and Session Caching

The FormulaEvalSession class maintains a per-session cache that memoizes cell results, range materializations, and sheet evaluators. This prevents O(n²) performance degradation when formulas reference cells across multiple sheets.

Cross-sheet resolution logic in FormulaEvaluator.References.cs handles defined names through GetDefinedNames and TryDefinedNameAsSimpleRef, which inline or tokenize named references on-the-fly. When evaluating cross-sheet formulas, the engine reuses cached evaluators to minimize memory overhead and calculation time.

Error Handling and Excel Compatibility

Error propagation follows Excel's standard through FormulaResult.Error, supporting #DIV/0!, #NUM!, #VALUE!, and other error types. The engine mimics Excel's coercion rules: blank cells evaluate to 0 in arithmetic contexts, numeric-looking strings are parsed on demand, and Infinity values map to #NUM! errors.

Practical Implementation Examples

The following examples demonstrate how to use the Core Formula engine from C# handlers or custom plugins:

// 1️⃣ Basic scalar evaluation
var sheet = workbook.WorkbookPart!.WorksheetParts.First().Worksheet.GetFirstChild<SheetData>()!;
var evaluator = new FormulaEvaluator(sheet, workbook.WorkbookPart);
var result = evaluator.TryEvaluateFull("=SUM(A1:A5) * 2");
Console.WriteLine(result?.ToCellValueText()); // → "1234" (example)

// 2️⃣ Dynamic‑array spill
var spill = evaluator.TryEvaluateFull("=SEQUENCE(3,2,1,2)");
if (spill?.IsRange == true)
{
    var area = spill.RangeValue!;
    for (int r = 0; r < area.Rows; r++)
    {
        for (int c = 0; c < area.Cols; c++)
            Console.Write(area.Cells[r, c]?.AsNumber() + "\t");
        Console.WriteLine();
    }
    // Output:
    // 1   3
    // 2   4
    // 3   5
}

// 3️⃣ Using LET + LAMBDA
var lambdaResult = evaluator.TryEvaluateFull(
    "LET(x, A1, y, A2, LAMBDA(z, x+z)(y))");
Console.WriteLine(lambdaResult?.AsNumber()); // → value of A1 + A2

// 4️⃣ Cross‑sheet reference (memoised session)
var session = new FormulaEvalSession();
var evalSheet1 = new FormulaEvaluator(sheet1, wbPart, session, 0, "Sheet1");
var evalSheet2 = new FormulaEvaluator(sheet2, wbPart, session, 0, "Sheet2");
var cross = evalSheet1.TryEvaluateFull("=Sheet2!B3 + 10");
Console.WriteLine(cross?.AsNumber());

Summary

• OfficeCLI's formula engine uses a recursive-descent parser with explicit operator precedence methods to process Excel syntax.
• 350+ functions are dispatched through EvalFunction in FormulaEvaluator.Functions.cs, covering legacy, statistical, financial, and dynamic-array categories.
• Dynamic array support is implemented through RangeData objects that preserve spill origins, enabling full compatibility with modern Excel functions like SEQUENCE and FILTER.
• LET and LAMBDA enable user-defined functions and higher-order programming through the FormulaResult.LambdaValue storage mechanism.
• Session caching via FormulaEvalSession prevents performance degradation when evaluating cross-sheet references.
• Stack overflow guards and Excel-compatible error handling ensure robust evaluation of complex dependency chains.

Frequently Asked Questions

How does OfficeCLI handle dynamic array spill behavior?

Functions that return dynamic arrays create RangeData objects stored in FormulaResult.RangeValue, preserving origin coordinates (BaseRow, BaseCol, BaseSheet) so that dependent functions like ROW and COLUMN calculate correct references. The engine supports spill-aware functions including SEQUENCE, SORT, UNIQUE, FILTER, HSTACK, VSTACK, and TEXTSPLIT, among others.

What safeguards exist against recursive formula errors?

The FormulaEvaluator implements stack-overflow protection through MaxSameSheetDepth limits and RuntimeHelpers.TryEnsureSufficientExecutionStack checks (lines 48-50 in FormulaEvaluator.cs). These mechanisms prevent deep recursion from crashing the application when evaluating complex nested formulas or circular reference chains.

How does the FormulaEvalSession class improve performance?

FormulaEvalSession maintains a per-session cache that memoizes cell results, range materializations, and sheet evaluators. This caching strategy avoids O(n²) complexity when formulas reference multiple sheets, as the engine reuses cached evaluators rather than recreating them for each cross-sheet reference.

Which Excel functions are implemented in the engine?

The engine implements over 350 functions across categories including statistical (MEDIAN, STDEV), financial (PMT, FV, NPV), lookup (VLOOKUP, XLOOKUP, XMATCH), dynamic arrays (SEQUENCE, SORT, FILTER), and higher-order operations (MAP, REDUCE, SCAN). Implementation files are organized by domain in FormulaEvaluator.Statistics.cs, FormulaEvaluator.Securities.cs, and FormulaEvaluator.SpecialFunctions.cs.

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