How OfficeCLI's Excel Formula Evaluator Supports Dynamic Arrays and 350+ Functions

OfficeCLI's FormulaEvaluator class implements a complete Excel-compatible calculation engine with dynamic-array spill support and over 350 built-in functions through a modular architecture spanning tokenization, recursive-descent parsing, and dispatch-based function evaluation.

The OfficeCLI project's spreadsheet calculation capabilities live in the OfficeCli.Core.Formula namespace. At its core sits the FormulaEvaluator partial class, which delivers near-identical formula semantics to Microsoft Excel—including the modern dynamic-array functions introduced in Excel 2021 and Microsoft 365.

Architecture of the Formula Engine

The evaluation pipeline follows a classic compiler-like structure with four distinct stages, each implemented across focused source files.

Tokenization: Converting Formula Strings to Tokens

The Tokenize method in [FormulaEvaluator.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.cs#L73-L114) scans raw formula strings into a stream of Token objects. This lexer recognizes:

• Numeric literals, string literals, and boolean values
• Cell references (A1, R1C1 style) and range operators (:, ,, union)
• Array constants bracketed with {…}
• The new LET and LAMBDA keywords
• Quoted sheet names for cross-sheet references

Recursive-Descent Parser

The parser builds a syntax tree through precedence-climbing methods defined in [FormulaEvaluator.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.cs#L41-L66):

• ParseExpression → ParseComparison → ParseConcat → ParseAddSub → ParseMulDiv → ParsePower → ParseUnary → ParsePostfix

The parser handles operator precedence, implicit intersection, the % postfix operator, and array/range coercion rules. This structure directly mirrors Excel's own calculation behavior.

Evaluation Engine with Result Wrapping

The EvaluateFormula method converts parsed expressions into FormulaResult objects. These results encapsulate:

• Scalars: numbers, strings, booleans, errors
• Arrays: double[] for 1-D arrays
• Ranges: RangeData for 2-D spill results

Implicit intersection automatically collapses arrays to single cells in scalar contexts—matching Excel's legacy behavior for formulas like =B1:B3*1.

Function Dispatch for 350+ Excel Functions

The EvalFunction method in [FormulaEvaluator.Functions.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Functions.cs) routes function calls to implementations. Each returns a FormulaResult with appropriate type tagging.

Dynamic Array and Spill Support

OfficeCLI's dynamic-array implementation centers on range-returning functions that produce FormulaResult.RangeValue with preserved origin coordinates (BaseRow, BaseCol, BaseSheet).

Spill-Capable Functions

The engine implements the complete modern Excel dynamic-array toolkit:

Category	Functions
Sequence generation	SEQUENCE, MAKEARRAY
Transformation	SORT, SORTBY, UNIQUE, FILTER
Restructuring	EXPAND, HSTACK, VSTACK, WRAPROWS, WRAPCOLS
Text splitting	TEXTSPLIT, TEXTJOIN (array-aware)
Functional programming	MAP, BYROW, BYCOL, SCAN, REDUCE
Lookup (modern)	XLOOKUP, XMATCH

These functions create RangeData objects that occupy multiple cells, with downstream references automatically resolving ROW, COLUMN, and ADDRESS relative to the spill origin.

LET and LAMBDA Implementation

User-defined functions arrive through two cooperating mechanisms in [FormulaEvaluator.SpecialFunctions.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.SpecialFunctions.cs):

• EvalLet: Binds named variables to intermediate results, enabling formula reuse without repetition
• MakeLambda: Captures parameter names plus tokenized body; InvokeLambda evaluates with arguments bound

The Lambda record (defined at lines 28-30 in the core file) stores these values in FormulaResult.LambdaValue, enabling higher-order operations like REDUCE, MAP, and SCAN.

Cross-Sheet Evaluation and Performance

The FormulaEvalSession class provides per-session memoization for:

• Cell evaluation results
• Range materializations
• Sheet evaluator instances

This cache prevents O(n²) blow-up when formulas reference other sheets. Defined names are inlined or tokenized on-the-fly through GetDefinedNames and TryDefinedNameAsSimpleRef.

The engine includes stack-overflow guards via MaxSameSheetDepth and RuntimeHelpers.TryEnsureSufficientExecutionStack (lines 48-50).

Error Handling and Excel Compatibility

Errors propagate through FormulaResult.Error with standard Excel codes: #DIV/0!, #NUM!, #VALUE!, #REF!, #NAME?, #N/A. The engine mimics Excel's coercion rules:

• Blank cells evaluate to 0 in arithmetic contexts
• Numeric-looking strings parse on demand
• Infinity maps to #NUM!

Practical Usage Examples

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"

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
}

LET and LAMBDA

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

Cross-Sheet with Memoization

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");

Key Source Files

File	Responsibility
[FormulaEvaluator.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.cs)	Tokenizer, parser, evaluation engine, result types
[FormulaEvaluator.Functions.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Functions.cs)	350+ function implementations
[FormulaEvaluator.SpecialFunctions.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.SpecialFunctions.cs)	LET, LAMBDA, higher-order functions
[FormulaEvaluator.Helpers.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Helpers.cs)	Numeric utilities, array helpers
[FormulaEvaluator.Solver.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Solver.cs)	Iterative solvers (RATE, IRR)
[FormulaEvaluator.References.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.References.cs)	Cross-sheet logic, defined names
[FormulaEvaluator.Statistics.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Statistics.cs)	Statistical functions
[FormulaEvaluator.Regression.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Regression.cs)	LINEST, TREND, GROWTH
[FormulaEvaluator.Securities.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Securities.cs)	Financial calculations

Summary

• Tokenization and parsing in FormulaEvaluator.cs handle Excel's full formula syntax including array literals and modern operators
• 350+ functions dispatch through EvalFunction in FormulaEvaluator.Functions.cs with consistent FormulaResult wrapping
• Dynamic arrays spill via RangeData objects with preserved origin coordinates for ROW/COLUMN resolution
• LET/LAMBDA enable user-defined functions and functional programming patterns
• Session-based memoization delivers efficient cross-sheet evaluation without recursive re-evaluation

Frequently Asked Questions

Which dynamic array functions does OfficeCLI implement?

OfficeCLI implements the complete modern Excel dynamic-array suite including SEQUENCE, SORT, SORTBY, UNIQUE, FILTER, EXPAND, HSTACK, VSTACK, WRAPROWS, WRAPCOLS, TEXTSPLIT, XLOOKUP, XMATCH, and the functional programming helpers MAP, BYROW, BYCOL, SCAN, and REDUCE. These return RangeData objects that automatically spill into adjacent cells.

How does the formula evaluator prevent stack overflow on complex workbooks?

The evaluator implements two safeguards: MaxSameSheetDepth limits recursion depth within a single sheet, and RuntimeHelpers.TryEnsureSufficientExecutionStack checks available stack space before deep recursive calls. Additionally, the FormulaEvalSession cache eliminates re-evaluation cycles that could otherwise cause exponential recursion.

Can OfficeCLI evaluate formulas that reference other worksheets?

Yes. The FormulaEvalSession class maintains memoized evaluators for each referenced sheet. Cross-sheet formulas like =Sheet2!B3 + 10 resolve through FormulaEvaluator.References.cs, with defined names inlined via TryDefinedNameAsSimpleRef for optimal performance.

Does OfficeCLI support Excel's new LAMBDA and LET functions?

Full support exists through EvalLet and MakeLambda in FormulaEvaluator.SpecialFunctions.cs. LET binds variables to intermediate results; LAMBDA captures参数 names and tokenized bodies for later invocation. The Lambda record stores these in FormulaResult.LambdaValue, enabling higher-order operations compatible with MAP, REDUCE, and SCAN.

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