How to Create Excel Sheets with Formulas Using OfficeCLI

OfficeCLI treats any cell value starting with an equals sign (=) as an Excel formula, storing it directly in the Open XML CellFormula property via the ExcelHandler class.

The iOfficeAI/OfficeCLI repository provides a command-line interface for manipulating Office documents via selector-based syntax. When you create Excel sheets with formulas using OfficeCLI, the tool writes directly to the Open XML format without requiring the Excel application, enabling automated spreadsheet generation from shell scripts or CI/CD pipelines.

How OfficeCLI Handles Excel Formulas

The CLI architecture routes commands through a resident server that delegates to document-specific handlers. In src/officecli/ResidentServer.cs, the command parser identifies the target file type and routes Excel operations to ExcelHandler.

When processing a set command, src/officecli/Handlers/ExcelHandler.cs examines the payload. If the value string begins with =, the handler stores it as the cell’s Formula property (Open XML CellFormula) rather than a literal CellValue. This distinction preserves the formula logic for recalculation when the file opens in Excel or LibreOffice.

Changes are not written immediately. Instead, src/officecli/Handlers/ExcelBatchEmitter.cs accumulates mutations—including formulas—in memory. Calling flush triggers FlushDirtyParts, which commits all pending changes to disk in a single atomic update, guaranteeing workbook integrity.

Writing Formulas to Excel Cells

Basic Formula Syntax

OfficeCLI uses a selector path to identify cells: /filename.xlsx/SheetName!CellReference. To write a formula, prefix the value with =.

# Create a new workbook (or open existing) with write access

officecli create myReport.xlsx --editable

# Write a SUM formula to cell B1

officecli set /myReport.xlsx/Sheet1!B1 "=SUM(A2:A10)"

# Write a VLOOKUP referencing another sheet

officecli set /myReport.xlsx/Sheet1!C1 "=VLOOKUP(D1, Sheet2!A:B, 2, FALSE)"

Mixing Static Values and Formulas

You can populate cells with static data and reference them in formulas. The handler distinguishes between the two based on the = prefix.

# Static value in D1

officecli set /myReport.xlsx/Sheet1!D1 "42"

# Formula referencing D1 in E1

officecli set /myReport.xlsx/Sheet1!E1 "=D1*2"

Batch Operations and Range Syntax

OfficeCLI supports range selectors to apply identical formulas across multiple cells efficiently. The batch emitter optimizes these operations into a single write transaction.

# Apply the same formula to cells F2 through F100

officecli set /myReport.xlsx/Sheet1!F2:F100 "=A2*B2"

# Persist all pending changes atomically

officecli flush /myReport.xlsx

The flush command is essential; until it executes, modifications remain queued in ExcelBatchEmitter.

Core Implementation Files

Understanding the source structure helps when debugging or extending functionality:

• src/officecli/Handlers/ExcelHandler.cs — Main entry point for Excel documents; parses selectors, determines if payloads are formulas, and routes mutations.
• src/officecli/Handlers/ExcelBatchEmitter.cs — Accumulates cell changes (including formulas) and executes atomic writes via FlushDirtyParts.
• src/officecli/ResidentServer.cs — Central command router that instantiates the correct handler based on file extension.
• src/officecli/Handlers/Excel/ExcelHandler.View.cs — Contains read-only logic for querying existing cell values and formulas.
• src/officecli/Handlers/Excel/ExcelHandler.Helpers.cs — Provides utility methods for style, number formatting, and formula validation during mutation.

Summary

• Formula detection relies on the = prefix; values starting with this character are stored as Open XML CellFormula objects.
• Architecture delegates Excel operations to ExcelHandler, batches changes in ExcelBatchEmitter, and commits them via FlushDirtyParts.
• Range support allows applying formulas to multiple cells (e.g., F2:F100) in a single command.
• Persistence requires explicitly calling officecli flush to write queued mutations to the file system.

Frequently Asked Questions

How does OfficeCLI distinguish between a formula and a text value?

In src/officecli/Handlers/ExcelHandler.cs, the mutation logic inspects the payload string. If it begins with an equals sign (=), the handler assigns it to the cell’s Formula property; otherwise, it treats the payload as a static CellValue.

Can I apply formulas to multiple cells at once?

Yes. Use range syntax in your selector, such as Sheet1!A1:A100, to write the formula to an entire range. The ExcelBatchEmitter processes this as a single batch operation for improved performance.

What Excel functions are supported by OfficeCLI?

OfficeCLI supports any standard Excel function—including SUM, VLOOKUP, IF, and named ranges—because it stores the formula string literally in the Open XML format without interpreting the function logic.

How do I ensure my changes are saved to the file?

Execute officecli flush <filepath> to trigger FlushDirtyParts in ExcelBatchEmitter.cs. This writes all pending mutations, including formulas, to the workbook in one atomic operation.

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