xlsx/pivot-table-authoring

Metadata

XPath assertions

Render assertions

No render assertions declared.

Generator source

scripts/gen_pivot_table_authoring.py

#!/usr/bin/env python3
"""Generate ``fixtures/xlsx/pivot-table-authoring.xlsx``.

A minimal workbook with a Region / Quarter / Sales source range on a
'Data' sheet and a pivot-table anchored at 'Report!A1' built via the
W10-D POC worksheet-level API::

    ws.add_pivot_table(
        "A1:C5",
        rows=["Region"],
        columns=["Quarter"],
        values=["Sales"],           # SUM is the POC default
        target_cell="A1",
        target_sheet="Report",
    )

Designed to satisfy the assertions in
``features/xlsx/pivot-table-authoring.json``.
"""

from __future__ import annotations

import datetime as _dt
from pathlib import Path

from xlsx import Workbook


_REPRODUCIBLE_DT = _dt.datetime(2000, 1, 1, 0, 0, 0)

_REPO_ROOT = Path(__file__).resolve().parent.parent
_OUT = _REPO_ROOT / "fixtures" / "xlsx" / "pivot-table-authoring.xlsx"


def main() -> int:
    wb = Workbook()
    ws = wb.active
    ws.title = "Data"
    ws.append(["Region", "Quarter", "Sales"])
    ws.append(["North", "Q1", 10])
    ws.append(["North", "Q2", 12])
    ws.append(["South", "Q1", 8])
    ws.append(["South", "Q2", 13])
    wb.create_sheet("Report")

    ws.add_pivot_table(
        "A1:C5",
        rows=["Region"],
        columns=["Quarter"],
        values=["Sales"],
        target_cell="A1",
        target_sheet="Report",
    )

    _OUT.parent.mkdir(parents=True, exist_ok=True)
    wb.save(_OUT, zip_date_time=_REPRODUCIBLE_DT)
    print(_OUT)
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

Manifest (expanded)

{
  "$schema": "../manifest.schema.json",
  "id": "xlsx/pivot-table-authoring",
  "title": "Pivot table authoring (worksheet API)",
  "format": "xlsx",
  "category": "pivot-tables",
  "summary": "A PivotTable authored via Worksheet.add_pivot_table(range, rows, columns, values, target_cell) wires up three parts: xl/pivotCache/pivotCacheDefinition1.xml carries the source schema (one <cacheField> per source column), xl/pivotCache/pivotCacheRecords1.xml carries the row-wise cached data, and xl/pivotTables/pivotTable1.xml carries the layout with axis-field role assignments and a single SUM-aggregated data field. SCOPE NOTES: POC only — aggregation is SUM-only (bare-name values default to w:subtotal='sum'); rows and columns are single-level; no filter axis, no slicers, no pivot charts, no timelines, no drawing anchors. Follow-ups: other aggregations (count/average/min/max/etc.), multi-level row/col axes, filter/page axis, slicer+timeline authoring, pivot-chart association, grand-total toggle, number-format carryover on data fields, calculated-field support, OLAP / external cache sources.",
  "spec": {
    "source": "ecma-376-5-part-1",
    "clause": "18.10",
    "element": "x:pivotTableDefinition",
    "rnc_reference": "spec/ecma-376-5/part-1/rnc/SpreadsheetML.rnc",
    "xsd_reference": "spec/ecma-376-5/part-1/xsd/sml.xsd",
    "notes": "ECMA-376 Part 1 clause 18.10 defines the pivot-table family. A conformant pivot is three parts bound by relationships: the worksheet part is related (via RT pivotTable) to a pivotTable part, which is in turn related (via RT pivotCacheDefinition) to a pivotCacheDefinition part, which is related (via RT pivotCacheRecords) to a pivotCacheRecords part. The data-field aggregate lives on <x:dataField>'s @subtotal (clause 18.10.1.40); SUM is the default and the only aggregate the W10-D POC exposes through the worksheet-level wrapper. <x:worksheetSource> on the cache definition records the source range and sheet, satisfying Excel's round-trip expectations. Because the cache records part is optional but strongly recommended (Excel will refresh an empty-records pivot on open; a missing records part produces a warning), the authoring path always emits it."
  },
  "fixtures": {
    "machine": "xlsx/pivot-table-authoring"
  },
  "generator": {
    "python": "scripts/gen_pivot_table_authoring.py"
  },
  "assertions": [
    {
      "id": "pivot-table-definition-part-present",
      "part": "xl/pivotTables/pivotTable1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "/x:pivotTableDefinition",
      "must": "exist",
      "description": "The pivot-table part must exist and its document element must be <x:pivotTableDefinition>."
    },
    {
      "id": "pivot-cache-definition-part-present",
      "part": "xl/pivotCache/pivotCacheDefinition1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "/x:pivotCacheDefinition/x:cacheSource/x:worksheetSource[@ref='A1:C5' and @sheet='Data']",
      "must": "exist",
      "description": "The pivot-cache-definition part must exist and must point at the Region/Quarter/Sales source range on the Data sheet."
    },
    {
      "id": "pivot-cache-records-part-present",
      "part": "xl/pivotCache/pivotCacheRecords1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "/x:pivotCacheRecords/x:r",
      "must": "exist",
      "description": "The pivot-cache-records part must exist and must contain at least one <r> row record (four rows for this fixture)."
    },
    {
      "id": "pivot-data-field-is-sum",
      "part": "xl/pivotTables/pivotTable1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:dataFields/x:dataField[@subtotal='sum']",
      "must": "exist",
      "description": "The single data field on the pivot must aggregate with SUM (matches the POC's only supported aggregation)."
    },
    {
      "id": "pivot-row-axis-field",
      "part": "xl/pivotTables/pivotTable1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:rowFields/x:field",
      "must": "exist",
      "description": "At least one <field> entry must be present under <rowFields> (the 'Region' axis)."
    },
    {
      "id": "pivot-col-axis-field",
      "part": "xl/pivotTables/pivotTable1.xml",
      "namespaces": {
        "x": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
      },
      "xpath": "//x:colFields/x:field",
      "must": "exist",
      "description": "At least one <field> entry must be present under <colFields> (the 'Quarter' axis)."
    }
  ]
}