Pivot Tables

A pivot table summarises a block of source data by grouping it along one or more fields and aggregating a value field for each group. In XLibur you describe the pivot specification — which fields go on rows, on columns, into the filter area, and what to aggregate — and Excel renders the result when the file is opened.

A pivot table has four areas:

Area	Property	Role
Rows	`RowLabels`	Fields that group data down the left
Columns	`ColumnLabels`	Fields that group data across the top
Values	`Values`	Fields that are aggregated in the body
Filters	`ReportFilters`	Fields exposed as page-level dropdowns

note

XLibur writes the pivot table definition and a cache of the source data; it does not compute the summarised cells itself. Excel (or LibreOffice) recalculates the body on open. That means a pivot table you create has no aggregated values to read back — the body is empty until the file is opened in a spreadsheet application.

A pivot table loaded from a file is different: the cells Excel already rendered into the pivot's range are left where they are, and read back as ordinary cell values. Loading a workbook and saving it therefore no longer empties its pivot tables, which it used to do.

Inserting data

A pivot table needs a source. The cleanest one is an Excel table, because the pivot follows the table as it grows:

using XLibur.Excel;

var pastries = new List<Pastry>
{
    new("Croissant", 150, 60.2, "Apr"),
    new("Croissant", 250, 50.42, "May"),
    new("Doughnut", 250, 89.99, "Apr"),
    new("Doughnut", 225, 70, "May"),
    new("Danish", 394, 20.24, "Apr"),
    new("Danish", 190, 60, "May"),
};

using var workbook = new XLWorkbook();
var dataSheet = workbook.Worksheets.Add("PastrySalesData");

var source = dataSheet.Cell(1, 1).InsertTable(pastries, "PastrySalesData", createTable: true);
dataSheet.Columns().AdjustToContents();

A plain range works equally well when the data will not change size:

var sourceRange = dataSheet.Range("A1:D7");

Creating

Put the pivot on its own sheet and anchor it at a target cell. That cell becomes the pivot's top-left corner:

var pivotSheet = workbook.Worksheets.Add("Summary");

var pivot = pivotSheet.PivotTables.Add("SalesPivot", pivotSheet.Cell(1, 1), source);

PivotTables.Add has three overloads, one per source kind:

pivotSheet.PivotTables.Add("P1", pivotSheet.Cell("A1"), table);        // from an IXLTable
pivotSheet.PivotTables.Add("P2", pivotSheet.Cell("A1"), range);        // from an IXLRange
pivotSheet.PivotTables.Add("P3", pivotSheet.Cell("A1"), pivotCache);   // reuse an existing cache

There is also a shortcut straight from the source:

var pivot = source.CreatePivotTable(pivotSheet.FirstCell(), "SalesPivot");

tip

Two pivot tables built from the same range share one pivot cache automatically, which keeps the file smaller. To share deliberately, pass existingPivot.PivotCache to the Add overload.

Refreshing the cache

A pivot cache holds a copy of the source data. After you change the source, refresh the cache so that the pivot table uses the new data:

pivot.PivotCache.Refresh();

XLibur can refresh a cache only if its source is in the same workbook: a range, a table or a defined name. Excel can also make a pivot table from other sources, such as another workbook or a database. XLibur keeps these pivot tables when it saves the file, but it cannot read their data, so Refresh throws NotSupportedException. Check SourceKind first:

var cache = pivot.PivotCache;

if (cache.SourceKind is XLPivotSourceKind.Range or XLPivotSourceKind.Name)
{
    cache.Refresh();
}

If you rename the sheet that holds the source data, the cache follows it. If you delete that sheet, the cache and its pivot tables stay, with the data they already had.

Finding and removing pivot tables:

var pivot = pivotSheet.PivotTables.PivotTable("SalesPivot");

if (pivotSheet.PivotTables.Contains("SalesPivot"))
{
    pivotSheet.PivotTables.Delete("SalesPivot");
}

pivotSheet.PivotTables.DeleteAll();

Adding fields

Field names are the source column names — the table's header text, or the property names of the objects you inserted.

// Rows: one group per pastry name, then per month within it
pivot.RowLabels.Add("Name");
pivot.RowLabels.Add("Month");

// Columns: one column group per month
pivot.ColumnLabels.Add("Month");

// Filters: a page-level dropdown
pivot.ReportFilters.Add("Name");

Give a field a display name different from the source column with the two-argument overload:

pivot.RowLabels.Add("Name", "Pastry");

Field options

Each Add returns the field, so options chain off it:

pivot.RowLabels.Add("Name")
    .SetSort(XLPivotSortType.Ascending)
    .SetCollapsed()
    .SetRepeatItemLabels()
    .SetInsertBlankLines(false);

pivot.RowLabels.Add("Month")
    .SetSort(XLPivotSortType.Descending)
    .SetShowBlankItems(false);

