How OfficeCLI's Formula Evaluation Engine Handles Excel Dynamic Arrays

The OfficeCLI formula evaluation engine handles Excel dynamic arrays through a four-stage pipeline: detection via function whitelist, namespace qualification with _xlfn. prefixes, XLDAPR metadata injection, and ghost-cell-free write-back that lets Excel reconstruct spill regions on file open.

Excel's post-2016 dynamic-array formulas—functions like SEQUENCE, SORT, FILTER, and XLOOKUP—automatically spill results into adjacent cells. The OfficeCLI library provides first-class support for these spill formulas while maintaining clean, compatible OOXML. This article explains how the formula evaluation engine processes dynamic arrays based on the source code implementation in iOfficeAI/OfficeCLI.

Stage 1: Dynamic Array Detection

Before any processing occurs, the engine must identify if a formula uses spill-capable functions. The ModernFunctionQualifier.IsDynamicArrayFormula method in src/officecli/Core/Formula/ModernFunctionQualifier.cs performs this check.

The method scans the raw formula string (excluding the leading =) against a curated DynamicArrayFunctions set. It respects identifier boundaries, skips quoted literals, and stops at the first match.

// From ModernFunctionQualifier.cs (lines 94-104)
public static bool IsDynamicArrayFormula(string formula)
{
    // Scans for SEQUENCE, SORT, FILTER, UNIQUE, XLOOKUP, etc.
    // Returns true only when a dynamic-array function is detected
}

This whitelist approach ensures the engine correctly distinguishes standard formulas from spill formulas without expensive parsing overhead.

Stage 2: Namespace Qualification

Detected dynamic-array functions require namespace prefixes for Excel compatibility. The engine applies two qualification patterns:

• _xlfn. — Standard prefix for most dynamic-array functions
• _xlfn._xlws. — Extended prefix for worksheet-only functions like FILTER

The transformation happens in ModernFunctionQualifier.cs (lines 8-18):

// Example transformations
"SEQUENCE(5)"        → "_xlfn.SEQUENCE(5)"
"FILTER(A:A,B:B>0)"  → "_xlfn._xlws.FILTER(A:A,B:B>0)"

These prefixes are automatically stripped when users view formulas, maintaining transparent round-trips between human-readable and OOXML representations.

Stage 3: Spill Metadata Injection

Dynamic arrays require special workbook-level metadata. The ExcelHandler.EnsureDynamicArrayMetadata method in src/officecli/Handlers/Excel/ExcelHandler.DynamicArray.cs (lines 47-73) guarantees proper setup:

1. Creates a CellMetadataPart if absent
2. Adds a single <metadata> record of type XLDAPR
3. Links the anchor cell via cm="1" attribute

The metadata part is static and reusable—created once per workbook, then referenced by every spill formula cell.

// From ExcelHandler.DynamicArray.cs
public void EnsureDynamicArrayMetadata(Cell cell)
{
    // Ensures XLDAPR metadata exists
    // Sets cell.cm = 1 pointing to the metadata record
}

Stage 4: Evaluation and Write-Back

The final stage involves FormulaEvaluator and coordinated write-back logic. Key design decisions from ExcelHandler.DynamicArray.cs (lines 10-24):

• Anchor cells receive t="array" and ref attributes
• Ghost cells are deliberately omitted—Excel regenerates them on open
• This avoids stale data, overwrite conflicts, and manual spill-boundary tracking

// Complete workflow example
var sheet = workbook.GetSheet("Sheet1");
var cell = sheet.GetCell("A1");

string rawFormula = "SEQUENCE(5,1,10,2)";
bool isDyn = ModernFunctionQualifier.IsDynamicArrayFormula(rawFormula);

if (isDyn)
{
    rawFormula = ModernFunctionQualifier.QualifyNamespace(rawFormula);
    cell.CellFormula = new CellFormula 
    { 
        Text = rawFormula, 
        FormulaType = CellValues.Array 
    };
    excelHandler.EnsureDynamicArrayMetadata(cell);
}

Querying Stored Dynamic Array Formulas

To check if an existing cell contains a dynamic-array formula, reverse the qualification logic:

var cell = sheet.GetCell("B2");
string stored = cell.CellFormula?.Text ?? "";

// Strip namespace prefix before detection
string unqualified = stored.StartsWith("_xlfn.") 
    ? stored.Substring(stored.StartsWith("_xlfn._xlws.") ? 12 : 6) 
    : stored;

bool isDynamic = ModernFunctionQualifier.IsDynamicArrayFormula(unqualified);

Key Source Files and Responsibilities

File	Responsibility
src/officecli/Core/Formula/ModernFunctionQualifier.cs	Function detection and _xlfn namespace qualification
src/officecli/Handlers/Excel/ExcelHandler.DynamicArray.cs	XLDAPR metadata creation and anchor cell linking
src/officecli/Core/Formula/FormulaEvaluator.cs	Core formula execution with dynamic-array awareness
src/officecli/Handlers/Excel/ExcelHandler.FormulaCache.cs	Value caching during dynamic-array write-back

Summary

• Detection uses a curated function whitelist in ModernFunctionQualifier.IsDynamicArrayFormula
• Qualification adds _xlfn. or _xlfn._xlws. prefixes for Excel OOXML compatibility
• Metadata stores a single reusable XLDAPR record per workbook, referenced by cm="1"
• Ghost cells are never written—Excel reconstructs spill regions automatically, ensuring correct #SPILL! handling

Frequently Asked Questions

What Excel versions support the dynamic arrays generated by OfficeCLI?

OfficeCLI generates formulas compatible with Excel 365 and Excel 2021+, which fully support dynamic-array spill behavior. The _xlfn namespace prefixes ensure backward compatibility in file format—older Excel versions will display the formulas but Calculate them as #NAME? errors rather than spilling.

Why does OfficeCLI skip writing ghost cells instead of pre-calculating the spill range?

The engine deliberately omits ghost cells to avoid three problems: stale data when source ranges change, overwrite conflicts with existing cell content, and manual tracking of variable spill boundaries. Excel recomputes the full spill extent on file open using the anchor cell's formula and XLDAPR metadata, guaranteeing fresh results and native #SPILL! error handling for blocked ranges.

How does the XLDAPR metadata part work at the OOXML level?

The XLDAPR (XML List Dynamic Array Property Record) metadata type signals to Excel that a cell is a dynamic-array anchor. OfficeCLI creates one CellMetadataPart containing a single <metadata> element with type XLDAPR. Every dynamic-array anchor cell points to this record via cm="1". This static, reusable structure keeps file size minimal regardless of spill formula count.

Can OfficeCLI detect dynamic arrays in formulas written by other tools?

Yes. ModernFunctionQualifier.IsDynamicArrayFormula works on any formula string, including those from external sources. The method handles already-qualified formulas (stripping _xlfn. prefixes internally) and correctly identifies dynamic-array functions regardless of their origin, enabling seamless interoperability with workbooks created in Excel or other libraries.

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