How OfficeCLI's Excel Formula Engine Evaluates Dynamic Array Functions

OfficeCLI implements a full-featured Excel formula engine in C# that evaluates dynamic array functions through tokenization, recursive-descent parsing, function dispatch with automatic array lifting, and spill-range handling with implicit intersection.

OfficeCLI is an open-source command-line interface for manipulating Excel workbooks without requiring Microsoft Office installation. Its Excel formula engine evaluates dynamic array functions such as SEQUENCE, FILTER, and SORT by tokenizing formulas, building an abstract syntax tree, and executing functions that return rectangular spill ranges. According to the iOfficeAI/OfficeCLI source code, the engine supports over 350 Excel functions through a layered architecture that includes automatic array lifting and implicit intersection handling.

The Three-Layer Evaluation Architecture

OfficeCLI's formula evaluation splits into distinct logical layers: tokenization and parsing, session management, and function dispatch. This separation enables efficient handling of complex workbook calculations while supporting modern Excel features.

Tokenization and Recursive-Descent Parsing

The entry point for formula evaluation resides in FormulaEvaluator.cs, where the Tokenize method (lines 91-150) scans formula strings into discrete tokens including numbers, cell references, operators, and the TT.ArrayLit type for array literals. A recursive-descent parser then constructs an abstract syntax tree through methods like ParseExpression, ParseComparison, and ParseAtom, respecting operator precedence and parentheses along the way.

For array constants such as {1,2;3,4}, the parser invokes ParseArrayConstant (lines 800-825) to distinguish row separators (;) from column separators (,), ultimately building a RangeData object that represents the rectangular grid in memory.

Session Management and Memoization

To prevent exponential re-evaluation when formulas reference each other across worksheets, the engine creates a FormulaEvalSession (lines 94-114 in FormulaEvaluator.cs) for each workbook. This session stores memoized cell results, tracks visited nodes for cycle detection, and caches range materializations. When a dynamic array function spills into dependent cells, the session ensures those values remain available for subsequent formula evaluations without recalculation.

Function Dispatch System

After parsing reduces an expression to an AST, function calls route through EvalFunction in FormulaEvaluator.Functions.cs (lines 13-108). This method implements a dispatch table via a switch statement covering more than 350 Excel functions, forwarding each to dedicated helpers like EvalSumProduct, EvalIf, or EvalSequence. The dynamic array functions reside in a dedicated region of this file, specifically lines 92-108, where they return RangeData objects wrapped as FormulaResult.Area instances.

Dynamic Array (Spill) Evaluation Mechanisms

Modern Excel's dynamic array behavior requires specific handling for spilled ranges, array literals, and scalar-to-array operations. OfficeCLI mirrors Excel 365's behavior through four key mechanisms.

Array Literal Recognition

During tokenization, the engine recognizes array literals through TT.ArrayLit tokens. When ParseArrayConstant encounters these tokens, it constructs a multi-dimensional RangeData structure that represents the literal grid. This allows formulas like ={1,2,3} to evaluate immediately as a spilled horizontal array without requiring a surrounding function call.

Spill-Aware Function Implementation

Functions specifically designed to return dynamic arrays—such as SEQUENCE, FILTER, SORT, and UNIQUE—are implemented in the dynamic arrays region of EvalFunction (lines 92-108). These functions return RangeData objects representing their full rectangular output. The top-level EvaluateFormula method automatically handles implicit intersection when a downstream scalar context consumes the spill, extracting the first element (top-left cell) to match Excel's legacy behavior.

Automatic Scalar Lifting

Scalar functions that do not natively handle arrays can still operate on dynamic arrays through automatic lifting. The engine maintains a LiftableScalarFunctions list and applies TryLiftOverArrays (lines 30-45 in FormulaEvaluator.cs) to map functions like ABS or SIN over each element of an input array. This produces a spilled result without requiring explicit array logic inside each function implementation.

Implicit Intersection and Broadcasting

When binary operators encounter mixed scalar and array operands, ApplyBinaryOp (lines 176-194) implements broadcasting rules matching Excel's behavior. If one operand is a grid (RangeData) and the other is scalar, the scalar broadcasts across the grid. When a spill result appears where a single value is required, EvaluateFormula (lines 60-66) collapses the array to its first element. Mismatched dimensions during broadcasting emit #N/A errors for out-of-range cells, ensuring compatibility with Excel's error propagation.

