How the OfficeCLI Formula Evaluation Engine Works: Architecture and Supported Excel Functions

OfficeCLI's formula evaluation engine is a full-featured Excel-compatible parser written in C# that tokenizes, parses, and evaluates formulas across three partial classes, supporting over 350 Excel functions including dynamic arrays, LAMBDA, and LET.

The OfficeCLI project provides a command-line interface for manipulating Excel files programmatically. At its core lies a complete formula evaluation engine implemented in OfficeCli.Core that can parse and compute Excel formulas without requiring Microsoft Excel to be installed. This engine handles everything from basic arithmetic to complex dynamic arrays, making it possible to evaluate cell values, generate HTML previews, and perform batch calculations on workbooks.

Architecture of the Formula Evaluation Engine

The engine is architected as a recursive-descent parser with memoization and is split across three partial class files to separate concerns.

Core Files and Responsibilities

The implementation spans three primary files under src/officecli/Core/Formula/:

• FormulaEvaluator.cs – Contains the tokenizer, recursive-descent parser, cell-reference resolver, and the FormulaEvalSession management system (lines 5-21 for entry point, lines 91-150 for tokenization).
• FormulaEvaluator.Functions.cs – Dispatches more than 350 Excel functions across categories like Math, Text, Date/Time, Logical, Lookup, and Financial (function switch at lines 36-555).
• FormulaEvaluator.Helpers.cs – Provides utilities for numeric coercion, range expansion, error handling, and support for LAMBDA and LET constructs.

Evaluation Entry Point

Clients such as ExcelHandler, the officecli command line, or the HTML preview renderer instantiate the evaluator and call TryEvaluateFull:

var result = new Core.FormulaEvaluator(sheetData, workbookPart)
                 .TryEvaluateFull(formulaString);

According to the source code in FormulaEvaluator.cs (lines 5-21), TryEvaluateFull catches name-resolution errors and returns a nullable FormulaResult?. Callers strip the leading "=" character before passing the formula string, as seen in ExcelHandler.Set.Cells.cs at line 356.

Session Management and Memoization

The FormulaEvalSession class (lines 94-140 in FormulaEvaluator.cs) maintains state throughout an evaluation sweep to optimize performance and prevent errors:

• CellMemo – Caches results of fully evaluated cells (e.g., Sheet!A1 → FormulaResult) to avoid recomputing dependencies.
• RangeMemo – Stores materialized ranges from functions like OFFSET or spilled arrays.
• Visiting – Tracks the current evaluation stack to detect circular references on the same sheet.
• Recursion Protection – MaxSameSheetDepth (lines 51-52) and RuntimeHelpers.TryEnsureSufficientExecutionStack guard against stack-overflow attacks from deeply nested formulas.

All evaluators processing cross-sheet data share the same session instance, eliminating O(n²) performance degradation in workbooks with many inter-linked formulas.

How the Formula Engine Parses Expressions

Tokenization Process

The Tokenize method (lines 91-150 in FormulaEvaluator.cs) performs lexical analysis on the raw formula string, emitting a list of Token objects. The tokenizer recognizes:

