How to Create Pivot Tables from Source Ranges with showDataAs and Date Grouping in OfficeCLI

OfficeCLI generates Excel pivot tables via a single officecli add command that references a source range and configures layout, aggregation, date grouping, and percentage calculations through the showDataAs property.

OfficeCLI is an open-source command-line tool for automating Microsoft Office documents. The project's xlsx skill schema defines how pivot tables are constructed from raw worksheet data, supporting advanced features like temporal grouping and relative value calculations.

Specifying Source Ranges and Pivot Layout

The pivot creation process begins with the source property, which accepts an absolute worksheet range (e.g., Sheet1!A1:J51). According to the schema defined in SKILL.md (lines 260-270), the CLI parses this range to extract column headers and establish the pivot cache.

Field placement is controlled through three key properties:

• rows – Fields placed on the row axis.
• cols – Fields placed on the column axis.
• values – Data fields with aggregation specifications.

Implementing showDataAs for Percentage Calculations

To display values as percentages rather than raw aggregates, OfficeCLI implements the showDataAs transformation through the values property syntax: field:aggregation:showDataAs.

When a third token is present (such as percent_of_row, percent_of_col, percent_of_total, or running_total), the CLI injects a <showDataAs> element into the pivot definition. This modifies how Excel renders the calculated values relative to row, column, or grand totals.

Enabling Date Grouping via Field Suffixes

Date grouping requires no separate configuration flag. Instead, append temporal suffixes directly to date field names in the rows or cols properties:

• :year for annual grouping.
• :quarter for quarterly rollup.
• :month for monthly aggregation.

When OfficeCLI detects these suffixes, it automatically generates <fieldGroup> elements in the underlying OOXML, enabling Excel's native date hierarchy without manual pivot table manipulation.

Practical CLI Implementation

The following command creates a pivot table that groups dates by year and quarter, displays sales as a percentage of each row, and filters by region:

officecli add pivot-demo.xlsx "/Sales by Date" --type pivottable \
  --prop source=Sheet1!A1:J51 \
  --prop 'rows=Date:year,Date:quarter' \
  --prop 'values=Sales:sum:percent_of_row' \
  --prop filters=Region \
  --prop layout=outline \
  --prop grandtotals=both \
  --prop style=PivotStyleMedium7

This example appears in the repository's documentation at lines 66-74 of pivot-tables.md.

Programmatic Batch Generation

For automated workflows, reference the Python implementation in pivot-tables.py. This script iterates over configuration dictionaries and executes officecli add via subprocess, demonstrating how to generate multiple pivot tables programmatically from the same source data.

Key Source Files

• SKILL.md – Defines the pivottable element schema, including showDataAs enumerations and date grouping specifications (lines 260-270).
• pivot-tables.md – Contains 19 practical examples, including date grouping demonstrations (lines 66-74).
• pivot-tables.py – Python driver showing programmatic CLI invocation.
• README.md – High-level capability overview linking to the full pivot table documentation.

Summary

• Source ranges are specified via the source property using absolute worksheet references (e.g., Sheet1!A1:J51).
• showDataAs calculations use the syntax field:agg:showDataAs (e.g., Sales:sum:percent_of_row) to generate <showDataAs> XML elements.
• Date grouping is triggered by appending :year, :quarter, or :month to field names in rows or cols, creating <fieldGroup> hierarchies.
• Implementation occurs through the officecli add --type pivottable command, which renders OOXML pivot cache and definition files.

Frequently Asked Questions

How do I configure date grouping by month instead of year?

Append :month to your date field name in the rows or cols property (e.g., --prop 'rows=Date:month'). OfficeCLI automatically generates the appropriate <fieldGroup> XML structure for monthly aggregation without requiring additional flags.

What values are supported for the showDataAs parameter?

OfficeCLI supports percent_of_row, percent_of_col, percent_of_total, and running_total as valid showDataAs tokens. These map directly to Excel's native pivot table calculation types and are defined in the xlsx skill schema.

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

Yes. The source property can reference the same worksheet range across multiple officecli add commands. Each invocation generates an independent pivot table definition while sharing the underlying pivot cache for efficiency.

Where is the pivot table XML generated in the OfficeCLI source?

The XML generation logic resides within the xlsx skill implementation. The CLI constructs <dataField> elements for values, <fieldGroup> elements for date hierarchies, and <showDataAs> elements for percentage calculations, then packages these into the workbook's xl/pivotTables/ and xl/pivotCache/ directories according to the OOXML specification.

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