How OfficeCLI Handles Dynamic Arrays and Spilling in the Formula Engine

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, 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, 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, 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 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. 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:

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:

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:

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 trigger spill handling automatically.
• Lambda Evaluation: 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 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, 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 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), 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.

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