How OfficeCLI's Formula Engine Evaluates Excel Functions and Handles Dynamic Arrays

OfficeCLI implements a three-layered C# formula engine that tokenizes and parses Excel syntax into an AST, dispatches over 350 built-in functions through a centralized switch table, and supports modern dynamic array behaviors including spill ranges, implicit intersection, and element-wise lifting.

OfficeCLI's formula engine provides a complete Excel-compatible calculation environment written in C#, enabling the CLI to evaluate workbook formulas without requiring Microsoft Excel. The engine handles everything from basic arithmetic to complex dynamic array functions like SEQUENCE and FILTER, mirroring Excel 365's behavior through a sophisticated tokenization, parsing, and evaluation pipeline.

Architecture of the Formula Engine

The engine separates concerns into three distinct logical layers that transform formula strings into calculated values while preventing exponential re-evaluation through intelligent memoization.

Tokenization and Parsing

The evaluation pipeline begins in FormulaEvaluator.cs where the Tokenize method scans formula strings and emits typed tokens for numbers, strings, cell references, ranges, operators, functions, and array literals. A recursive-descent parser then constructs an abstract syntax tree (AST) through methods like ParseExpression, ParseComparison, and ParseAtom, properly handling operator precedence, parentheses, and special tokens such as the % postfix operator.

The parser recognizes Excel-specific syntax including array literals like {1,2;3,4} through dedicated handling in ParseArrayConstant, which builds a RangeData object representing the grid structure.

Evaluation Session Management

To prevent infinite recursion and redundant calculations when formulas reference cells across multiple worksheets, the engine uses a FormulaEvalSession instance created per workbook. This session, defined in FormulaEvaluator.cs, stores memoized cell results, maintains cross-sheet evaluator references, tracks visited nodes for cycle detection, and caches range materializations.

When a formula references another cell that hasn't been evaluated yet, ResolveCellResult or ResolveSheetCellResult lookups consult this session cache first, ensuring that complex dependency chains evaluate efficiently without exponential overhead.

Function Dispatch System

After AST construction, function calls route through EvalFunction in FormulaEvaluator.Functions.cs, which implements a massive switch statement containing over 350 Excel functions. Each case forwards to dedicated evaluation helpers such as EvalSumProduct, EvalIf, EvalDate, or EvalTextSplit, all residing in the same partial class.

The dispatcher recognizes modern Excel functions through ModernFunctionQualifier.cs, which handles the _xlfn. namespace prefix used by newer functions to ensure backward compatibility when workbooks store qualified names.

Dynamic Array and Spill Range Support

OfficeCLI fully implements Excel 365's dynamic array behaviors, allowing functions to return rectangular spill ranges that automatically populate adjacent cells rather than single scalar values.

Array Literals and Parser Support

The tokenizer recognizes array literal syntax ({1,2;3,4}) through the TT.ArrayLit token type. The ParseArrayConstant method (lines 800-825 in FormulaEvaluator.cs) parses semicolons as row separators and commas as column separators, constructing a RangeData object wrapped in a FormulaResult.Area return type.

This allows users to define inline matrices that participate in calculations exactly as they would in Excel's grid.

Spill-Aware Functions (SEQUENCE, FILTER, etc.)

Dynamic array functions such as SEQUENCE, FILTER, and SORT live in the Dynamic arrays / spill region of EvalFunction. These implementations return RangeData objects (or arrays) wrapped as FormulaResult.Area instances rather than single values.

When the top-level EvaluateFormula method encounters these area results in contexts expecting scalar values, it performs implicit intersection by returning the first element, matching Excel's behavior when referencing a spill range from a single cell.

Element-Wise Lifting for Scalar Functions

Scalar functions marked as liftable in the LiftableScalarFunctions set (such as SIN, COS, and ABS) automatically map over each element of array arguments through the TryLiftOverArrays method. This automatic vectorization produces spilled arrays without requiring each function to implement explicit array logic.

For example, =ABS({-1,2;-3,4}) evaluates to {1,2;3,4} through automatic lifting rather than requiring array-specific code in the ABS implementation.

Implicit Intersection and Broadcasting

