How to Use Dynamic Array Formulas Like FILTER in OfficeCLI

OfficeCLI automatically generates the required Excel metadata for dynamic array formulas when you write to cells using the set command, enabling Excel 365 to calculate spill ranges without requiring manual population of derived cells.

OfficeCLI is an open-source Excel manipulation engine that evaluates over 350 built-in functions, including the modern dynamic array family. When you write a formula like FILTER to a workbook, the tool handles the complex Open XML metadata generation internally, ensuring the anchor cell is properly marked for Excel 365 to recognize and expand the spill region automatically.

How OfficeCLI Handles Dynamic Array Metadata

The core functionality resides in ExcelHandler.DynamicArray.cs. When you write a dynamic array formula, the EnsureDynamicArrayMetadata method creates a single CellMetadataPart containing the XLDAPR record and assigns cell.CellMetaIndex = 1U to the anchor cell.

This metadata assignment (exposed as cm="1" in the XML) signals to Excel 365 that the cell contains a dynamic array formula. During the write operation in ExcelHandler.Set.Cells.cs, the cell receives the attribute t="array" along with the formula string, while OfficeCLI manages the metadata part behind the scenes.

The function validation pipeline begins in ModernFunctionQualifier.cs, which enumerates supported dynamic array functions including "FILTER" (line 70). The actual implementation mapping occurs in FormulaEvaluator.Functions.cs, where the token "FILTER" routes to the EvalFilter method.

Writing FILTER Formulas via Command Line

To apply a dynamic array formula, use the set command with the --prop formula parameter. OfficeCLI parses the expression, registers the function, and attaches the necessary metadata automatically.

# Create a workbook

officecli create data.xlsx

# Add a sheet

officecli add data.xlsx / --type sheet --prop name="Sales"

# Populate source data

officecli set data.xlsx /Sales!B2 --prop value=120
officecli set data.xlsx /Sales!B3 --prop value=80
officecli set data.xlsx /Sales!C2 --prop value=7
officecli set data.xlsx /Sales!C3 --prop value=3

# Apply FILTER formula to show values where column C > 5

officecli set data.xlsx /Sales!A1 \
  --prop formula="=FILTER(B2:B3, C2:C3>5)"

Using the Python SDK

The Python SDK provides equivalent functionality through the send method, handling the same metadata generation transparently.

import officecli

with officecli.create("data.xlsx") as wb:
    wb.send({"command":"add","parent":"/","type":"sheet","props":{"name":"Sales"}})
    wb.send({"command":"set","path":"/Sales!B2","props":{"value":120}})
    wb.send({"command":"set","path":"/Sales!B3","props":{"value":80}})
    wb.send({"command":"set","path":"/Sales!C2","props":{"value":7}})
    wb.send({"command":"set","path":"/Sales!C3","props":{"value":3}})
    wb.send({"command":"set","path":"/Sales!A1","props":{"formula":"=FILTER(B2:B3, C2:C3>5)"}})
    
    # Verify the calculated value

    result = wb.send({"command":"get","path":"/Sales!A1","json":True})
    print(result)

Reading Spilled Values

Retrieve the calculated results using the get command. OfficeCLI reads the computed value from the anchor cell, while Excel handles the spill range calculation internally based on the embedded metadata.

# Get the anchor cell value

officecli get data.xlsx /Sales!A1 --json

# Get a range that may include spilled values

officecli get data.xlsx /Sales!A1:A10 --json

Metadata Reuse and Safety Considerations

The EnsureDynamicArrayMetadata method checks for existing CellMetadataPart components before creating new ones. If the workbook already contains cell metadata, OfficeCLI reuses the existing part while ensuring the anchor cell's cm attribute points to index 1.

Key points to remember:

• Only write the anchor cell. Never manually populate cells within the spill range, as Excel 365 calculates these automatically when opening the file.
• Spill errors surface naturally. If the spill range overlaps existing data, Excel returns a #SPILL! error, which OfficeCLI exposes through its structured JSON output.
• Safe for existing workbooks. The metadata management logic prevents duplication of CellMetadataPart components when modifying existing files.

Summary

• OfficeCLI in iOfficeAI/OfficeCLI supports dynamic array formulas like FILTER, SORT, UNIQUE, SEQUENCE, LET, LAMBDA, and MAP through automatic metadata generation.
• The EnsureDynamicArrayMetadata method in ExcelHandler.DynamicArray.cs creates the XLDAPR record and sets CellMetaIndex = 1U to mark anchor cells.
• Use CommandBuilder.Set.cs functionality via officecli set <file> /Sheet!Cell --prop formula="<expression>" to write dynamic array formulas.
• Function validation occurs in ModernFunctionQualifier.cs, while evaluation logic resides in FormulaEvaluator.Functions.cs.
• Excel 365 reads the generated metadata and calculates spill ranges automatically when opening the file, requiring no additional write operations from OfficeCLI.

Frequently Asked Questions

Do I need to manually write values to spilled cells?

No. When you write a dynamic array formula to the anchor cell using officecli set, OfficeCLI only writes the formula and the required metadata. Excel 365 calculates and displays the spilled values when the file opens. Writing to cells within the spill range would cause #SPILL! errors.

What happens if the spill range overlaps existing data?

Excel 365 detects the collision and displays a #SPILL! error in the anchor cell. OfficeCLI surfaces this error through its structured JSON output when you attempt to read the cell value, allowing you to identify and resolve the overlap.

Can I use other dynamic array functions besides FILTER?

Yes. OfficeCLI supports the full modern dynamic array family including SORT, UNIQUE, SEQUENCE, LET, LAMBDA, and MAP, as registered in ModernFunctionQualifier.cs and implemented in FormulaEvaluator.Functions.cs. All follow the same metadata generation pattern.

How does OfficeCLI handle existing workbooks with metadata?

The EnsureDynamicArrayMetadata method checks for existing CellMetadataPart components before creating new ones. It safely reuses existing parts while ensuring the anchor cell references index 1, preventing metadata duplication and maintaining file integrity.

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