How to Create Pivot Tables from a Source Range Using OfficeCLI: Complete Feature Guide

OfficeCLI enables command-line creation of Excel pivot tables from source ranges using the add command with --type pivottable, supporting field configurations, aggregations, styling, and slicer integration through a dual-part architecture managed by the PivotTableHelper class.

OfficeCLI by iOfficeAI provides comprehensive command-line manipulation of Excel workbooks, allowing you to create pivot tables from a source range using OfficeCLI without opening the Excel application. The tool implements a sophisticated dual-part architecture consisting of a PivotTablePart (the rendered visualization) and a PivotTableCacheDefinitionPart (the cached source data), with all creation logic centralized in the PivotTableHelper class. According to the schema documented in schemas/help/xlsx/pivottable.json, the CLI exposes extensive configuration options through property bags while maintaining read-only structural integrity for generated pivot components.

Understanding the OfficeCLI Pivot Table Architecture

OfficeCLI treats pivot tables as first-class workbook elements with a distinct internal structure. When you execute an add command with --type pivottable, the CLI parses the supplied property bag and constructs a PivotTableDefinition XML document, as implemented in [src/officecli/Core/PivotTableHelper.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Core/PivotTableHelper.cs) around line 904. The architecture separates concerns between the PivotTablePart (worksheet-level rendering) and the PivotTableCacheDefinitionPart (workbook-level data cache), enabling efficient data management and update operations.

Creating a Pivot Table from a Source Range

Specifying the Source Data

The mandatory source property (alias src) defines the rectangular data range that feeds the pivot table, formatted as SheetName!A1:D100. The PivotTableHelper.ResolvePivotSourceSpec method (approximately line 1669 in PivotTableHelper.cs) validates this reference before creating or reusing a cache definition. Validating the source range ensures the pivot table maintains referential integrity with the underlying data.

Configuring Row, Column, and Data Fields

Field placement and aggregation are controlled through three key properties parsed by PivotTableHelper.BuildPivotTableDefinition (around line 1120):

• rows: Comma-separated field names for row labels (e.g., Region,Product)
• cols: Comma-separated field names for column labels (e.g., Year)
• values: Field names with aggregation functions using Field:agg syntax (e.g., Sales:sum,Quantity:avg)

You can also apply filters using the filters property, which adds fields to the filter axis without displaying them in the row or column structure.

Positioning the Pivot Table

By default, OfficeCLI places the pivot table immediately after the source range unless you specify an explicit anchor using the pos property (alias for position). The PivotTableHelper.CreatePivotTable method (approximately line 1675) handles this positioning logic, ensuring the pivot table does not overwrite existing data unless explicitly directed.

Supported Pivot Table Features and Configuration

Aggregation and Top-N Filtering

Beyond standard aggregations (sum, avg, count, etc.), OfficeCLI supports topN filtering to limit row keys to the top N values based on the first data field. Set --prop topN=5 to retain only the five highest-value rows, implemented in PivotTableHelper.CreatePivotTable around line 1089.

Layout and Visual Styling

Control the visual presentation through properties processed by BuildPivotTableDefinition (approximately line 1205):

• style: Layout options including compact and outline formats
• subtotals: Boolean to enable or disable subtotal rows (default true)
• grandtotals: Boolean to show or hide grand total rows and columns (default true)

Slicer Integration

After creating a pivot table, you can attach interactive slicers using the separate slicer handler. The ExcelHandler.Slicer.cs file (see line 42 in [src/officecli/Handlers/Excel/ExcelHandler.Slicer.cs](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Slicer.cs)) implements slicer binding by referencing the pivot table path (e.g., /Sales/pivottable[1]) and specifying the target field.

Practical Command-Line Examples

The following examples demonstrate complete pivot table creation workflows:

# Create a comprehensive pivot table with styling and Top-N filtering

officecli add sales.xlsx /Sales --type pivottable \
  --prop source=Data!A1:E200 \
  --prop rows=Region,Product \
  --prop cols=Year \
  --prop values=Sales:sum,Quantity:avg \
  --prop topN=10 \
  --prop style=compact,outline \
  --prop subtotals=false

# Verify pivot table structure and read-only child nodes

officecli get sales.xlsx /Sales/pivottable[1] --format json

# Attach a slicer to filter the pivot by Region

officecli add sales.xlsx /Sales --type slicer \
  --prop pivotTable=/Sales/pivottable[1] \
  --prop field=Region

# PowerShell equivalent with backtick continuation

officecli add sales.xlsx "/Sales" --type pivottable `
  --prop source=Data!A1:E200 `
  --prop rows=Region,Product `
  --prop cols=Year `
  --prop values=Sales:sum,Quantity:avg `
  --prop topN=10 `
  --prop style=compact,outline `
  --prop subtotals=false

Summary

• OfficeCLI creates pivot tables using a dual-part architecture (PivotTablePart and PivotTableCacheDefinitionPart) managed by PivotTableHelper
• The source property (mandatory) defines the input range via ResolvePivotSourceSpec (line 1669)
• Configure dimensions using rows, cols, and values (with aggregation syntax) via BuildPivotTableDefinition (line 1120)
• Apply topN filtering, style options, and subtotals controls for customized presentation
• Attach slicers to existing pivot tables using the handler in ExcelHandler.Slicer.cs (line 42)
• Child nodes (pivotfield, pivotrow, pivotcolumn, pivotdata) are read-only structural elements generated by the CLI

Frequently Asked Questions

What is the minimum required property to create a pivot table?

You must specify the source property pointing to a valid range (e.g., Sheet1!A1:D100). The PivotTableHelper.ResolvePivotSourceSpec method validates this reference before creating the PivotTableCacheDefinitionPart and PivotTablePart.

Can I modify an existing pivot table's field structure?

No. According to the schema in schemas/help/xlsx/pivottable.json, child nodes such as pivotfield, pivotrow, pivotcolumn, and pivotdata are read-only structural elements. You must delete and recreate the pivot table with new field configurations.

How does OfficeCLI handle the data cache?

OfficeCLI creates a PivotTableCacheDefinitionPart at the workbook level to cache the source data, while the PivotTablePart handles the worksheet-level rendering. The PivotTableHelper class manages both parts, ensuring the cache updates when source data changes.

Is slicer binding supported for all pivot tables?

Yes. Any pivot table created via OfficeCLI can accept slicer bindings using the add command with --type slicer, referencing the pivot table path (e.g., /Sales/pivottable[1]) and specifying the target field, as implemented in ExcelHandler.Slicer.cs around line 42.

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