# How OfficeCLI Handles Dynamic Arrays and Spilling in the Formula Engine

> Discover how OfficeCLI's formula engine processes dynamic arrays and spilling formulas by writing to anchor cells and letting Excel handle spill region calculation.

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

---

**OfficeCLI's formula engine handles dynamic arrays by writing only the anchor cell with spill metadata, delegating the spill region calculation to Excel while supporting HTML preview emulation.**

OfficeCLI provides comprehensive support for Excel's modern dynamic-array functionality. The engine treats the formula cell as an anchor, persists minimal metadata, and allows Excel to materialize the spilled range upon file open. This approach ensures compatibility with Excel 365 while keeping the CLI implementation lightweight.

## Anchor-Only Write-Back Strategy

When a formula returns a dynamic array, OfficeCLI writes **only the anchor cell** to the file, leaving the adjacent "ghost" cells untouched. In [`src/officecli/Handlers/Excel/ExcelHandler.DynamicArray.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.DynamicArray.cs), the implementation adds the required spill metadata—specifically `t="array"` and a `ref` attribute—so that Excel recomputes the extent of the spill when the workbook is opened. This mirrors Excel 365 behavior where the spreadsheet itself determines the spill dimensions, keeping the client out of the spill-region lifecycle.

## Function Qualification and Spill Detection

The engine maintains a curated list of modern functions that produce spilling results. In [`src/officecli/Core/Formula/ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/ModernFunctionQualifier.cs), functions like `FILTER`, `SORT`, `UNIQUE`, `SEQUENCE`, `MAP`, `BYROW`, `BYCOL`, `SCAN`, and regression functions such as `LINEST` and `LOGEST` are enumerated. Any function appearing in this list automatically receives the spill handling path during evaluation, ensuring the anchor-only write-back strategy is applied consistently.

## Lambda-Driven Spill Evaluation

During formula evaluation, the engine detects spill-capable functions and routes calculations through dedicated spill lambda handlers. In [`src/officecli/Core/Formula/FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/Formula/FormulaEvaluator.SpillLambda.cs), the evaluation logic for the `MAP`, `BYROW`, `BYCOL`, and `SCAN` families returns a single value for the anchor cell while the surrounding spill metadata instructs Excel to broadcast the array to adjacent cells. This separation of concerns allows the engine to compute the result once while letting Excel manage the visual spill.

## HTML Preview of Spilled Content

When rendering Excel sheets as HTML, OfficeCLI visually emulates text spilling into empty neighbor cells. The [`ExcelHandler.HtmlPreview.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.HtmlPreview.cs) implementation adds a `spill` class to the table cell and wraps overflowing text in a `spill-text` span with a calculated `max-width` heuristic. This provides users with a faithful visual representation of how the dynamic array will appear in Excel without materializing the ghost cells in the underlying data structure.

## Metadata Storage and Array References

Spill information is stored in the `CellFormula` element using the `arrayref` attribute, which is exposed in the JSON schema at [`schemas/help/xlsx/cell.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/schemas/help/xlsx/cell.json). This attribute holds the A1-style range reference (such as `B3#`) that Excel uses to recreate the spilled region. When reading files back, OfficeCLI exposes this property via the `--prop arrayref` flag, allowing scripts to query the intended spill range without calculating dimensions themselves.

## Practical Examples

### Writing a Dynamic-Array Formula

Use the `arrayformula` property to write a spill-capable formula that anchors at the specified cell:

```bash
officecli set file.xlsx /Data/B3 --prop arrayformula="FILTER(A1:A20, A1:A20>5)"

```

This command writes only cell B3 with the appropriate metadata, allowing Excel to populate the filtered results across the necessary range when the file opens.

### Reading the Spill Reference

Retrieve the spill range identifier that Excel will use to materialize the array:

```bash
officecli get file.xlsx /Data/B3 --prop arrayref

# Output: B3#

```

The returned value uses Excel's spill notation (`B3#`), indicating the dynamic range originating from that anchor.

### Generating HTML with Spill Visualization

Create an HTML preview that visually represents the spilled content:

```bash
officecli view file.xlsx --format html > preview.html

```

The generated HTML includes `spill` class annotations and `spill-text` spans that constrain width based on heuristics, emulating Excel's overflow behavior for dynamic arrays.

## Summary

- **Anchor-Only Writes**: OfficeCLI writes only the anchor cell, adding `t="array"` and `ref` metadata for Excel to handle the spill region.
- **Automatic Detection**: Functions listed in [`ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs) trigger spill handling automatically.
- **Lambda Evaluation**: [`FormulaEvaluator.SpillLambda.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/FormulaEvaluator.SpillLambda.cs) manages spill calculations for modern functions like `MAP` and `SCAN`.
- **Visual Preview**: HTML output uses `spill` classes and `spill-text` spans to emulate spilled content visually.
- **Metadata Exposure**: The `arrayref` attribute provides the spill range reference without requiring OfficeCLI to calculate array dimensions.

## Frequently Asked Questions

### Does OfficeCLI write all cells in a spilled range?

No. OfficeCLI writes only the anchor cell containing the formula. The implementation in [`ExcelHandler.DynamicArray.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.DynamicArray.cs) adds metadata that tells Excel where the spill should occur, but the actual population of ghost cells is deferred to Excel's calculation engine when the file is opened.

### Which functions trigger dynamic array handling in OfficeCLI?

The engine recognizes spilling functions defined in [`ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs), including `FILTER`, `SORT`, `UNIQUE`, `SEQUENCE`, `MAP`, `BYROW`, `BYCOL`, `SCAN`, `LINEST`, and `LOGEST`. When any of these appear in a formula, the engine automatically applies the anchor-only write-back strategy and spill metadata generation.

### How does the HTML preview represent spilled content?

The HTML renderer in [`ExcelHandler.HtmlPreview.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.HtmlPreview.cs) applies a `spill` CSS class to the anchor cell and wraps text content in a `spill-text` span with calculated `max-width` constraints. This heuristic-based approach visually emulates how text overflows into adjacent cells in Excel without creating actual data entries for the spilled region.

### What metadata identifies a cell as a dynamic array anchor?

OfficeCLI stores the spill reference in the `arrayref` attribute (defined in [`schemas/help/xlsx/cell.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/schemas/help/xlsx/cell.json)), which contains the A1-style range with spill notation (e.g., `B3#`). The underlying XML also includes `t="array"` and a `ref` attribute pointing to the spill range, ensuring Excel recognizes the cell as a dynamic array origin.