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 which format id <c:fmtId> has to resolve against. This page maps those relationships inside the package, names the parts involved, and lists the failures Excel repairs silently.

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">
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 number formats the fmtId value has to resolve against

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 number-format id. ECMA-376 defines it as part of the pivot source record (CT_PivotSource, section 21.2.2.158), and the value has to resolve against the workbook's format ids. Two consequences follow:

  • A chart whose fmtId no longer resolves renders with default formatting, and Excel may offer to repair the file.
  • Changing the workbook's format table for any other reason can invalidate the id, so a library that rewrites formats without checking the chart's fmtId can break a chart it never touched. WolfXL writes the value per series across its chart families, which is why the 2.0 release notes list pivot-chart linkage as construction work rather than formatting work.

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 fmtId that resolves to no format
Pivot opens with no rows Cache definition present without a populated pivotCacheRecords part
Chart loses its number format fmtId invalidated by a change to the workbook's format ids
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, the chart binding, and the format id all have to agree on first write.

WolfXL 2.0 constructs the set in modify mode and validates the emitted parts. The 2.0 release notes carry 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 <c:pivotSource> and the per-series <c:fmtId>.

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 formatting rather than data, check that the fmtId it names still resolves in xl/styles.xml.