# OfficeCLI Formula Evaluator Dynamic Arrays: Implementation Guide

> Learn how the OfficeCLI formula evaluator processes dynamic array formulas for seamless Excel integration. Discover its implementation for spill functions and OOXML compliance.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: implementation-guide
- Published: 2026-07-10

---

**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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs) specifically manages the spilling logic, while [`FormulaEvaluator.Functions.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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:

```bash
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#

```csharp
using OfficeCli.Core;

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

```

### Qualifying Formulas for OOXML

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

```

### Evaluating with the FormulaEvaluator

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

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