Working with PivotTables

PivotTables are represented by several coordinated parts. A worksheet can own one or more pivot table definition parts, while the workbook owns pivot cache definitions and those cache definitions can own cache records.

PivotTables present aggregated views of source data. The visible PivotTable cells on the worksheet are display data; the reusable source field metadata and cached records live in the pivot cache parts.

PivotTable package model

The pivot table definition references a pivot cache. Multiple PivotTables can use the same cache.

The pivot table definition describes which fields appear on the row axis, column axis, values area, and report filter area. The pivot cache definition describes all available source fields, including fields that a particular PivotTable is not currently using.

PivotTable markup

<pivotTableDefinition name="PivotTable1" cacheId="1">
  <location ref="A3:C10" firstHeaderRow="1" firstDataRow="2"/>
  <pivotFields count="2">
    <pivotField axis="axisRow"/>
    <pivotField dataField="1"/>
  </pivotFields>
</pivotTableDefinition>

Rust workflow

Use the workbook and worksheet part graph to locate pivot-related parts. WorkbookPart exposes pivot cache definition parts, and WorksheetPart exposes pivot table parts.

#![allow(unused)]
fn main() {
pub fn count_pivottable_parts(
  path: &Path,
) -> Result<PivotTablePartCounts, Box<dyn std::error::Error>> {
  let document = SpreadsheetDocument::new_from_file_with_settings(path, lazy_settings())?;
  let workbook_part = document.workbook_part()?;
  let worksheet_pivot_tables = workbook_part
    .worksheet_parts(&document)
    .map(|worksheet_part| worksheet_part.pivot_table_parts(&document).count())
    .sum();
  let workbook_pivot_caches = workbook_part
    .pivot_table_cache_definition_parts(&document)
    .count();

  Ok(PivotTablePartCounts {
    worksheet_pivot_tables,
    workbook_pivot_caches,
  })
}
}

In ooxmlsdk, the generated part graph includes WorkbookPart::pivot_table_cache_definition_parts, WorksheetPart::pivot_table_parts, PivotTablePart::pivot_table_cache_definition_part, and PivotTableCacheDefinitionPart::pivot_table_cache_records_part. The corresponding schema types include PivotTableDefinition, PivotField, PivotCacheDefinition, and PivotCacheRecords.

Creating or editing PivotTables requires coordinated cache, worksheet, relationship, and display cell updates. Keep those updates together: a pivot table definition without a matching cache, worksheet relationship, and displayed cell range is not a complete PivotTable.