How OfficeCLI Creates Pivot Tables with Multi-Field Grouping and Aggregations

OfficeCLI generates native OOXML pivot tables directly in the workbook through PivotTableHelper.CreatePivotTable, supporting multiple row/column fields, diverse aggregation functions, and date grouping without requiring Excel.

OfficeCLI is an open-source command-line tool that manipulates Office documents programmatically. When you need to create pivot tables with complex multi-field groupings and custom aggregations, the tool builds the underlying XML structures natively—no Excel automation or round-trip required.

Core Architecture: PivotTableHelper.cs

The pivot table creation logic resides in src/officecli/Core/PivotTableHelper.cs. The CreatePivotTable method orchestrates the entire pipeline, from cache definition to final layout formatting.

When you execute officecli add --type pivottable, the handler in src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs parses your command-line properties and delegates to this helper. The helper then performs six distinct operations:

• Pivot cache creation – Generates or reuses a PivotCacheDefinition part containing raw source data and cache field metadata
• Field-area assignment – Maps each field to RowFields, ColumnFields, PageFields, or DataFields collections
• Aggregation wiring – Translates function names into DataFieldFunction attributes
• Date grouping – Adds Group elements for hierarchical date bucketing
• Layout formatting – Applies layout, grandTotals, subtotals, and related options
• Cache sharing – Enables copy-on-write for safe multi-table cache reuse

Multi-Field Grouping Implementation

Multiple fields in rows or columns are achieved by sequential addition to the respective collections. The order you specify fields in your command determines their hierarchy in the resulting pivot table.

Consider this command structure:

officecli add --type pivottable \
  --prop source=Sheet1!A1:D1000 \
  --prop rows=Region,Category \
  --prop cols=Year \
  --prop values=Sales \
  --prop agg=sum

Here Region becomes the primary row grouping, with Category nested beneath it. Year creates column groupings. The PivotField objects are added to RowFields and ColumnFields in the sequence provided, producing the hierarchical layout Excel renders.

Aggregation Function Mapping

For each value field, OfficeCLI maps your requested aggregation to the corresponding DataFieldFunction enumeration. Supported functions include:

• sum
• count
• average
• max
• min
• product
• stdDev
• var

When multiple value fields require different aggregations, provide them as comma-separated values matching your --prop values order:

officecli add --type pivottable \
  --prop source=Data!A1:E500 \
  --prop rows=Product \
  --prop cols=Month \
  --prop values=Revenue,Units \
  --prop agg=sum,count \
  --prop showDataAs=percentOfColumnTotal

The showDataAs parameter controls display transformations—percentOfColumnTotal, running totals, differences, and other analytical views—by setting the ShowDataAs element on the DataField.

Date Grouping and Hierarchical Buckets

Date fields support automatic grouping through the --prop group parameter. Valid options include year, quarter, month, and combinations thereof.

officecli add --type pivottable \
  --prop source=Sales!A1:F2000 \
  --prop rows=Date \
  --prop group=year,quarter \
  --prop values=Amount \
  --prop agg=sum

Internally, this creates a Group element within the field definition. Excel interprets this as hierarchical date buckets, collapsing individual dates into the specified granularity levels.

Layout and Formatting Controls

The PivotTableDefinition part receives additional configuration through these properties:

Property	Effect
layout=compact|outline|tabular	Controls field header presentation
grandTotals=on|off	Enables/disables grand total rows/columns
subtotals=on|off	Shows or hides intermediate subtotals
repeatItemLabels=true|false	Repeats labels for nested items
blankRows=insert	Adds spacing between grouped items

These map directly to OOXML elements within the pivot table definition, ensuring Excel renders the table exactly as specified.

Calculated Fields and Advanced Features

Beyond basic aggregation, OfficeCLI supports calculated fields through the calc property:

officecli add --type pivottable \
  --prop source=Sales!A1:F2000 \
  --prop rows=Date \
  --prop group=year,quarter \
  --prop values=Amount \
  --prop agg=sum \
  --prop calc=Profit=Amount-Cost

Calculated field expressions are parsed and injected into the PivotTableDefinition as CalculatedItem or CalculatedField elements, depending on context.

Cache Management and Performance

The pivot cache employs copy-on-write semantics, allowing multiple pivot tables to reference identical source data without duplication. This optimization matters for workbooks containing numerous analytical views of the same dataset.

The cache definition stores only field metadata and references to source data ranges—not the calculated pivot results themselves. Excel computes displayed values on file open, ensuring your pivot tables reflect current data without requiring OfficeCLI to perform aggregation calculations.

Modifying Existing Pivot Tables

After creation, pivot table properties can be updated through src/officecli/Handlers/Excel/ExcelHandler.Set.Tables.cs. This handler supports changing:

• Field assignments (moving fields between rows, columns, values, filters)
• Aggregation functions
• Grouping parameters
• Layout and formatting options

The update process rewrites the relevant PivotTableDefinition sections while preserving the existing pivot cache, minimizing file modification scope.

Summary

• Native OOXML generation: OfficeCLI builds pivot tables through direct XML manipulation in PivotTableHelper.CreatePivotTable, eliminating Excel dependency
• Multi-field grouping: Achieved by sequential PivotField insertion into RowFields/ColumnFields collections
• Flexible aggregation: Maps CLI function names to DataFieldFunction attributes; supports showDataAs transformations
• Date hierarchies: Created via Group elements with year, quarter, month bucketing
• Performance optimization: Copy-on-write cache sharing enables efficient multi-table workbooks

Frequently Asked Questions

What aggregation functions does OfficeCLI support for pivot tables?

OfficeCLI supports eight core aggregation functions: sum, count, average, max, min, product, stdDev, and var. These map directly to OOXML DataFieldFunction enumeration values. Each function is specified per value field through the --prop agg parameter, accepting comma-separated values when multiple fields require different aggregations.

Can I group date fields by multiple time periods simultaneously?

Yes. Provide comma-separated group values like --prop group=year,quarter or --prop group=year,month. OfficeCLI creates hierarchical Group elements that Excel renders as expandable date levels, allowing analysts to drill from years into quarters or months within the same pivot table structure.

How does OfficeCLI handle multiple pivot tables referencing the same source data?

OfficeCLI implements copy-on-write semantics for the PivotCacheDefinition part. Multiple pivot tables can safely share a single cache without interference—each table maintains its own PivotTableDefinition while referencing common source data. This reduces file size and ensures consistency across analytical views.

Is Excel required to view pivot tables created by OfficeCLI?

No. OfficeCLI generates fully compliant OOXML that Excel, LibreOffice Calc, and other compatible applications can open and render. The pivot tables contain complete structural definitions; Excel computes display values dynamically on file open without requiring prior calculation by OfficeCLI.

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