How to Create Pivot Tables in Excel Documents Using the OfficeCLI Add Command

Use officecli add <file.xlsx> <sheet> --prop source=<range> --prop rows=<field> --prop values=<field> to generate OpenXML pivot tables directly from the command line via the PivotTableHelper.CreatePivotTable method.

The iOfficeAI/OfficeCLI tool enables command-line manipulation of Excel workbooks by treating them as collections of handlers that operate on underlying OpenXML parts. When you need to create pivot tables in Excel documents using the OfficeCLI add command, the request is routed by CommandBuilder.Add (see src/officecli/CommandBuilder.Add.cs at line 244) to the ExcelHandler, which delegates the actual construction to the PivotTableHelper class.

Architecture and Entry Points

When you invoke officecli add against an .xlsx file, the tool executes a handler-based pipeline. The entry point resides in src/officecli/CommandBuilder.Add.cs, where the Add method resolves the file format and dispatches to ExcelHandler.Add in src/officecli/Handlers/Excel/ExcelHandler.Add.cs. This handler instantiates the PivotTableHelper class and calls the static method CreatePivotTable, implemented at lines 90–115 of src/officecli/Core/PivotTableHelper.cs.

The Seven-Step Pivot Creation Pipeline

The CreatePivotTable method orchestrates a complete pipeline to transform raw worksheet data into a functioning pivot table. Understanding these steps helps diagnose configuration errors and optimize performance.

1. Property Normalization

User-supplied --prop key/value pairs first pass through NormalizePivotProperties (lines 334–345 in PivotTableHelper.cs). This method resolves aliases such as row, rowfield, and col into canonical keys (rows, cols, etc.), ensuring consistent internal processing regardless of the terminology used in the CLI.

2. Validation and Unknown Key Detection

Before processing, the tool validates inputs using ValidatePivotName (lines 73–92), which enforces that pivot table names must be non-empty, contain fewer than 255 characters, and exclude control characters. Simultaneously, CollectUnknownPivotKeys (lines 76–82) identifies unrecognized property keys and emits UNSUPPORTED warnings, preventing silent failures due to typos in property names.

3. Thread-Static Configuration Scopes

Axis sort modes, grand-total flags, subtotal settings, layout modes, repeat-label options, blank-row handling, and caption text are propagated via ThreadStatic fields. The method enters these scopes using using var _ = Push…(properties); statements, ensuring that configuration values remain isolated to the current pivot creation context without affecting global state.

4. Source Data Extraction

The ReadSourceData method extracts the source range from the specified worksheet, capturing row data, column headers, and cell styles. This raw data serves as the foundation for the pivot cache.

5. Optional Preprocessing

If specified in the properties, the pipeline applies date-grouping algorithms, label filtering, and top-N filtering to the extracted data before cache generation. These transformations occur in memory before any OpenXML parts are written.

6. OpenXML Part Generation

The method creates two critical OpenXML parts: a PivotCacheDefinitionPart to store the raw data cache, and a PivotTablePart to store the pivot definition (including field layouts, styles, and location). This separation allows Excel to refresh data without rebuilding the table structure.

7. Worksheet Insertion

Finally, the pivot table is placed at the cell coordinates supplied via the position property (for example, F1). The workbook updates to reference the new parts, and the newly created pivot table becomes the active sheet. The method returns the 1-based index of the pivot table within the workbook.

Command Syntax and Property Keys

All pivot configuration occurs through repeated --prop flags followed by key=value pairs. Keys are case-insensitive and support extensive aliasing as defined in the _pivotKeyAliases table within PivotTableHelper.cs.

Essential properties include:

• source: The data range (e.g., Sheet1!A1:D200)
• rows, cols: Fields to use as row or column labels
• values: Fields to aggregate
• name: Unique identifier for the pivot table
• position: Target cell for the upper-left corner of the pivot table

Practical CLI Examples

Below are runnable examples demonstrating common pivot table scenarios using the officecli add command.

Basic pivot table creation:

officecli add sales.xlsx Sheet1 \
  --prop source=Sheet1!A1:D200 \
  --prop rows=Region \
  --prop cols=Month \
  --prop values=Revenue \
  --prop name=RevenueByRegion

Adding grand totals and styling:

officecli add sales.xlsx Sheet1 \
  --prop source=Sheet1!A1:D200 \
  --prop rows=Region \
  --prop cols=Month \
  --prop values=Revenue \
  --prop grandtotalcaption="Total Revenue" \
  --prop style=PivotStyleMedium9

Applying a top-N filter (top 5 regions by revenue):

officecli add sales.xlsx Sheet1 \
  --prop source=Sheet1!A1:D200 \
  --prop rows=Region \
  --prop values=Revenue \
  --prop topn=5

Label filtering (regions starting with "A"):

officecli add sales.xlsx Sheet1 \
  --prop source=Sheet1!A1:D200 \
  --prop rows=Region \
  --prop values=Revenue \
  --prop labelFilter=Region:beginsWith:A

Modifying Existing Pivot Tables

After creation, you can update pivot table properties using the excel-set command, which reuses the same normalization and validation logic found in SetPivotTableProperties within src/officecli/Handlers/Excel/ExcelHandler.Set.cs.

Update layout to tabular with repeated labels:

officecli set sales.xlsx /Sheet1/pivottable[1] \
  --prop layout=tabular \
  --prop repeatlabels=true

Summary

• The officecli add command routes Excel operations through CommandBuilder.Add.cs to ExcelHandler.Add.cs, which invokes PivotTableHelper.CreatePivotTable.
• Property normalization at lines 334–345 of PivotTableHelper.cs converts aliases like row to canonical rows before processing.
• The creation pipeline validates names, warns on unknown keys, uses thread-static scopes for configuration, and generates both a PivotCacheDefinitionPart and PivotTablePart.
• All configuration occurs via --prop flags; the source, rows, cols, and values properties are required for basic functionality.
• Existing pivot tables can be modified using officecli set, leveraging the same validation logic as the add path.

Frequently Asked Questions

What is the maximum length for a pivot table name in OfficeCLI?

According to the ValidatePivotName method in src/officecli/Core/PivotTableHelper.cs (lines 73–92), pivot table names must be non-empty, contain fewer than 255 characters, and must not include control characters. Violating these constraints raises a validation error before any OpenXML parts are created.

Can I use aliases for property keys when creating pivot tables?

Yes. The NormalizePivotProperties method (lines 334–345 in PivotTableHelper.cs) automatically converts aliases such as row, rowfield, and col into their canonical forms (rows, cols). All property keys are case-insensitive, so Rows, ROWS, and rows are treated identically.

How does OfficeCLI handle unknown or misspelled property keys?

The CollectUnknownPivotKeys method (lines 76–82) identifies any keys that remain unrecognized after normalization. The tool emits an UNSUPPORTED warning listing these keys, allowing you to identify typos in --prop arguments without aborting the operation, though unrecognized properties are ignored during pivot creation.

What is the difference between officecli add and officecli set for pivot tables?

The add command creates new pivot tables via PivotTableHelper.CreatePivotTable, while set modifies existing ones through SetPivotTableProperties in ExcelHandler.Set.cs. Both commands share the same property normalization logic and validation rules, ensuring consistent syntax whether you are creating or updating pivot tables.

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