Option	Effect
`SetSort(XLPivotSortType)`	`Default` (manual), `Ascending`, `Descending`
`SetCollapsed(bool)`	Start the group collapsed
`SetLayout(XLPivotLayout)`	`Compact`, `Outline`, or `Tabular` for this field
`SetRepeatItemLabels(bool)`	Repeat the group label on every row
`SetInsertBlankLines(bool)`	Blank line after each group
`SetShowBlankItems(bool)`	Include items with no data
`SetInsertPageBreaks(bool)`	Page break after each group when printing
`SetSubtotalsAtTop(bool)`	Subtotals above rather than below the group
`SetSubtotalCaption(string)`	Custom subtotal label

Filter fields with pre-selected values

Report filters can be pre-set to a subset of values:

pivot.ReportFilters.Add("Name")
    .AddSelectedValue("Croissant")
    .AddSelectedValue("Doughnut");

pivot.ReportFilters.Add("Month")
    .AddSelectedValues(["Apr", "May"]);

Values and totals columns

Values.Add puts a field in the body. The default aggregation is Sum:

pivot.Values.Add("NumberOfOrders");
pivot.Values.Add("Quality");

Set the aggregation explicitly with SetSummaryFormula:

pivot.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
pivot.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Average);
pivot.Values.Add("Name").SetSummaryFormula(XLPivotSummary.Count);

Available summaries: Sum, Count, Average, Minimum, Maximum, Product, CountNumbers, StandardDeviation, PopulationStandardDeviation, Variance, PopulationVariance.

The same field twice

Use the two-argument overload to add one source field under several names — a common way to show both a total and a count of the same column:

pivot.Values.Add("NumberOfOrders", "Total orders").SetSummaryFormula(XLPivotSummary.Sum);
pivot.Values.Add("NumberOfOrders", "Order count").SetSummaryFormula(XLPivotSummary.Count);
pivot.Values.Add("NumberOfOrders", "Average order").SetSummaryFormula(XLPivotSummary.Average);

Number formats on values

pivot.Values.Add("Quality", "Sum of Quality")
    .NumberFormat.SetFormat("#,##0.00");

pivot.Values.Add("Revenue", "Revenue")
    .NumberFormat.Format = "$ #,##0";

"Show values as" calculations

Beyond raw aggregation, a value can be shown relative to something else:

pivot.Values.Add("NumberOfOrders", "% of total").ShowAsPercentageOfTotal();
pivot.Values.Add("NumberOfOrders", "% of row").ShowAsPercentageOfRow();
pivot.Values.Add("NumberOfOrders", "% of column").ShowAsPercentageOfColumn();
pivot.Values.Add("NumberOfOrders", "Running total").ShowAsRunningTotalIn("Month");

// Relative to a specific item of another field
pivot.Values.Add("NumberOfOrders", "% of Danish")
    .ShowAsPercentageFrom("Name").And("Danish")
    .NumberFormat.Format = "0%";

// Difference from the previous item
pivot.Values.Add("NumberOfOrders", "Change")
    .ShowAsDifferenceFrom("Month").AndPrevious();

pivot.Values.Add("NumberOfOrders").ShowAsNormal();   // back to plain aggregation

Where the value headers sit

With more than one value field, Excel adds a "Values" pseudo-field. Place it explicitly on rows or columns using the sentinel label:

// Value names down the rows
pivot.RowLabels.Add(XLConstants.PivotTable.ValuesSentinalLabel);
pivot.RowLabels.Add("Name");

// ...or across the columns
pivot.ColumnLabels.Add("Month");
pivot.ColumnLabels.Add(XLConstants.PivotTable.ValuesSentinalLabel);

Grand totals and subtotals

pivot.ShowGrandTotalsRows = true;      // total row at the bottom
pivot.ShowGrandTotalsColumns = true;   // total column on the right

// Fluent equivalents
pivot.SetShowGrandTotalsRows(false)
     .SetShowGrandTotalsColumns(true);

Subtotals for the intermediate groups are controlled at the table level:

pivot.Subtotals = XLPivotSubtotals.DoNotShow;   // or AtTop, AtBottom
pivot.SetSubtotals(XLPivotSubtotals.AtBottom);

Per-field subtotal functions — a field can carry several at once:

var field = pivot.RowLabels.Add("Name");
field.AddSubtotal(XLSubtotalFunction.Sum);
field.AddSubtotal(XLSubtotalFunction.Average);
field.SetSubtotal(XLSubtotalFunction.Count, enabled: false);

Layout and appearance

pivot.Layout = XLPivotLayout.Tabular;      // Compact (default), Outline, or Tabular
pivot.SetLayout(XLPivotLayout.Outline);

pivot.Theme = XLPivotTableTheme.PivotStyleMedium9;

pivot.ShowRowHeaders = true;
pivot.ShowColumnHeaders = true;
pivot.SetShowRowStripes()
     .SetShowColumnStripes(false)
     .SetShowLastColumn();

pivot.SetRowHeaderCaption("Pastry name");
pivot.SetColumnHeaderCaption("Measures");

