Skip to content

xlsx pivot parts: pivotTableParts, pivotSource, and fmtId

Searches for OOXML pivot internals usually start from a part name and a question: why a worksheet carries <pivotTableParts>, what <c:pivotSource> binds inside a chart, and what the <c:fmtId> value in it refers to. This page maps those relationships inside the package, names the parts involved, and lists the failures Excel repairs silently.

Read and write pivot tables in Python with WolfXL

You can build every part in the graph below from Python with WolfXL: the pivot cache definition and its records, the pivot table part, the worksheet relationship, and a pivot chart's <c:pivotSource> with its <c:fmtId>. Install the Community release from PyPI:

pip install wolfxl

import wolfxl
from wolfxl.chart import BarChart, Reference
from wolfxl.pivot import PivotCache, PivotTable

wb = wolfxl.Workbook()
ws = wb.active
ws.append(["region", "quarter", "revenue"])
for row in [("East", "Q1", 120), ("East", "Q2", 135),
            ("West", "Q1", 90), ("West", "Q2", 110)]:
    ws.append(row)

# The cache is workbook-scoped; the table is sheet-scoped.
src = Reference(ws, min_col=1, min_row=1, max_col=3, max_row=5)
cache = wb.add_pivot_cache(PivotCache(source=src))
pt = PivotTable(cache=cache, location="E2", rows=["region"],
                cols=["quarter"], data=[("revenue", "sum")])
ws.add_pivot_table(pt)

# Optional pivot chart: pivot_source emits <c:pivotSource> and <c:fmtId>.
chart = BarChart()
chart.add_data(Reference(ws, min_col=3, min_row=1, max_row=5),
               titles_from_data=True)
chart.pivot_source = pt
ws.add_chart(chart, "E10")
wb.save("pivot.xlsx")

# Read the pivot tables back from the saved file.
for handle in wolfxl.load_workbook("pivot.xlsx").active.pivot_tables:
    print(handle.name, handle.location)  # PivotTable1 E2:H6

unzip -l pivot.xlsx then lists xl/pivotCache/pivotCacheDefinition1.xml, xl/pivotCache/pivotCacheRecords1.xml, xl/pivotTables/pivotTable1.xml, and xl/charts/chart1.xml. The cache records are written at save time, so the pivot opens populated with no refresh on open.

The part graph

Part Path inside the package References
Worksheet xl/worksheets/sheet1.xml <pivotTableParts><pivotTablePart r:id="rId1"/>
Worksheet relationships xl/worksheets/_rels/sheet1.xml.rels relationship of type pivotTable to the pivot table part
Pivot table xl/pivotTables/pivotTable1.xml <pivotTableDefinition name="PivotTable1" cacheId="1"> and, once the table has a pivot chart, a <chartFormats> collection
Pivot cache definition xl/pivotCache/pivotCacheDefinition1.xml <cacheSource><worksheetSource ref="A1:D10000" sheet="Data"/> and an r:id to the records part
Pivot cache records xl/pivotCache/pivotCacheRecords1.xml <pivotCacheRecords count="N"> holding one <r> per cached row
Chart xl/charts/chart1.xml <c:pivotSource><c:name>PivotTable1</c:name><c:fmtId val="0"/></c:pivotSource>
Styles xl/styles.xml the differential formats (dxf) a pivot format index can refer to

Each part is bound to its parent through a relationships part, never by path convention alone. xl/worksheets/_rels/sheet1.xml.rels is what turns the r:id in <pivotTableParts> into an actual part.

Why pivotTableParts decides whether a pivot exists at all

<pivotTableParts> on the worksheet is the only link between a sheet and its pivot tables. A package can contain a complete xl/pivotTables/pivotTable1.xml and cache parts that no worksheet refers to. Excel loads the sheet, finds no pivot table parts, and shows nothing: the parts sit in the ZIP and stay unreachable from the workbook graph.

The same rule applies one level up. The workbook part lists the cache through xl/_rels/workbook.xml.rels, and the cache definition points at its records with its own r:id. Removing any one relationship leaves the remaining parts orphaned rather than raising a clear error.

What pivotSource and fmtId bind in a chart

A pivot chart carries two pieces of pivot metadata:

<c:pivotSource>
  <c:name>PivotTable1</c:name>
  <c:fmtId val="0"/>
</c:pivotSource>

<c:name> matches the name attribute on the pivot table definition, which is how the chart attaches to one specific pivot table rather than to the sheet.

<c:fmtId> carries a pivot format id. ECMA-376 Part 1 §21.2.2.70 defines it as the chart's link back to the pivot table: the value matches the chart attribute of a <chartFormat> element in the table part's <chartFormats> collection (§3.10.1.12), whose format attribute then selects the pivot format in use: the chart's <c:pivotFmts> collection holds those entries, and the spreadsheet side describes the same index as a differential format (dxf) in the Styles part. Microsoft Excel typically writes 0 here. The value is a pivot format index rather than a workbook number format: it does not resolve through the number formats in xl/styles.xml, and the number formats a pivot field applies live on pivotField/@numFmtId in the same pivot table part.

<!-- xl/pivotTables/pivotTable1.xml -->
<pivotTableDefinition name="PivotTable1" cacheId="1">
  <chartFormats>
    <chartFormat chart="1" format="0" series="0"/>
  </chartFormats>
</pivotTableDefinition>

Two consequences follow:

  • The two parts refer to each other by name and by index, so renaming the pivot table or renumbering the charts breaks the link without touching the chart's data.
  • A fmtId that matches no <chartFormat> entry leaves the chart's pivot formatting unattached, and Excel may drop it or offer to repair the file. The same rewrites that drop the <pivotTableParts> relationship can leave the chart pointing at a table the workbook no longer loads.

Failures that Excel repairs quietly

Symptom in Excel Cause in the package
Pivot table missing after open Pivot parts exist but the worksheet has no pivotTableParts entry, or the relationship in _rels is absent
"We found a problem with some content" on open Dangling r:id, missing cache records part, or a pivotField/@numFmtId with no matching format
Pivot opens with no rows Cache definition present without a populated pivotCacheRecords part
Chart loses its pivot formatting <c:name> no longer matches the pivot table name, or the chart's fmtId matches no <chartFormat> entry
Pivot survives, formatting does not Pivot parts rewritten from scratch instead of edited in place, discarding format references

Constructing pivot parts from Python

Most Python spreadsheet libraries can preserve pivot parts they read. Emitting a working set of parts is the harder case, because the cache, the table, and the chart's pivot source have to agree on first write.

WolfXL 2.0 constructs the set in modify mode and validates the emitted parts. The openpyxl migration guide carries the worked examples: ws.add_pivot_table(pt) for the cache and table, and chart.pivot_source = pt for the chart binding, which is the attribute that writes the chart's <c:pivotSource> block naming that table.

Checking a package by hand

Unzip the .xlsx and read the relationship parts first. They answer most questions about a broken pivot faster than the pivot XML itself:

unzip -o workbook.xlsx -d /tmp/xlsx-parts
cat /tmp/xlsx-parts/xl/worksheets/_rels/sheet1.xml.rels
cat /tmp/xlsx-parts/xl/_rels/workbook.xml.rels

Grep the sheet for pivotTableParts, then confirm the relationship it names exists in _rels. When a chart loses its pivot formatting rather than its data, check that the <c:name> value matches the pivot table's name attribute, and that any <chartFormat> the table declares names the chart's fmtId value in its chart attribute.