How to Create Pivot Tables from Source Ranges Using OfficeCLI

OfficeCLI creates pivot tables from source ranges by parsing CLI commands in CommandBuilder.Add.cs, routing them through ExcelHandler.Add.Tables.cs, and generating Open XML pivot definitions via PivotTableHelper.CreatePivotTable.

Creating pivot tables programmatically from existing data ranges is a core Excel automation task. The OfficeCLI open-source project (iOfficeAI/OfficeCLI) implements this through a specialized handler pipeline that transforms CLI arguments into fully-formed Open XML pivot table definitions. This guide explains the exact code paths, method signatures, and commands you need to create and configure pivot tables from source ranges.

Architecture: How OfficeCLI Processes Pivot Table Commands

OfficeCLI routes pivot table creation through three coordinated layers. Understanding this flow helps debug failures and extend functionality.

Command Parsing Layer

The CommandBuilder.Add.cs file parses the add pivot subcommand and its arguments (--sheet, --source, --dest, --name). It validates required parameters before forwarding to the Excel handler.

Handler Dispatch Layer

ExcelHandler.Add.Tables.cs (line ≈1781) receives the parsed arguments, performs worksheet existence checks, validates the source range format, and invokes PivotTableHelper.CreatePivotTable.

Core Implementation Layer

PivotTableHelper.cs (split across partial files) contains the actual XML generation logic. The public entry point is:

internal static int CreatePivotTable(
    WorksheetPart worksheetPart,
    string sourceRange,
    string pivotTableName,
    CellReference? topLeftCell = null,
    PivotTableOptions? options = null)

This method performs four critical operations:

1. Cache generation – Calls PivotTableHelper.EnsurePivotCacheSlicerExtension to create a PivotCacheDefinition part with a random pivotCacheId.
2. Definition building – Constructs the <pivotTableDefinition> XML block referencing both the cache and source range.
3. Part insertion – Creates a new PivotTablePart under the target worksheet.
4. Metadata updates – Adjusts <sheetData> size metadata to prevent "0 × 0" sheet reporting.

Creating Pivot Tables: CLI Commands and Code Path

Basic Pivot Table Creation

# Minimum required arguments

officecli add pivot --sheet Sheet1 --source A1:D20 --name SalesSummary

# With explicit destination cell

officecli add pivot --sheet Sheet1 --source A1:D20 --dest G1 --name SalesSummary

What happens internally:

• CommandBuilder.Add.cs extracts --source A1:D20 as the source range parameter.
• ExcelHandler.Add.Tables.cs validates that Sheet1 exists and A1:D20 forms a rectangular, non-empty range.
• PivotTableHelper.CreatePivotTable (line ≈904) generates the cache and definition XML.

Source Range Requirements

The helper enforces these constraints on the --source argument:

• Must be a valid A1-style range (e.g., A1:D20, not R1C1 format).
• Must reside entirely within one worksheet (cross-sheet ranges are rejected).
• Must contain at least one header row and one data row.

Configuring Pivot Tables After Creation

Use the set pivot command family to modify pivot structure. These commands route through ExcelHandler.Set.Tables.cs (line ≈1133).

Set Row Fields

# Use column 2 of source range as row field

officecli set pivot --sheet Sheet1 --pivot 1 --rowField 2

The --pivot 1 argument resolves to pivottable[1] via indexer lookup in the worksheet's PivotTableParts collection.

Add Filters

# Filter column 3 to only "2023" values

officecli set pivot --sheet Sheet1 --pivot 1 --filter 3=2023

Multiple filters can be chained:

officecli set pivot --sheet Sheet1 --pivot 1 --filter 3=2023 --filter 4=Completed

Internal Property Flow

ExcelHandler.Set.Tables.cs delegates to:

internal static void SetPivotTableProperties(
    PivotTablePart pivotTablePart,
    PivotTablePropertyMap properties)

This method updates the existing <pivotTableDefinition> XML without regenerating the cache.

Inspecting Pivot Table State

Verify creation success with the view command:

officecli view sheet --sheet Sheet1

Output format:

└─ "Sheet1" (100 rows × 30 cols, 1 pivot table(s))

The pivotInfo string is constructed in ExcelHandler.View.cs (lines 219–225) by counting PivotTableParts in the worksheet's part collection:

var pivotCount = worksheetPart.PivotTableParts.Count();
description += $", {pivotCount} pivot table(s)";

Key Source Files Reference

File	Purpose	Critical Members
src/officecli/Core/PivotTableHelper.cs	XML generation engine	CreatePivotTable(), cache wiring
src/officecli/Core/PivotTableHelper.Definition.cs	Helper structures and defaults	PivotTableOptions, default field layouts
src/officecli/Core/PivotTableHelper.Cache.cs	Cache part creation	EnsurePivotCacheSlicerExtension()
src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs	CLI-to-helper bridge	Line ≈1781 pivot handler
src/officecli/Handlers/Excel/ExcelHandler.Set.Tables.cs	Post-creation modification	Line ≈1133 property setter
src/officecli/Handlers/Excel/ExcelHandler.View.cs	State inspection	Lines 219–225 pivotInfo builder

Complete Workflow Example

# 1. Create pivot from sales data

officecli add pivot \
    --sheet "Sales Data" \
    --source A1:F100 \
    --dest H1 \
    --name "Q3 Summary"

# 2. Configure structure

officecli set pivot \
    --sheet "Sales Data" \
    --pivot 1 \
    --rowField 2 \
    --columnField 4 \
    --dataField 6

# 3. Add filter for specific region

officecli set pivot \
    --sheet "Sales Data" \
    --pivot 1 \
    --filter 3=North

# 4. Verify result

officecli view sheet --sheet "Sales Data"

Summary

• Entry point: CommandBuilder.Add.cs parses add pivot commands with --source for range specification.
• Validation: ExcelHandler.Add.Tables.cs confirms worksheet existence and range validity before calling the helper.
• Core creation: PivotTableHelper.CreatePivotTable (≈904) generates cache and definition XML as separate Open XML parts.
• Post-creation: ExcelHandler.Set.Tables.cs (≈1133) handles set pivot modifications via SetPivotTableProperties.
• Inspection: ExcelHandler.View.cs (219–225) exposes pivot count in sheet descriptions.

Frequently Asked Questions

How does OfficeCLI validate the source range before creating a pivot table?

ExcelHandler.Add.Tables.cs performs three checks: the worksheet must exist in the workbook, the range string must match A1 notation regex, and the range must not exceed the actual populated cells in the sheet. Invalid ranges trigger explicit error messages before PivotTableHelper.CreatePivotTable is invoked.

Can I create multiple pivot tables from the same source range?

Yes. Each add pivot command generates an independent PivotCacheDefinition part. Multiple pivots referencing A1:D20 will each have separate caches unless you manually edit the Open XML. The pivotInfo count in ExcelHandler.View.cs reflects all attached pivot tables.

What happens if I omit the --dest argument?

PivotTableHelper.CreatePivotTable uses a default placement algorithm: it finds the first empty cell to the right of the source range with a two-column buffer. The resulting CellReference is written into the <location> element of the pivot table definition.

How do I reference a specific pivot table in set commands?

Use the --pivot N syntax where N is the 1-based index in creation order. ExcelHandler.Set.Tables.cs resolves this via LINQ: worksheetPart.PivotTableParts.ElementAt(n-1). The index is validated against the actual part count before property modification proceeds.

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