# How to Create Pivot Tables in Excel Documents Using the OfficeCLI Add Command > Learn to create Excel pivot tables from the command line using the OfficeCLI add command. Generate OpenXML pivot tables quickly and efficiently. Enhance your data analysis. - Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI) - Tags: how-to-guide - Published: 2026-07-29 --- **Use `officecli add --prop source= --prop rows= --prop values=` 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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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:** ```bash 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:** ```bash 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):** ```bash 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"):** ```bash 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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Set.cs). **Update layout to tabular with repeated labels:** ```bash 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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/CommandBuilder.Add.cs) to [`ExcelHandler.Add.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Add.cs), which invokes `PivotTableHelper.CreatePivotTable`. - Property normalization at lines 334–345 of [`PivotTableHelper.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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.