# How OfficeCLI's Formula Evaluation Engine Handles Excel Dynamic Arrays

> Discover how OfficeCLI's formula engine processes Excel dynamic arrays using a four-stage pipeline including detection, namespace qualification, metadata injection, and ghost-cell-free write-back.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: internals
- Published: 2026-08-07

---

**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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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.

```csharp
// 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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs) (lines 8-18):

```csharp
// 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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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.

```csharp
// 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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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

```csharp
// 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:

```csharp
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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/ModernFunctionQualifier.cs) | Function detection and `_xlfn` namespace qualification |
| [`src/officecli/Handlers/Excel/ExcelHandler.DynamicArray.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.DynamicArray.cs) | XLDAPR metadata creation and anchor cell linking |
| [`src/officecli/Core/Formula/FormulaEvaluator.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.cs) | Core formula execution with dynamic-array awareness |
| [`src/officecli/Handlers/Excel/ExcelHandler.FormulaCache.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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.