• Numbers, strings, booleans, and error literals (#DIV/0!)
• Cell references (A1, 'Sheet 1'!B2) and ranges (A1:B3)
• Operators, parentheses, commas, and function names
• Excel-specific syntax including unary +/- signs, the % postfix operator, array literals {1,2;3,4}, and quoted sheet names

Defined names (named ranges) are either inlined or emitted as reference tokens during this phase (lines 73-114).

Recursive-Descent Parser

Parsing begins at ParseExpression (line 73) and proceeds through a hierarchy matching Excel's operator precedence:

Expression → Comparison → Concatenation → Add/Sub → Mul/Div → Power → Unary → Postfix → Atom

Each level returns a FormulaResult?. The parser includes a depth guard (_parseDepth compared against DocumentLimits.MaxRecursionDepth at lines 443-447) to prevent pathological nesting attacks.

Array-aware operators (+, *, ^, &, comparisons) automatically broadcast scalar arguments to match the shape of larger operands via ApplyBinaryOp (lines 212-227).

Function Dispatch

When the parser encounters a FUNC token, it calls EvalFunction (lines 13-14 in FormulaEvaluator.Functions.cs). This method:

1. Extracts arguments already evaluated to FormulaResult or raw arrays
2. Propagates errors early unless the function is listed in ErrorTransparentFns
3. Automatically "spills" liftable scalar functions (like SUM, ABS, ROUND) over arrays (lines 29-35)
4. Routes to a massive switch statement (lines 36-555) mapping function names to concrete implementations

Supported Excel Functions (350+)

The engine implements over 350 distinct Excel functions organized by category. The switch statement in FormulaEvaluator.Functions.cs handles the dispatch, with many functions delegating to specialized helpers like EvalSumProduct, EvalXlookup, or EvalConvert.

Mathematical and Aggregation Functions

• Basic: SUM, AVERAGE, MIN, MAX, PRODUCT
• Rounding: ROUND, CEILING_MATH, FLOOR, MOD
• Power/Log: POWER, SQRT, LOG, EXP
• Random: RAND, RANDBETWEEN

Statistical Functions

MEDIAN, STDEV, VAR.P, CORREL, PERCENTILE_INC, QUARTILE_EXC, and various distribution functions.

Logical Functions

IF, IFS, AND, OR, XOR, TRUE, FALSE, NOT, and SWITCH.

Text Functions

LEFT, RIGHT, MID, LEN, UPPER, LOWER, CONCAT, TEXTJOIN, plus modern regex functions REGEXTEST and REGEXREPLACE.

Lookup and Reference Functions

VLOOKUP, HLOOKUP, XLOOKUP, XMATCH, INDEX, MATCH, OFFSET, INDIRECT, ADDRESS, and CHOOSE.

Date and Time Functions

TODAY, NOW, DATE, YEAR, MONTH, DAY, NETWORKDAYS, EOMONTH, EDATE, and DATEDIF.

Financial Functions

PMT, FV, PV, IRR, XIRR, NPV, XNPV, and RATE.

Engineering and Conversion

BITAND, BITOR, BITLSHIFT, COMPLEX, IMABS, IMEXP, BIN2DEC, DEC2HEX, and CONVERT.

Dynamic Array Functions

Modern spill-capable functions including SEQUENCE, SORT, UNIQUE, FILTER, WRAPROWS, TOCOL, BYROW, and BYCOL.

Advanced Capabilities

Dynamic Arrays and Spill Behavior

The engine natively supports Excel's dynamic array formulas. Functions returning arrays (like SEQUENCE or FILTER) produce a RangeData object stored in FormulaResult. The RangeMemo cache stores these materialized ranges to prevent rebuilding them for every dependent formula, enabling efficient spill behavior across the calculation graph.

LAMBDA and LET Implementation

Advanced functional programming constructs are fully supported:

• LET – Binds names to values within the current evaluation scope (implementation at lines 998-1021 in FormulaEvaluator.cs). This allows complex formulas to define intermediate calculations without helper cells.
• LAMBDA – Creates a reusable function definition stored as a Lambda record (lines 28-32) containing parameter lists and unevaluated token streams. Immediate invocation syntax LAMBDA(...)(args) is handled in ParsePostfix (lines 556-558) via the InvokeLambda method.

Error Handling

Excel error literals (#DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, #NULL!) are represented by FormulaResult.Error objects. Operations check IsError first and propagate the first scalar error encountered unless the function explicitly handles them (e.g., AGGREGATE or SUMPRODUCT with error ignoring).

The TEXT and NUMBERVALUE functions optionally integrate with a NumberFormatProvider supplied by the Excel handler (lines 998-1001), falling back to .NET standard formatting when not available.

Practical Implementation Examples

Evaluating Formulas in C#

using OfficeCli.Core;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

// Load workbook and access sheet data
using var pkg = SpreadsheetDocument.Open("sample.xlsx", false);
var ws = pkg.WorkbookPart!.WorksheetParts.First();
var sheetData = ws.Worksheet.GetFirstChild<SheetData>()!;

// Create evaluator with shared session for performance
var eval = new FormulaEvaluator(sheetData, pkg.WorkbookPart);

// Simple scalar evaluation
var sumResult = eval.TryEvaluateFull("SUM(A1:A5)");
Console.WriteLine(sumResult?.AsNumber());   // → 123.45

// Dynamic array spill evaluation
var spill = eval.TryEvaluateFull("SEQUENCE(3,2,10,5)");
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:
    // 10   15
    // 20   25
    // 30   35
}

// LAMBDA and LET usage
var lambdaResult = eval.TryEvaluateFull(
    "LET(x, 5, y, 3, LAMBDA(a,b, a*b)(x, y))");
Console.WriteLine(lambdaResult?.AsNumber()); // → 15

Command-Line Usage

# View evaluated cell value

officecli view text sample.xlsx /Sheet1/B2

# Evaluate formula directly

officecli eval "TEXT(44561,\"yyyy-mm-dd\")"

# Generate HTML preview with evaluated formulas

officecli view html sample.xlsx --output preview.html

Summary

• OfficeCLI implements a complete Excel-compatible formula evaluation engine in C# under OfficeCli.Core, spanning three partial class files.

• The architecture uses a recursive-descent parser with FormulaEvalSession for memoization, circular reference detection, and recursion protection.

• Over 350 Excel functions are supported across mathematical, statistical, logical, text, lookup, date/time, financial, and engineering categories.

• Modern Excel features including dynamic arrays (SEQUENCE, FILTER), LET for variable binding, and LAMBDA for custom functions are fully implemented.

• The engine integrates with ExcelHandler for CLI operations and can be instantiated programmatically for custom .NET applications.

Frequently Asked Questions

How does OfficeCLI handle circular references in formulas?

The engine detects circular references through the Visiting hash set in FormulaEvalSession (lines 94-140 in FormulaEvaluator.cs), which tracks the current evaluation stack on a per-sheet basis. If a formula attempts to evaluate a cell already in the visiting set, the engine raises a circular reference error rather than entering infinite recursion.

Can I use OfficeCLI to evaluate Excel formulas without having Microsoft Excel installed?

Yes. The formula evaluation engine is a standalone C# implementation that does not depend on Microsoft Excel or the Excel Interop libraries. It parses and calculates formulas using the Open XML SDK to read workbook data and the internal parser to compute results, making it suitable for server environments and automation pipelines.

Which dynamic array functions are supported by the formula engine?

The engine supports modern spill-capable functions including SEQUENCE, SORT, SORTBY, UNIQUE, FILTER, WRAPROWS, WRAPCOLS, TOCOL, TOROW, BYROW, and BYCOL. These functions return RangeData objects that can spill into adjacent cells during evaluation, with results cached in RangeMemo for efficient dependency tracking.

How does the engine optimize performance for workbooks with many interdependent formulas?

Performance is optimized through the FormulaEvalSession object, which is shared across all evaluators when processing cross-sheet dependencies. This session maintains CellMemo for caching evaluated cell results and RangeMemo for materialized ranges, preventing O(n²) recomputation cycles. Additionally, MaxSameSheetDepth limits and runtime stack probes prevent stack overflow from deeply nested calculations.

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