When binary operators encounter mixed operands (one grid and one scalar), the ApplyBinaryOp method broadcasts the scalar across the grid dimensions. If dimensions mismatch, out-of-range cells emit #N/A errors, replicating Excel's element-wise arithmetic rules.

In scalar contexts consuming an array result (such as =B1:B3*1 in a single cell), EvaluateFormula collapses the spill to its top-left cell value through implicit intersection, maintaining compatibility with pre-dynamic-array Excel behavior while supporting modern spill semantics.

Evaluation Flow and Result Handling

The complete evaluation process follows five distinct phases:

1. Parse – Tokenize converts the formula string into tokens, then ParseExpression builds the AST.
2. Resolve references – ResolveCellResult checks the session memo cache or recursively evaluates dependencies.
3. Execute – EvalFunction dispatches to the appropriate implementation, with automatic lifting applied to eligible scalar functions.
4. Spill handling – Array-producing functions return RangeData; downstream scalar contexts trigger implicit intersection or broadcasting via ApplyBinaryOp.
5. Result conversion – FormulaResult provides typed accessors (NumericValue, StringValue, BoolValue, ErrorValue, ArrayValue, RangeValue) with ToCellValueText rendering final output for XML serialization or CLI display.

This pipeline ensures that ExcelHandler.View.cs can create evaluators for specific worksheets and format results for the officecli view command while maintaining full fidelity with Excel's calculation engine.

Code Examples and CLI Usage

The following examples demonstrate how to interact with the formula engine through OfficeCLI commands:

# Evaluate a simple arithmetic formula in cell C3

officecli view myworkbook.xlsx --cell C3

# Internally: new FormulaEvaluator(sheetData, workbookPart)

# 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 3×2 RangeData

# Values spill into D1:E3 starting at 10 with step 5

# Demonstrate automatic array lifting with ABS

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

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

# Result: 2×2 spill with values {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) for scalar context

Summary

• Three-layer architecture: Tokenization/parser (FormulaEvaluator.cs), session-based memoization (FormulaEvalSession), and function dispatch (EvalFunction switch with 350+ functions).
• Dynamic array support: Full implementation of spill ranges, array literals ({1,2;3,4}), and modern functions like SEQUENCE and FILTER through RangeData objects.
• Automatic vectorization: Scalar functions in LiftableScalarFunctions automatically lift over arrays via TryLiftOverArrays, while ApplyBinaryOp handles broadcasting.
• Excel compatibility: Implicit intersection and error propagation match Excel 365 behavior, with ModernFunctionQualifier.cs handling _xlfn. prefixes for newer functions.
• CLI integration: ExcelHandler.View.cs exposes the engine through the officecli view command, supporting both formula evaluation and dynamic array generation.

Frequently Asked Questions

Does OfficeCLI support all Excel 365 dynamic array functions?

OfficeCLI implements the core dynamic array infrastructure including SEQUENCE, FILTER, SORT, and spill range handling. The EvalFunction dispatcher in FormulaEvaluator.Functions.cs contains a dedicated region for these functions, and the engine automatically handles spill behaviors through RangeData objects and implicit intersection logic.

How does the engine prevent infinite loops when cells reference each other?

The FormulaEvalSession class maintains a visited-node set that tracks which cells are currently being evaluated. Before recursing into a cell reference, ResolveCellResult checks this set; if a cycle is detected, the engine halts evaluation and returns an appropriate error value rather than entering infinite recursion.

Can OfficeCLI evaluate formulas containing custom user-defined functions?

Currently, the engine supports the 350+ built-in Excel functions implemented in FormulaEvaluator.Functions.cs through the EvalFunction switch table. Custom user-defined functions would require extending the dispatcher, though the architecture supports adding new function cases to the switch statement or implementing additional handlers in the partial class files.

Why does multiplying a range by a scalar only show one result in OfficeCLI?

When a formula like =B1:B3*1 appears in a single cell context, EvaluateFormula performs implicit intersection per Excel's backward-compatibility rules, returning only the first element of the spill range. To see the full array result, the formula must be entered in a cell with sufficient adjacent space to accommodate the spill, or viewed using the --eval flag which displays the complete RangeData output.

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