# How OfficeCLI Handles Dynamic Array Spilling in Excel: Metadata, Write-Back, and Rendering

> Discover how OfficeCLI manages dynamic array spilling in Excel. Learn about metadata, write-back, and rendering for efficient data handling and seamless HTML previews.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: deep-dive
- Published: 2026-07-28

---

**OfficeCLI handles dynamic array spilling in Excel by persisting only the anchor cell with spill metadata, allowing Excel 365 to recompute the full spill range on file open, while independently emulating the visual overflow behavior in HTML previews through calculated CSS widths.**

OfficeCLI is an open-source command-line interface for programmatically manipulating Microsoft Office documents. When processing Excel workbooks that utilize modern dynamic array formulas such as `SORT`, `FILTER`, or `UNIQUE`, the library implements a sophisticated spill-handling strategy that balances file size efficiency with Excel compatibility.

## Spill Metadata and the Anchor Cell Strategy

OfficeCLI implements a **single-cell write strategy** for dynamic array formulas. Rather than populating every cell in the spill range (the "ghost" cells), the library writes only the **anchor cell**—the single cell containing the formula.

In [`src/officecli/Handlers/Excel/ExcelHandler.DynamicArray.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.DynamicArray.cs) (lines 10-22), the code records spill metadata by setting the cell's type attribute to `t="array"` within the `<c>` element. This XML annotation signals to Excel that the formula should spill across multiple cells when the workbook opens. The actual spilled values are not persisted to disk; instead, OfficeCLI relies on Excel's recalculation engine to regenerate the spill region from the anchor formula.

This approach aligns with Excel 365's behavior where modern functions like `SEQUENCE`, `MAP`, `UNIQUE`, `SORT`, and `FILTER` return multi-value ranges. By writing metadata rather than data, OfficeCLI maintains smaller file sizes while preserving full formula functionality.

## Write-Back Logic and Spill Detection

When inserting formulas programmatically, OfficeCLI must distinguish between standard formulas and dynamic array formulas that produce spill regions.

In [`src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Add.Cells.cs) (lines 387-409), the detection logic treats the target range as a **spill region** during the write operation. The implementation identifies when a formula will spill and ensures that only the anchor reference persists in the file structure. This prevents data duplication and maintains the integrity of the dynamic array calculation chain.

The spill detection integrates with the formula evaluation system defined in [`ModernFunctionQualifier.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ModernFunctionQualifier.cs) and `FormulaEvaluator.*` to recognize which modern functions produce array outputs, ensuring proper handling regardless of the specific Excel function used.

## Formula Cache Integration

To support downstream calculations that reference spilled ranges, OfficeCLI maintains a **formula cache** that tracks spill ownership.

In [`src/officecli/Handlers/Excel/ExcelHandler.FormulaCache.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.FormulaCache.cs) (lines 169-173), cells participating in a spill are marked with metadata indicating that the anchor cell owns the multi-cell region. This allows subsequent formulas referencing the spill range to resolve correctly, understanding that the data originates from the dynamic array formula rather than individual static values.

## Visual Spill Emulation in HTML Previews

Beyond file persistence, OfficeCLI provides HTML rendering capabilities that visually replicate Excel's spill behavior without requiring Excel to perform the calculation.

### CSS-Based Overflow Implementation

The [`src/officecli/Handlers/Excel/ExcelHandler.HtmlPreview.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.HtmlPreview.cs) file (lines 878-904) implements a **spill detection algorithm** for HTML output. The renderer examines each cell to determine if it contains left-aligned text with empty right-hand neighbors. When these conditions are met, indicating potential spill behavior, the code adds a `class="spill"` attribute to the table cell and wraps the content in a `<span class="spill-text">` element.

This HTML structure produces the visual overflow effect seen in Excel, where text appears to flow into adjacent empty cells without actually widening the column or merging cells.

### Width Calculation Heuristics

To determine how far the content should visually spill, OfficeCLI employs the `GetSpillWidthPt` helper function defined in [`src/officecli/Handlers/Excel/ExcelHandler.HtmlPreview.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.HtmlPreview.cs) (lines 945-960). This function estimates the required horizontal space using **glyph-advance approximations**, calculating the maximum width in points that the text would occupy if displayed in a single line.

The calculated width is then applied as an inline style (`max-width`) on the spill-text span, ensuring the visual representation matches Excel's actual spacing behavior.

