OfficeCLI Formula Evaluator Dynamic Arrays: Implementation Guide

OfficeCLI evaluates Excel dynamic array formulas by detecting spill functions via hash-set lookups, qualifying names with _xlfn. prefixes for OOXML compliance, and writing anchor cells with XLDAPR metadata so Excel handles the actual spill rendering.

OfficeCLI is an open-source command-line tool for reading, writing, and manipulating Office documents. Its formula evaluator supports modern Excel dynamic arrays—functions like SEQUENCE, FILTER, and SORT that spill results across multiple cells—allowing you to programmatically generate workbooks with contemporary Excel formulas that open correctly in Microsoft Excel.

Detecting Dynamic Array Functions

The static class ModernFunctionQualifier serves as the gatekeeper for identifying formulas that require dynamic array handling. Located in src/officecli/Core/Formula/ModernFunctionQualifier.cs, this class maintains three critical hash sets that categorize modern Excel functions:

• XlfnFunctions – Functions requiring the _xlfn. prefix (e.g., SEQUENCE, SORT, UNIQUE)
• XlwsFunctions – Functions requiring the _xlfn._xlws. prefix (currently only FILTER)
• DynamicArrayFunctions – Functions that produce spill arrays (includes the above plus others like MAP)

The method IsDynamicArrayFormula(string formula) scans raw formula strings while skipping quoted literals. It returns true immediately upon detecting any function call present in DynamicArrayFunctions, signaling to the evaluator that spill handling logic must be activated.

Qualifying Function Names for OOXML Compliance

Excel requires specific XML namespaces for modern functions to prevent #NAME? errors when files load. The Qualify method in ModernFunctionQualifier.cs processes formula text and injects the appropriate prefixes:

• _xlfn. for standard modern functions
• _xlfn._xlws. for worksheet-specific functions like FILTER
• _xlpm. for LET and LAMBDA parameters

The companion Unqualify method strips these prefixes when reading formulas back for user display, ensuring the CLI presents clean, readable formulas while maintaining OOXML compatibility under the hood.

Core Evaluation and Spill Logic

The FormulaEvaluator resides in a family of partial classes under src/officecli/Core/Formula/. This architecture handles both scalar and array computations:

• Parsing – Tokenizes formulas including modern function names
• Evaluation – Computes results, producing an ArrayResult when IsDynamicArrayFormula returns true
• Spill handling – Records that the result must be written with t="array" attributes and flags cells for dynamic array metadata

The file FormulaEvaluator.SpillLambda.cs specifically manages the spilling logic, while FormulaEvaluator.Functions.cs implements the modern function behaviors (e.g., SEQUENCE, SORT).

Writing Dynamic Array Metadata

Excel stores spill metadata in a CellMetadataPart (XLDAPR). OfficeCLI manages this through src/officecli/Handlers/Excel/ExcelHandler.DynamicArray.cs.

The EnsureDynamicArrayMetadata(Cell cell) method performs two critical operations:

1. Adds a CellMetadataPart to the workbook if none exists, using the static DynamicArrayMetadataXml record
2. Sets cell.CellMetaIndex = 1U on the anchor cell only

Only the anchor cell receives the cm="1" index. OfficeCLI does not write "ghost" cells (the spilled values); Excel computes the actual spill region on load, keeping the CLI implementation lightweight while maintaining full compatibility.

Formula Caching for Array Results

The ExcelHandler.FormulaCache in src/officecli/Handlers/Excel/ExcelHandler.FormulaCache.cs maintains per-sheet caches of evaluated formulas. For dynamic arrays:

• The evaluator's ArrayResult is stored in the cache
• The cache records the spill region (multi-cell range)
• Subsequent reads via GET or DUMP commands emit the appropriate values without re-evaluating the entire sheet

This optimization ensures efficient read operations after dynamic array formulas are initially computed.

Practical Implementation Examples

CLI Command Usage

Add a dynamic array formula that creates a 5-row sequence:

officecli add sheet1!A1 formula=SEQUENCE(5)

Behind the scenes, the CLI passes the raw string to ModernFunctionQualifier.Qualify, converting it to _xlfn.SEQUENCE(5) before evaluation and storage.

Detecting Dynamic Arrays in C#

using OfficeCli.Core;

string raw = "FILTER(A2:A10, B2:B10>0)";
bool isDyn = ModernFunctionQualifier.IsDynamicArrayFormula(raw);
// isDyn == true

Qualifying Formulas for OOXML

string raw = "SEQUENCE(5)";
string qualified = ModernFunctionQualifier.Qualify(raw);
// qualified == "_xlfn.SEQUENCE(5)"

Evaluating with the FormulaEvaluator

var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
var evaluator = new Core.FormulaEvaluator(sheetData, workbookPart);

// Evaluate a dynamic array formula
var result = evaluator.TryEvaluateFull("_xlfn.SEQUENCE(5)");

// result.IsArray == true
// result.ArrayValues contains [1,2,3,4,5]

Setting Metadata on Anchor Cells

Cell anchor = // ... the cell containing the formula
var excelHandler = new ExcelHandler(...);
excelHandler.EnsureDynamicArrayMetadata(anchor);
// anchor now has CellMetaIndex = 1U

Summary

• Detection relies on ModernFunctionQualifier.IsDynamicArrayFormula scanning against curated hash sets of function names
• Qualification adds _xlfn. and _xlfn._xlws. prefixes via ModernFunctionQualifier.Qualify to ensure OOXML compatibility
• Evaluation produces ArrayResult objects through the FormulaEvaluator partial class architecture
• Metadata writing uses ExcelHandler.DynamicArray.EnsureDynamicArrayMetadata to set cm="1" on anchor cells only
• Caching stores spill regions in ExcelHandler.FormulaCache for efficient subsequent reads
• Spill rendering is delegated to Excel via the XLDAPR metadata part, eliminating the need to track ghost cells

Frequently Asked Questions

What Excel functions trigger dynamic array detection in OfficeCLI?

OfficeCLI detects functions listed in the DynamicArrayFunctions hash set within ModernFunctionQualifier.cs. This includes SEQUENCE, SORT, SORTBY, FILTER, UNIQUE, MAP, LAMBDA, and LET. The IsDynamicArrayFormula method identifies these by name when parsing formula strings.

How does OfficeCLI handle the _xlfn prefix requirement for modern Excel functions?

The ModernFunctionQualifier.Qualify method automatically injects _xlfn. prefixes for standard modern functions and _xlfn._xlws. for worksheet-specific functions like FILTER. It also adds _xlpm. for LET and LAMBDA parameters. The Unqualify method reverses this process when reading formulas back for display.

What is the XLDAPR metadata record and why is it necessary?

XLDAPR (XML Dynamic Array Property Record) is the CellMetadataPart that Excel requires to recognize a cell as a dynamic array anchor. OfficeCLI creates a static metadata record in ExcelHandler.DynamicArray.cs and assigns cell.CellMetaIndex = 1U to link the anchor cell to this record. Without this metadata, Excel would treat the formula as a scalar value rather than a spilling array.

Does OfficeCLI write all cells in a spilled array or just the anchor?

OfficeCLI writes only the anchor cell containing the formula. The EnsureDynamicArrayMetadata method sets the metadata index exclusively on this cell. Excel calculates and displays the spilled values automatically when the file opens, which eliminates the need for OfficeCLI to track or write "ghost" cells in the spill region.

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