Refresh an Excel pivot table in C# · onedocument cookbook

Update an existing pivot table, a summary of worksheet rows. pivot.xlsx is in the zip. Worksheet-backed sources are supported; external, OLAP and Data Model sources are not refreshed.

Terminal

dotnet add package RecursiveAI.OneDocument.Spreadsheets

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

using RecursiveAI.OneDocument.Spreadsheets;
 
using var book = Workbook.Open("pivot.xlsx");
book.Sheets["Data"]["D2"].SetNumber(250);
var result = book.RefreshPivots();
book.SaveAs("refreshed.xlsx");
Console.WriteLine(result.AllRefreshed);

Writes refreshed.xlsx and prints True.

Unzip the example, then dotnet run.

The full program

The Data sheet has Region, Product, Qtr, Sales and Units columns. Here is its first data row; the program changes Sales from 100 to 250:

Column	First data row
Region	East
Product	A
Qtr	Q1
Sales	100
Units	1

The Pivot sheet summarizes sales by region. The full program prints the refresh status, source record count and updated East total. The sample comes from the library’s MIT-licensed pivot examples; its license is included.

using RecursiveAI.OneDocument.Spreadsheets;
 
using var book = Workbook.Open("pivot.xlsx");
book.Sheets["Data"]["D2"].SetNumber(250);
var result = book.RefreshPivots();
book.SaveAs("refreshed.xlsx");
foreach (var item in result.Outcomes)
    Console.WriteLine($"{item.Status}: {item.RecordCount} records");
Console.WriteLine($"East: {book.Sheets["Pivot"]["B4"].Number}");

dotnet run prints:

Text

Refreshed: 8 records
East: 680

Inspect each outcome: NotSupported or SourceInvalid means that table was not refreshed; an unreadable source leaves that pivot’s cache and cells as they were.

Variations

Refresh one table and recalculate dependent formulas.

using RecursiveAI.OneDocument.Spreadsheets;
 
using var book = Workbook.Open("pivot.xlsx");
book.Sheets["Data"]["D2"].SetNumber(250);
var result = book.Sheets["Pivot"].PivotTables.First()
    .Refresh();
book.Recalculate();
book.SaveAs("one-pivot.xlsx");
Console.WriteLine(result.Status);

Writes one-pivot.xlsx and prints Refreshed. Tables sharing that cache are refreshed together.

The short answer

Open the workbook, update its source cells, call RefreshPivots, and save the result. OneDocument rebuilds supported worksheet-backed pivot caches and their tables. Check the returned outcomes: saving the workbook is not evidence that every pivot was refreshed.

How do I refresh my Excel PivotTable?

The snippet at the top updates one source value before refreshing every pivot in the workbook. The included workbook already contains a cache and a pivot table; this recipe does not create a new pivot definition. Its Data sheet has eight records, and the Pivot sheet summarizes Sales by Region. Changing D2 from 100 to 250 increases the East total from 530 to 680.

Workbook.RefreshPivots() returns one outcome per table. Refreshed means that the cache and table were rebuilt. NotSupported means the source is external, a cube, the Data Model, a consolidation or a scenario, or the table has no cache. SourceInvalid means a required source sheet, range, table or name is missing, or a source header is empty. The pivot documentation describes these outcomes.

A workbook containing a mix of supported and unsupported sources needs more than one overall success message: the full program prints each table’s status and record count.

What does refresh do in a PivotTable?

Refreshing rebuilds the cache from a worksheet, table or defined-name source, then rebuilds every table using that cache. Even the single-table variation refreshes sibling tables that share the cache, because changed shared-item indexes would otherwise leave those siblings inconsistent.

The refresh writes the table’s displayed cells into its worksheet. It does not recalculate formulas. If another cell reads a pivot result, or uses GETPIVOTDATA, call Recalculate after refreshing, as the variation does. Conversely, a formula in source data needs its current result before the cache reads it. Separate source calculation, pivot refresh and dependent calculation in that order when your workbook needs all three.

Not every pivot feature is reproduced. Pivot charts, slicers, timelines, the Data Model, OLAP and external sources are kept as stored. The documentation says refresh of calculated items, label and value filters, show-values-as parent/rank forms, custom subtotals and hierarchy-based fields is reported, not reproduced. Treat those reports as a reason to review a workbook, not as a successful replacement for the original feature.

Can I auto-refresh a PivotTable?

You can make refresh an explicit step of your C# update job: write the new source values, refresh, inspect outcomes, recalculate dependents and save. The example does that work immediately; it does not merely request a refresh the next time someone opens Excel.

A failed or cancelled refresh does not partially rewrite its cache. The library performs the work in memory first; an unreadable source leaves that pivot’s cache and cells as they were. A workbook-wide call can still report different outcomes for different tables, so check them individually. When no refresh is requested, opening and saving preserves the stored pivot parts rather than rebuilding them.