Evaluation Flow from Parse to Result

The complete evaluation lifecycle follows five distinct phases:

1. Parse – Tokenize generates tokens, then ParseExpression builds the AST.
2. Resolve – ResolveCellResult and ResolveSheetCellResult look up cached values or recursively evaluate dependencies using the session's memoization cache.
3. Execute – EvalFunction dispatches to specific implementations, with scalar functions potentially lifted via TryLiftOverArrays.
4. Spill handling – Functions returning arrays produce RangeData objects; downstream scalar contexts trigger implicit intersection through EvaluateFormula.
5. Result conversion – The engine converts final values through FormulaResult types (NumericValue, StringValue, BoolValue, ArrayValue, RangeValue) into cell output via ToCellValueText, rendering values for XML or CLI display.

Working with Dynamic Arrays in OfficeCLI

The officecli view command exposes the formula engine's capabilities for testing dynamic array behavior directly from the terminal.

Evaluate a simple arithmetic formula:

officecli view myworkbook.xlsx --cell C3

# Internally: new FormulaEvaluator(sheetData, workbookPart)

# evaluator.TryEvaluateFull("=A1+B2") returns a numeric FormulaResult

Create a spilled sequence using the SEQUENCE function:

officecli view myworkbook.xlsx --cell D1 \
  --set "D1=SEQUENCE(3,2,10,5)"

# Writes a 3×2 spill range starting at 10 with step 5

# EvalFunction("SEQUENCE", args) → EvalSequence → returns RangeData (3×2)

Demonstrate automatic array lifting with the ABS function:

officecli view myworkbook.xlsx --eval "ABS({-1,2;-3,4})"

# ABS is in LiftableScalarFunctions, so TryLiftOverArrays maps over each element

# Result: a 2×2 spill with {1,2;3,4}

Show implicit intersection behavior:

officecli view myworkbook.xlsx --cell E5 \
  --set "E5=B1:B3*1"

# ApplyBinaryOp sees RangeData × scalar

# EvaluateFormula collapses to first cell (B1) before writing to E5

Summary

• OfficeCLI's formula engine separates concerns into tokenization/parsing, session management, and function dispatch to handle complex Excel calculations efficiently.
• Dynamic array support includes spill-aware functions like SEQUENCE and FILTER, automatic scalar lifting for functions like ABS, and proper handling of array literals through ParseArrayConstant.
• Implicit intersection automatically collapses spilled arrays to single values when scalar contexts require it, while broadcasting rules allow arithmetic between scalars and grids.
• Memoization via FormulaEvalSession prevents exponential re-evaluation and detects circular references across worksheet dependencies.

Frequently Asked Questions

How does OfficeCLI handle implicit intersection when a scalar references a spilled array?

When a formula expects a single value but receives a spilled array, the EvaluateFormula method (lines 60-66 in FormulaEvaluator.cs) automatically performs implicit intersection by returning the first element (top-left cell) of the spill range. This matches Excel's backward-compatible behavior where pre-dynamic-array formulas referencing an entire range would receive only the intersecting cell value.

What mechanism allows scalar functions like ABS to operate on dynamic arrays?

OfficeCLI implements automatic array lifting through the TryLiftOverArrays method (lines 30-45 in FormulaEvaluator.cs). Functions listed in LiftableScalarFunctions automatically map across each element of an array argument, producing a new array result without requiring explicit array logic inside the function implementation. This allows scalar mathematical functions to produce spilled outputs when given array inputs.

How does the evaluation session prevent circular references during formula calculation?

The FormulaEvalSession class (lines 94-114 in FormulaEvaluator.cs) maintains a visited-node set that tracks cells currently undergoing evaluation. When a formula references another cell that is already in the evaluation stack, the engine detects the cycle and can halt evaluation or return a circular reference error, preventing infinite recursion in interdependent dynamic array formulas.

Which source file handles modern Excel function names prefixed with _xlfn.?

The ModernFunctionQualifier.cs file in src/officecli/Core/Formula/ manages the _xlfn. namespace prefix used by newer Excel functions. This utility ensures that functions stored with qualified names in modern workbooks are correctly recognized and dispatched to their appropriate handlers in EvalFunction, bridging compatibility between different Excel versions.

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