Refresh Pivot Tables and Pivot Caches in Aspose.Cells for Python via .NET

Aspose.Cells provides a layered refresh API that lets you reload pivot data at four different scopes — from the entire workbook down to a single pivot table. Starting with Aspose.Cells for Python via .NET v26.7, the legacy method PivotTable.refresh_data() is marked obsolete and should be replaced with the more efficient, cache-aware APIs described in this article.

Introduction

Refreshing a pivot table is rarely a single operation. Behind the scenes, Aspose.Cells maintains a layered data chain that connects your original source data to the rendered values you see in the worksheet. Understanding this chain is the key to choosing the right refresh API for any situation.

The four-layer data chain is:

Data Source — the original worksheet ranges, database query, or consolidation range where the raw values live.
PivotCache — the in-memory snapshot of the source data. Every pivot table is built on top of a PivotCache; this is where all data is gathered and aggregated.
PivotTable — the view object that defines row, column, value, and filter fields. A PivotTable reads only from its PivotCache, never directly from the data source.
Cells — the worksheet Cells that the PivotTable renders its computed values and styles into.

PivotCache.source_type (enum PivotTableSourceType) indicates where the cache data came from. As of v26.7, PivotCache.refresh() supports only the Sheet and Consolidation source types — that is, data that lives in worksheet ranges. External sources (databases, external connections, etc.) are not yet refreshable through the cache API.

Because of this chain, there are two fundamental refresh paths in Aspose.Cells:

PivotCache.refresh() — reloads source → cache AND recalculates all dependent PivotTables in a single operation.
PivotTable.calculate_data() — recalculates one PivotTable’s display from already-cached data, with no round-trip back to the data source.

All scenarios in this article use worksheet-cell source data, so the source type is Sheet and refresh operations behave as described.

Quick Start

If you just need the shortest possible code that refreshes every pivot in the workbook, a single call is enough:

using Aspose.Cells;

Workbook workbook = new Workbook("input.xlsx");
workbook.RefreshAll();
workbook.Save("output.xlsx");

Everything else in this article explains when to choose a narrower API instead.

Required Imports

All Python examples in this article begin with the following three import statements because the pivot types live in the aspose.cells.pivot namespace:

import sys
import aspose.cells
import aspose.cells.pivot

Refresh All Pivot Tables in the Workbook

When you need to ensure that every pivot cache and every pivot table in the workbook reflects the latest source data, the simplest and most comprehensive API is Workbook.refresh_all(). A single call traverses the entire workbook — refreshing each PivotCache from its source and then recalculating every dependent PivotTable. This is the recommended approach for general, full-document refreshes where performance is not a concern.

The following example builds a workbook with a Fruit/Year/Amount source range, creates one pivot table, modifies some source values, and then uses refresh_all() to bring everything up to date in a single call.

import aspose.cells as ac

# Create a new workbook
workbook = ac.Workbook()
worksheet = workbook.worksheets[0]

# Write header row into cells A1:C1
worksheet.cells["A1"].put_value("Fruit")
worksheet.cells["B1"].put_value("Year")
worksheet.cells["C1"].put_value("Amount")

# Write data rows into cells A2:C9 (8 rows of fruit data across 2020 and 2021)
worksheet.cells["A2"].put_value("grape")
worksheet.cells["B2"].put_value(2020)
worksheet.cells["C2"].put_value(50)

worksheet.cells["A3"].put_value("blueberry")
worksheet.cells["B3"].put_value(2020)
worksheet.cells["C3"].put_value(60)

worksheet.cells["A4"].put_value("kiwi")
worksheet.cells["B4"].put_value(2020)
worksheet.cells["C4"].put_value(70)

worksheet.cells["A5"].put_value("cherry")
worksheet.cells["B5"].put_value(2020)
worksheet.cells["C5"].put_value(80)

worksheet.cells["A6"].put_value("grape")
worksheet.cells["B6"].put_value(2021)
worksheet.cells["C6"].put_value(90)

worksheet.cells["A7"].put_value("blueberry")
worksheet.cells["B7"].put_value(2021)
worksheet.cells["C7"].put_value(100)

worksheet.cells["A8"].put_value("kiwi")
worksheet.cells["B8"].put_value(2021)
worksheet.cells["C8"].put_value(110)

worksheet.cells["A9"].put_value("cherry")
worksheet.cells["B9"].put_value(2021)
worksheet.cells["C9"].put_value(120)

# Add a pivot table: source range "A1:C9", destination cell "E3", name "Pivot1"
pivot_index = worksheet.pivot_tables.add("A1:C9", "E3", "Pivot1")
pivot_table = worksheet.pivot_tables[pivot_index]

# Assign pivot fields: Fruit to Rows, Year to Columns, Amount to Data
pivot_table.add_field_to_area(ac.PivotFieldType.ROW, "Fruit")
pivot_table.add_field_to_area(ac.PivotFieldType.COLUMN, "Year")
pivot_table.add_field_to_area(ac.PivotFieldType.DATA, "Amount")

