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

OfficeCLI implements a full-featured Excel formula engine in C# that parses, evaluates, and spills dynamic arrays across more than 350 functions through a layered architecture of tokenization, AST parsing, and function dispatch.

The iOfficeAI/OfficeCLI repository provides a command-line interface for manipulating Excel workbooks without Microsoft Office installed. At its core, the OfficeCLI Excel formula evaluator replicates modern Excel's calculation engine, supporting dynamic array spill behavior and an extensive function library entirely in managed code.

Three-Layer Architecture of the Formula Engine

The evaluation system is organized into three distinct logical layers that transform formula strings into calculated results.

Tokenization and Parsing

The process begins in FormulaEvaluator.cs where the Tokenize method scans formula strings and emits tokens for numbers, strings, cell references, ranges, operators, and functions. A recursive-descent parser (ParseExpression → ParseComparison → … → ParseAtom) constructs an abstract syntax tree while respecting operator precedence and handling special tokens such as the % postfix operator.

According to the source code in [FormulaEvaluator.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.cs#L91-L150), this layer handles array literals like {1,2;3,4} through dedicated token types and parsing logic.

Evaluation Session Management

Each workbook utilizes a FormulaEvalSession instance that persists across FormulaEvaluator instances. This session stores memoized cell results, cross-sheet evaluators, visited-node sets for cycle detection, and cached range materializations.

As implemented in lines 94-114 of FormulaEvaluator.cs, this caching layer prevents exponential re-evaluation when formulas reference each other across multiple worksheets.

Function Dispatch System

After parsing, function calls route through the EvalFunction method, which contains a switch statement with over 350 cases covering Excel's function library. Each case forwards to dedicated helpers such as EvalSumProduct, EvalIf, EvalDate, or EvalTextSplit located in the partial class file FormulaEvaluator.Functions.cs.

The dispatch table resides in [FormulaEvaluator.Functions.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.Functions.cs#L13-L108), where modern functions qualified with the _xlfn. namespace are handled via ModernFunctionQualifier.cs.

Dynamic Array and Spill Behavior

OfficeCLI mirrors Excel 365's dynamic array capabilities through several specialized mechanisms that handle array literals, spill ranges, and implicit intersections.

Array Literals and Parsing

Array constants like {1,2;3,4} are recognized during tokenization via the TT.ArrayLit token type. The ParseArrayConstant method parses semicolon-separated rows and comma-separated columns, building a RangeData object that returns an Area result.

This parsing logic appears in lines 334-346 and 800-825 of FormulaEvaluator.cs, enabling the engine to treat array literals as first-class range objects.

Spill-Aware Functions

Native dynamic array functions—including SEQUENCE, FILTER, and SORT—are implemented in the Dynamic arrays / spill region of EvalFunction. These functions return RangeData objects wrapped as FormulaResult.Area instances.

When a downstream scalar context consumes the result, the top-level EvaluateFormula method automatically performs implicit intersection, returning the first element when a single value is expected. This behavior is defined in lines 92-108 of FormulaEvaluator.Functions.cs.

Element-Wise Lifting

Scalar functions marked as liftable (such as SIN and ABS) automatically map over each element of an array argument through the TryLiftOverArrays method. The LiftableScalarFunctions registry in lines 30-45 of FormulaEvaluator.cs identifies which functions support this vectorization.

This mechanism produces spilled arrays without requiring explicit array logic in each function implementation.

Implicit Intersection and Broadcasting

When an Area or array result appears where a scalar is required, EvaluateFormula collapses the spill to its top-left cell, matching Excel's pre-dynamic-array intersect behavior. For binary operations, the ApplyBinaryOp method (lines 176-194) broadcasts scalars across grid operands and emits #N/A for dimension mismatches.

Supporting 350+ Excel Functions

The engine's breadth comes from the extensive EvalFunction switch statement that dispatches to specialized evaluation logic. Helper methods reside across three primary files:

• FormulaEvaluator.Functions.cs: Core function implementations including dynamic array verbs
• FormulaEvaluator.Helpers.cs: Numeric utilities, comparison helpers, and error propagation
• ModernFunctionQualifier.cs: Namespace handling for newer Excel functions prefixed with _xlfn.

Each function returns a FormulaResult structure providing NumericValue, StringValue, BoolValue, ErrorValue, ArrayValue, or RangeValue, with ToCellValueText rendering final values for XML serialization or CLI display.

Practical Usage Examples

The CLI exposes the evaluator through commands that demonstrate the engine's capabilities:

# Evaluate a simple arithmetic formula referencing cells

officecli view myworkbook.xlsx --cell C3

# Internally: new FormulaEvaluator(sheetData, workbookPart)

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

# Write a dynamic array spill range using SEQUENCE

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

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

# ExcelHandler writes spill values into appropriate cells

# Demonstrate automatic array lifting with ABS

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

# ABS is in LiftableScalarFunctions, so the engine maps over each element

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

# Implicit intersection when multiplying range by scalar

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

# ApplyBinaryOp sees RangeData × scalar

# EvaluateFormula collapses to first cell (B1) before writing

Summary

• OfficeCLI implements a complete Excel formula evaluator in C# with three logical layers: tokenization/parsing, session management, and function dispatch.

• The engine supports dynamic arrays through array literal parsing, spill-aware functions, automatic element-wise lifting, and implicit intersection.

• 350+ functions are dispatched via a switch statement in FormulaEvaluator.Functions.cs, with modern functions handled through namespace qualification.

• Evaluation sessions cache results and detect cycles to prevent exponential re-evaluation across complex workbooks.

• Binary operators support broadcasting rules that match Excel's element-wise arithmetic behavior.

Frequently Asked Questions

How does OfficeCLI handle Excel's new dynamic array functions like SEQUENCE and FILTER?

The EvalFunction method in FormulaEvaluator.Functions.cs contains dedicated cases for dynamic array functions. When SEQUENCE or FILTER is called, the corresponding helper (e.g., EvalSequence) returns a RangeData object wrapped as FormulaResult.Area. The top-level EvaluateFormula method then manages spill behavior, automatically intersecting or broadcasting results based on the consuming context.

What prevents circular reference errors when evaluating complex workbooks?

The FormulaEvalSession class maintains a visited-node set that tracks cells currently under evaluation. Before recursing into a referenced cell, the engine checks this set in ResolveCellResult. If a cycle is detected, the evaluation halts appropriately, preventing infinite loops and stack overflow errors during cross-sheet formula resolution.

Can scalar functions like SIN or ABS work with array inputs automatically?

Yes. The engine maintains a LiftableScalarFunctions registry in FormulaEvaluator.cs. When a function in this set receives an array argument, the TryLiftOverArrays method automatically maps the scalar operation over each element, producing a spilled array result without requiring explicit array handling code in each function implementation.

How does the evaluator manage performance with interdependent formulas?

A shared FormulaEvalSession instance caches memoized cell results across all FormulaEvaluator instances within a workbook. When ResolveCellResult or ResolveSheetCellResult encounters a previously calculated cell, it returns the cached value rather than re-evaluating the formula tree, eliminating exponential re-evaluation penalties in deeply referenced worksheets.

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