pivot.AutofitColumns = true;
pivot.PreserveCellFormatting = true;

The five table-style emphasis flags are ShowRowHeaders, ShowColumnHeaders, ShowRowStripes, ShowColumnStripes and ShowLastColumn, each with a Set… overload taking a bool and one taking nothing (which sets true).

A pivot table also carries accessibility text, which survives a save and reload:

pivot.Title = "Sales by pastry and month";
pivot.Description = "Orders and average quality, grouped by pastry name.";

Copying a pivot table

CopyTo reproduces the whole definition on another sheet — every setting the reader and writer carry, including compact/outline form, visual totals, the grand-total caption and the data caption:

var copy = pivot.CopyTo(otherSheet.Cell("A1"));

The one thing a copy deliberately does not inherit is chartFormat, which is the next free id for the pivot charts pointing at the source table — and a copy has none of them.

Handling gaps and errors in the source:

pivot.EmptyCellReplacement = "—";
pivot.ErrorValueReplacement = "n/a";
pivot.SetShowEmptyItemsOnRows(false);
pivot.SetShowEmptyItemsOnColumns(false);

Interaction switches, which matter mostly for the printed or exported view:

pivot.SetShowExpandCollapseButtons(false);
pivot.SetDisplayCaptionsAndDropdowns(false);
pivot.SetClassicPivotTableLayout();

Slicers and timelines

A pivot table can be filtered from a button panel or a date scrubber drawn beside it:

// A slicer on any cache field
var slicer = pivotSheet.Slicers.Add(pivot, "Name");
slicer.Position = pivotSheet.Cell("F1");

// A timeline on a cache field holding dates
var timeline = pivotSheet.Timelines.Add(pivot, "OrderDate");
timeline.Position = pivotSheet.Cell("F12");

pivot.Slicers and pivot.Timelines list the controls pointing at this pivot table, wherever in the workbook they are drawn. See Slicers and Timelines for reading, styling and positioning them.

note

A pivot table XLibur creates is stamped with a createdVersion that supports slicers. Files written before this was fixed carry version 0, which Excel reads as "written before slicers existed" and silently refuses to attach a slicer to — no repair prompt, no validation error, the panel simply never appears. Re-saving such a file with a current build does not restamp it: a pivot table loaded from a file keeps whatever version its file declares.

A worked example

using XLibur.Excel;

public record Pastry(string Name, int NumberOfOrders, double Quality, string Month);

using var workbook = new XLWorkbook();

// 1. The source data, as a table
var dataSheet = workbook.Worksheets.Add("PastrySalesData");
var pastries = new List<Pastry>
{
    new("Croissant", 150, 60.2, "Apr"),
    new("Croissant", 250, 50.42, "May"),
    new("Croissant", 134, 22.12, "Jun"),
    new("Doughnut", 250, 89.99, "Apr"),
    new("Doughnut", 225, 70, "May"),
    new("Doughnut", 210, 75.33, "Jun"),
    new("Danish", 394, 20.24, "Apr"),
    new("Danish", 190, 60, "May"),
    new("Danish", 221, 24.76, "Jun"),
};

var source = dataSheet.Cell(1, 1).InsertTable(pastries, "PastrySalesData", createTable: true);
dataSheet.Columns().AdjustToContents();

// 2. The pivot
var pivotSheet = workbook.Worksheets.Add("Summary");
var pivot = pivotSheet.PivotTables.Add("SalesPivot", pivotSheet.Cell(1, 1), source);

pivot.RowLabels.Add("Name").SetSort(XLPivotSortType.Ascending);
pivot.ColumnLabels.Add("Month");

pivot.Values.Add("NumberOfOrders", "Orders")
     .SetSummaryFormula(XLPivotSummary.Sum);

pivot.Values.Add("Quality", "Avg quality")
     .SetSummaryFormula(XLPivotSummary.Average)
     .NumberFormat.SetFormat("#,##0.00");

pivot.SetRowHeaderCaption("Pastry")
     .SetColumnHeaderCaption("Month")
     .SetShowGrandTotalsRows()
     .SetShowGrandTotalsColumns();

pivot.Subtotals = XLPivotSubtotals.DoNotShow;
pivot.Theme = XLPivotTableTheme.PivotStyleMedium9;

pivotSheet.Columns().AdjustToContents();
workbook.SaveAs("PastrySales.xlsx");

Where to next

Slicers and Timelines — the button panels and date scrubbers that filter a pivot
Tables — the recommended pivot source
Charts — plotting the data a pivot summarises
Theming — pivot styles follow the workbook theme colours

Inserting data​

Creating​

Refreshing the cache​

Adding fields​

Field options​

Filter fields with pre-selected values​

Values and totals columns​

The same field twice​

Number formats on values​

"Show values as" calculations​

Where the value headers sit​

Grand totals and subtotals​

Layout and appearance​

Copying a pivot table​

Slicers and timelines​

A worked example​

Where to next​