# Modify several Amount values in the source data to simulate changes
worksheet.cells["C2"].put_value(55)
worksheet.cells["C5"].put_value(85)
worksheet.cells["C9"].put_value(125)

# Refresh every pivot table / pivot cache in the workbook
workbook.refresh_all()

# Save the workbook
workbook.save("output.xlsx")

Refresh All Pivot Tables on a Single Worksheet

Sometimes you only need to refresh the pivot tables that live on one specific worksheet — for example, when pivot tables on other worksheets are known to be unrelated and shouldn’t be touched. For this case, Aspose.Cells provides Worksheet.refresh_pivot_tables(), which is scoped to a single Worksheet instance.

This is more selective than Workbook.refresh_all(): only the pivot tables on the targeted worksheet are refreshed, leaving any pivot tables on other worksheets untouched.

import aspose.cells as ac

workbook = ac.Workbook()
worksheet = workbook.worksheets[0]

worksheet.cells["A1"].put_value("Fruit")
worksheet.cells["B1"].put_value("Year")
worksheet.cells["C1"].put_value("Amount")

worksheet.cells["A2"].put_value("grape")
worksheet.cells["B2"].put_value(2020)
worksheet.cells["C2"].put_value(100)

worksheet.cells["A3"].put_value("blueberry")
worksheet.cells["B3"].put_value(2021)
worksheet.cells["C3"].put_value(150)

worksheet.cells["A4"].put_value("kiwi")
worksheet.cells["B4"].put_value(2020)
worksheet.cells["C4"].put_value(200)

worksheet.cells["A5"].put_value("cherry")
worksheet.cells["B5"].put_value(2021)
worksheet.cells["C5"].put_value(120)

worksheet.cells["A6"].put_value("grape")
worksheet.cells["B6"].put_value(2021)
worksheet.cells["C6"].put_value(180)

worksheet.cells["A7"].put_value("blueberry")
worksheet.cells["B7"].put_value(2020)
worksheet.cells["C7"].put_value(130)

worksheet.cells["A8"].put_value("kiwi")
worksheet.cells["B8"].put_value(2021)
worksheet.cells["C8"].put_value(220)

worksheet.cells["A9"].put_value("cherry")
worksheet.cells["B9"].put_value(2020)
worksheet.cells["C9"].put_value(140)

pivot_index = worksheet.pivot_tables.add("A1:C9", "E3", "Pivot1")
pivot_table = worksheet.pivot_tables[pivot_index]

pivot_table.add_field_to_area(ac.PivotFieldType.ROW, "Fruit")
pivot_table.add_field_to_area(ac.PivotFieldType.COLUMN, "Year")
pivot_table.add_field_to_area(ac.PivotFieldType.DATA, "Amount")

worksheet.cells["C2"].put_value(300)
worksheet.cells["C5"].put_value(250)
worksheet.cells["C9"].put_value(400)

worksheet.refresh_pivot_tables()

workbook.save("output.xlsx")

Refresh a Single Pivot Table

When you want fine-grained control over a single pivot table, the cache-based API gives you two options. The choice between them depends on what actually changed: the underlying source data, or just the view/layout settings of the pivot table itself.

Source Data Changed — Use `PivotCache.refresh()`

If the underlying source data has changed, the right entry point is pivot_table.pivot_cache.refresh(). This call re-reads the source data into the cache and then recalculates every PivotTable that depends on that cache.

Only View/Layout Changed — Use `calculate_data()`

If the source data has not changed but only the pivot table’s view or layout settings have been modified (for example, a field has been moved to a different area, or a refresh-on-open setting has been toggled), there is no need to round-trip back to the data source. The cache already holds the right data; only the rendered PivotTable needs recalculation. In this case, pivot_table.calculate_data() is the right choice.

The following example modifies a non-source property of the pivot table and then calls calculate_data() to re-render it from the existing cache.

import aspose.cells as ac
import aspose.cells.pivot as acp

workbook = ac.Workbook()
worksheet = workbook.worksheets[0]

# Write Fruit / Year / Amount header row
worksheet.cells["A1"].put_value("Fruit")
worksheet.cells["B1"].put_value("Year")
worksheet.cells["C1"].put_value("Amount")

# Write 8 data rows (rows 2-9, fitting the source range A1:C9)
worksheet.cells["A2"].put_value("Grape")
worksheet.cells["B2"].put_value(2020)
worksheet.cells["C2"].put_value(100)

worksheet.cells["A3"].put_value("Blueberry")
worksheet.cells["B3"].put_value(2020)
worksheet.cells["C3"].put_value(200)

worksheet.cells["A4"].put_value("Kiwi")
worksheet.cells["B4"].put_value(2020)
worksheet.cells["C4"].put_value(300)

worksheet.cells["A5"].put_value("Cherry")
worksheet.cells["B5"].put_value(2020)
worksheet.cells["C5"].put_value(400)

worksheet.cells["A6"].put_value("Grape")
worksheet.cells["B6"].put_value(2021)
worksheet.cells["C6"].put_value(150)