### Spill Restrictions and Edge Cases

OfficeCLI's HTML rendering respects Excel's spill constraints. According to the implementation in [`src/officecli/Handlers/Excel/ExcelHandler.HtmlPreview.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.HtmlPreview.cs) (lines 1010-1015), the following cell types **never spill**:
- Numbers
- Booleans
- Error values
- Cells with text wrapping enabled

Only cells with **left or general alignment** containing text can overflow into adjacent empty cells, accurately mirroring Excel's native spill rules.

## Practical Code Examples

To add a dynamic array formula using the OfficeCLI command-line interface:

```bash

# Write a SORT formula to cell A1; OfficeCLI handles spill metadata automatically

officecli set /Sheet1/A1 "SORT(B2:B10)" --xlsx mybook.xlsx

```

The internal C# implementation comments clarify the write-back strategy:

```csharp
// From src/officecli/Handlers/Excel/ExcelHandler.DynamicArray.cs (lines 10-22)
/// Dynamic-array (spill) write-back. A post-2016 dynamic-array formula
/// (SEQUENCE/FILTER/SORT/UNIQUE/MAP/…) only spills in Excel 365 when its anchor
/// does NOT spill. We write the anchor only — NOT the spilled "ghost" cells.

```

When rendering to HTML, the spill visualization is constructed with calculated widths:

```csharp
// Excerpt from src/officecli/Handlers/Excel/ExcelHandler.HtmlPreview.cs (lines 878-904)
if (spillWidth > 0)
    spillClass = " class=\"spill\"";
content = $"<span class=\"spill-text\" style=\"max-width:{spillWidth:0.##}pt{spanDecor}\">{content}</span>";
rowSb.Append($"<td ...{spillClass}{style}>{diagSvg}{content}</td>");

```

## Summary

- **Anchor-Only Persistence**: OfficeCLI writes only the anchor cell with `t="array"` metadata to [`ExcelHandler.DynamicArray.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.DynamicArray.cs), letting Excel recalculate spill regions on load.
- **Spill Region Detection**: The [`ExcelHandler.Add.Cells.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Add.Cells.cs) implementation identifies dynamic array formulas and prevents ghost cell duplication.
- **Formula Cache Tracking**: [`ExcelHandler.FormulaCache.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.FormulaCache.cs) marks spill ownership to maintain calculation chain integrity.
- **Visual HTML Emulation**: [`ExcelHandler.HtmlPreview.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.HtmlPreview.cs) replicates spill visuals using CSS classes and calculated max-widths via `GetSpillWidthPt`.
- **Constraint Compliance**: Only left-aligned text cells spill in HTML previews; numbers, booleans, errors, and wrapped text remain contained.

## Frequently Asked Questions

### How does OfficeCLI store dynamic array formulas without writing all spilled cells?

OfficeCLI writes only the anchor cell containing the formula to the Excel XML structure, adding a `t="array"` type attribute that marks it as a dynamic array. When Excel opens the file, it reads this metadata and recalculates the full spill range automatically, eliminating the need to persist "ghost" cells that would increase file size.

### What functions trigger spill behavior detection in OfficeCLI?

Modern Excel functions including `SORT`, `FILTER`, `UNIQUE`, `SEQUENCE`, and `MAP` trigger spill detection. The [`ExcelHandler.Add.Cells.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Add.Cells.cs) logic recognizes these functions during the write-back process and applies the single-cell anchor strategy rather than attempting to write the full array output.

### Why do numbers and booleans not spill in OfficeCLI's HTML preview?

According to the edge case handling in [`ExcelHandler.HtmlPreview.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.HtmlPreview.cs) (lines 1010-1015), only left-aligned or general-aligned text cells are eligible for visual spilling. Numbers, booleans, error values, and cells with text wrapping enabled are explicitly excluded from spill behavior to match Excel's native rendering rules.

### How does the spill width calculation work for HTML rendering?

The `GetSpillWidthPt` helper function in [`ExcelHandler.HtmlPreview.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.HtmlPreview.cs) (lines 945-960) uses glyph-advance approximations to estimate the horizontal space required for text content. This calculated width is applied as a `max-width` CSS property on the `spill-text` span, allowing the HTML to visually overflow into adjacent empty cells while maintaining proper text flow boundaries.