How to Use OfficeCLI to Generate an Excel Report: Command-Line Automation Guide

OfficeCLI is a self-contained binary that creates, modifies, and formats Excel files entirely from the command line using a layered architecture that separates command parsing from in-memory document manipulation.

The iOfficeAI/OfficeCLI repository provides a powerful .NET-based tool for generating Excel reports without requiring Microsoft Excel installation. By leveraging resident mode for in-memory operations and deterministic JSON output for automation pipelines, you can use OfficeCLI to generate an Excel report programmatically from shell scripts, CI/CD workflows, or AI agents.

Understanding the OfficeCLI Architecture

OfficeCLI implements a three-layer architecture that handles Excel documents differently depending on your abstraction needs. The Command Builder (implemented in CommandBuilder.Add.cs and CommandBuilder.Set.cs) parses user commands while the Resident Server (ResidentServer.cs) maintains the workbook in memory, enabling near-zero-latency batch operations.

The architectural layers include:

• L1 (Read): High-level content views using view ... text or view ... html
• L2 (DOM): Structured element operations via add, set, remove, query, and batch commands
• L3 (Raw): Direct XML manipulation through raw and raw-set commands when lower-level access is required

When you open a file in Resident Mode using officecli open, the workbook stays in memory until you explicitly execute officecli close, eliminating repeated disk I/O during complex report generation.

Creating Your First Excel Report

To generate an Excel report with OfficeCLI, you combine workbook creation, sheet management, data population, and formatting commands.

Initialize the Workbook and Worksheet

Start by creating the file and adding a named worksheet:

# Create a new Excel file

officecli create report.xlsx

# Add a worksheet named "Q4"

officecli add report.xlsx / --type sheet --prop name="Q4"

The add command implementation in CommandBuilder.Add.cs handles the creation of sheets, cells, tables, charts, and other Excel elements.

Populate Data and Formulas

Use the set command to write values, headers, and formulas. The CommandBuilder.Set.cs file implements the property-based value assignment:

# Add header row

officecli set report.xlsx '/Q4' --prop row=1 --prop values='["Region","Sales","Growth%"]'

# Insert data rows starting at row 2

officecli set report.xlsx '/Q4' --prop startRow=2 --prop values='[
  ["EMEA",5000,0.12],
  ["APAC",7200,0.18],
  ["Americas",6100,0.15],
  ["LATAM",3400,0.07]
]'

# Add a total row with Excel formula

officecli set report.xlsx '/Q4' --prop row=6 --prop values='["Total", "=SUM(B2:B5)", ""]'

OfficeCLI supports 350+ built-in Excel functions and preserves formula references when generating the final .xlsx file.

Add Charts and Visualizations

Generate visual reports by adding charts without external dependencies:

officecli add report.xlsx '/Q4' --type chart \
  --prop chartType=column \
  --prop title="Q4 Sales by Region" \
  --prop dataRange="A2:B5"

The tool supports bar charts, line graphs, box-whisker plots, Pareto charts, and sparklines, all configurable through the add --type chart command structure documented in the excel-chart wiki.

Batch Operations for Automated Pipelines

For CI/CD environments or AI agent integration, OfficeCLI accepts JSON batch files through CommandBuilder.Batch.cs. This executes multiple operations atomically:

cat > batch.json <<'EOF'
[
  {"command":"create","path":"report.xlsx"},
  {"command":"add","path":"/","type":"sheet","props":{"name":"Q4"}},
  {"command":"set","path":"/Q4","props":{"row":1,"values":["Region","Sales","Growth%"]}},
  {"command":"set","path":"/Q4","props":{"startRow":2,"values":[["EMEA",5000,0.12],["APAC",7200,0.18],["Americas",6100,0.15],["LATAM",3400,0.07]]}},
  {"command":"set","path":"/Q4","props":{"row":6,"values":["Total","=SUM(B2:B5)",""]}},
  {"command":"add","path":"/Q4","type":"chart","props":{"chartType":"column","title":"Q4 Sales by Region","dataRange":"A2:B5"}}
]
EOF

officecli batch report.xlsx --input batch.json --json

The --json flag ensures deterministic output that downstream automation can parse reliably, avoiding fragile text parsing.

Advanced Excel Features

Beyond basic data and charts, OfficeCLI exposes the full Excel object model through L2 DOM commands:

• PivotTables: Generate multi-field pivot tables using add --type pivottable (see excel-pivottable wiki)
• Tables: Create structured Excel tables with add --type table for auto-filtering and formatting
• Conditional Formatting: Apply rules via set commands with color scales and data bars (excel-conditionalformatting wiki)
• Data Validation: Add dropdown lists and input restrictions
• Slicers: Insert interactive filters for tables and pivot tables

When L2 commands are insufficient, use L3 Raw commands (raw, raw-set) to manipulate the underlying Open XML directly.

Summary

• OfficeCLI provides a complete command-line solution to generate Excel reports without Excel installation, using the .NET-based architecture in the iOfficeAI/OfficeCLI repository.
• The Resident Server (ResidentServer.cs) keeps workbooks in memory during editing sessions, minimizing disk I/O for batch operations.
• Three architecture layers (L1 Read, L2 DOM, L3 Raw) provide flexibility from high-level views to direct XML manipulation.
• Use create, add, and set commands (implemented in CommandBuilder.Add.cs and CommandBuilder.Set.cs) to build reports with data, formulas, and styles.
• Batch mode (CommandBuilder.Batch.cs) with JSON input enables reliable automation in CI/CD pipelines.
• Every command supports --json output for programmatic result parsing.

Frequently Asked Questions

How does OfficeCLI handle Excel formulas when generating reports?

OfficeCLI preserves formula strings exactly as written and evaluates them within the generated .xlsx file. When you use officecli set with values like "=SUM(B2:B5)", the tool stores the formula in the cell's underlying XML while maintaining the calculation chain. This works for all 350+ built-in Excel functions, including complex financial and statistical operations.

Can I use OfficeCLI in a CI/CD pipeline without installing Microsoft Excel?

Yes. OfficeCLI is a self-contained binary that operates directly on the Open XML format. As demonstrated in the batch command example using CommandBuilder.Batch.cs, you can generate Excel reports entirely on headless Linux servers or Docker containers without any Microsoft Office dependencies. The deterministic JSON output (--json) ensures build scripts can verify success programmatically.

What is the difference between Resident Mode and standard file operations?

Resident Mode keeps the workbook loaded in memory via ResidentServer.cs while you issue multiple commands. When you execute officecli open report.xlsx, the file stays in RAM until officecli close is called. This architecture eliminates the serialization overhead of repeated disk reads/writes, making it ideal for complex reports requiring dozens of add and set operations that would execute instantly compared to traditional file-per-command approaches.

How do I verify the Excel report was generated correctly from the command line?

Use the L1 Read layer commands to inspect content without opening Excel. Execute officecli view report.xlsx text --max-lines 20 to see cell values as text, or officecli view report.xlsx html for formatted output. For programmatic verification, use officecli get report.xlsx '/Q4/chart[1]' --json to retrieve specific elements like chart definitions as structured JSON that automated tests can assert against.

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