worksheet.cells["A7"].put_value("Blueberry")
worksheet.cells["B7"].put_value(2021)
worksheet.cells["C7"].put_value(250)

worksheet.cells["A8"].put_value("Kiwi")
worksheet.cells["B8"].put_value(2021)
worksheet.cells["C8"].put_value(350)

worksheet.cells["A9"].put_value("Cherry")
worksheet.cells["B9"].put_value(2021)
worksheet.cells["C9"].put_value(450)

# Add a pivot table named "Pivot1" placed at destination cell E3, sourcing from A1:C9
pivot_index = worksheet.pivot_tables.add("A1:C9", "E3", "Pivot1")
pivot_table = worksheet.pivot_tables[pivot_index]

# Assign fields: Fruit to Row, Year to Column, Amount to Data
pivot_table.add_field_to_area(acp.PivotFieldType.ROW, "Fruit")
pivot_table.add_field_to_area(acp.PivotFieldType.COLUMN, "Year")
pivot_table.add_field_to_area(acp.PivotFieldType.DATA, "Amount")

# Modify a view/layout property — this is a presentation-only change,
# so it does NOT require re-reading the source data through PivotCache.Refresh().
pivot_table.refresh_data_on_opening_file = False

# CalculateData() re-renders THIS pivot table's display (data + style) from the
# data already held in the PivotCache. Because the source data did not change,
# no round-trip to the source is performed — only the cached values are recalculated
# into worksheet cells.
pivot_table.calculate_data()

# Save the workbook to disk
workbook.save("output.xlsx")

A workbook often contains many pivot tables that all sit on top of one shared cache. To enumerate them — for example, before performing a batch refresh, or to diagnose shared-cache impact — use PivotCache.get_pivot_tables(). This method returns the collection of every PivotTable that depends on the given cache.

Migrating from the Obsolete `PivotTable.refresh_data()`

Prior to Aspose.Cells for Python via .NET v26.7, the standard way to refresh a pivot table was to call PivotTable.refresh_data() on each pivot table individually. As of v26.7, that method is marked obsolete and should be replaced with the cache-aware APIs described above.

There are two reasons the per-table refresh_data() approach is problematic in real-world workbooks:

It re-fetches data from the source every time it is called, even when the source has not changed.

The recommended replacements are:

Refresh ALL pivot tables in the workbook → use workbook.refresh_all();
Refresh SOME of them → use pivot_table.pivot_cache.refresh(); for one cache. Because the cache is shared, this single call updates every pivot table built on top of that cache. Other pivot tables that sit on an already-refreshed cache can be safely skipped.
Only the pivot view/layout changed → use pivot_table.calculate_data(); to re-render from the existing cache without any source round-trip.

The following example demonstrates the new efficient pattern for workbooks with multiple pivot tables sharing a single cache.

Which Refresh API Should I Use?

The table below summarizes the available refresh APIs and when to choose each one.

Goal	Recommended API	Notes
Refresh everything in the workbook	`Workbook.refresh_all()`	One call; covers all caches and tables.
Refresh only pivot tables on a single sheet	`Worksheet.refresh_pivot_tables()`	Scoped to one worksheet.
Source data changed for one cache	`pivot_table.pivot_cache.refresh()`	Refreshes ALL pivot tables on that shared cache.
Only view/layout settings changed	`pivot_table.calculate_data()`	Skips unnecessary source round-trip.
List all pivot tables on a shared cache	`pivot_cache.get_pivot_tables()`	Use to enumerate before bulk refresh.

In practice, prefer the cache-based APIs over the obsolete per-table refresh_data(). They are aware of shared caches, they avoid redundant source fetches, and they let you choose the smallest scope that satisfies your refresh requirement.

Common Pitfalls

Forgetting to refresh before saving. A pivot table only writes its rendered values into the worksheet when its data chain is refreshed. If you modify source cells, call PivotCache.Refresh() (or Workbook.RefreshAll()) before Workbook.Save(), otherwise the saved file still contains the old aggregated values.
Calling the obsolete RefreshData() per table. In v26.7, PivotTable.RefreshData() is marked obsolete and re-fetches the source for every call. With multiple pivot tables sharing a cache this means N redundant source fetches. Replace with a single PivotCache.Refresh() followed by CalculateData() per table.
Refreshing when only the layout changed. If you only changed a pivot table’s view (column order, ConsolidationFunction, etc.) without touching source data, PivotCache.Refresh() is unnecessary and slow. Call pivotTable.CalculateData() to re-render from the existing cache.
External source not supported by PivotCache.Refresh(). If the pivot table’s source comes from an external connection (database, OLAP cube, etc.), PivotCache.Refresh() cannot refresh it in v26.7 — it currently only supports Sheet and Consolidation source types. For external sources, re-open the workbook or rebuild the cache from the source.

Add Calculated Field in Pivot Table Add Pivot Table Row and Column Fields in Aspose.Cells for Python via .NET