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

> Learn to generate Excel reports efficiently using OfficeCLI a command-line tool for seamless Excel automation. Automate report creation from the command line.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: how-to-guide
- Published: 2026-07-30

---

**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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/CommandBuilder.Add.cs) and [`CommandBuilder.Set.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/CommandBuilder.Set.cs)) parses user commands while the **Resident Server** ([`ResidentServer.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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:

```bash

# 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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/CommandBuilder.Set.cs) file implements the property-based value assignment:

```bash

# 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:

```bash
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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/CommandBuilder.Batch.cs). This executes multiple operations atomically:

```bash
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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/CommandBuilder.Add.cs) and [`CommandBuilder.Set.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/CommandBuilder.Set.cs)) to build reports with data, formulas, and styles.
- **Batch mode** ([`CommandBuilder.Batch.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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.