# How to Create Pivot Tables from Source Ranges Using OfficeCLI

> Learn to create pivot tables from source ranges using OfficeCLI. This guide explains how OfficeCLI parses commands and generates Open XML pivot definitions for efficient data analysis.

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

---

**OfficeCLI creates pivot tables from source ranges by parsing CLI commands in [`CommandBuilder.Add.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/CommandBuilder.Add.cs), routing them through [`ExcelHandler.Add.Tables.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/PivotTableHelper.cs)** (split across partial files) contains the actual XML generation logic. The public entry point is:

```csharp
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

```bash

# 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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/CommandBuilder.Add.cs) extracts `--source A1:D20` as the **source range** parameter.
- [`ExcelHandler.Add.Tables.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Tables.cs)** (line ≈1133).

### Set Row Fields

```bash

# 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

```bash

# Filter column 3 to only "2023" values

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

```

Multiple filters can be chained:

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

```

### Internal Property Flow

[`ExcelHandler.Set.Tables.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Tables.cs) delegates to:

```csharp
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:

```bash
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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.View.cs)** (lines 219–225) by counting `PivotTableParts` in the worksheet's part collection:

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

```

## Key Source Files Reference

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

## Complete Workflow Example

```bash

# 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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/CommandBuilder.Add.cs) parses `add pivot` commands with `--source` for range specification.
- **Validation**: [`ExcelHandler.Add.Tables.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Tables.cs) (≈1133) handles `set pivot` modifications via `SetPivotTableProperties`.
- **Inspection**: [`ExcelHandler.View.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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`](https://github.com/iOfficeAI/OfficeCLI/blob/main/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.