Click or drag to resize

ExcelFilePivotCaches Property

Gets the collection of PivotCaches defined in this ExcelFile instance.

Currently supported in XLSX only.

Namespace:  GemBox.Spreadsheet
Assembly:  GemBox.Spreadsheet (in GemBox.Spreadsheet.dll) Version: 41.3.30.1122
Syntax
public PivotCacheCollection PivotCaches { get; }

Property Value

Type: PivotCacheCollection
The collection of PivotCaches defined in this ExcelFile instance.
Remarks

Pivot Tables are used to arrange and aggregate complicated data and drill down on details.

Source of data for Pivot Table can be:

  • Worksheet - cell range, named range, table, etc.
  • External - database, OLAP cube, textual file, web query, etc.
  • Other - multiple consolidation ranges in the workbook, another Pivot Table, etc.
Note Note

GemBox.Spreadsheet currently supports creating only worksheet source.

All other sources are supported through preservation (to correctly round-trip to XLSX format).

Source of the data for Pivot Table contains following information:

  • Scheme - data source fields/columns, its data types, etc.
  • Data - actual data source records.

Pivot Table does not directly reference its data source, but instead it references a cache of a data source. Cache of a data source contains an in-memory copy of data source scheme and, optionally, data and is stored in a workbook which may contain multiple caches of various sources of data.

Cache can be updated/refreshed from a data source automatically (for example, when opening a workbook) or on request. Cache is used to provide better performance of Pivot Table functionality which requires frequent access to data records and their values and to enable sharing the same data source between multiple Pivot Tables.

GemBox.Spreadsheet represents cache with a PivotCache type. Cache source is represented with a PivotCacheSource type (and its sub-types) and can be accessed from a CacheSource property. PivotCache instances are stored in a PivotCaches collection. PivotCache currently doesn't store the actual cached records (it stores only scheme - fields and their properties), but instead Microsoft Excel is instructed to update the record cache from the data source when opening a workbook.

PivotTable is created from a PivotCache with the specified name and the specified cell in the worksheet where it is placed using the Add Overload method. PivotTable contains the PivotFields collection which PivotField elements can be moved into the following Pivot Table areas:

  • Report Filter Area represented by PageFields collection.
  • Row Axis represented by RowFields collection.
  • Column Axis represented by ColumnFields collection.
  • Values Area represented by DataFields collection.

Each (non-calculated) PivotField can contain zero or more PivotItems which are used for filtering and grouping by a specific value.

Except using fields defined in Pivot Table data source (more precisely, in PivotCache) which cannot be removed, Pivot Table can also define calculated PivotFields and calculated PivotItems which use a formula to specify their values. Calculated PivotField can be added using a AddCalculated(String, String) method and removed using a RemoveCalculated(String) method. Property IsCalculated tells if a field is calculated or is a field from the data source. Calculated PivotField is automatically moved to a Values Area (DataFields collection) and only Values Area can contain calculated PivotFields. Calculated PivotItem can be added using a AddCalculated(String, String) method and removed using a RemoveCalculated(String) method. Property IsCalculated tells if an item is calculated.

Note Note

If calculated PivotField is added/removed in one PivotTable, then it is also added/removed from all other PivotTables which reference the same PivotCache.

Since adding/removing calculated PivotFields means changing the data scheme, which is defined in PivotCache, actual modification is made in PivotCache and propagated to all PivotTables which are referencing it.

PivotFields can be re-ordered in a specific area using the Move(Int32, Int32) method. PivotItems can be re-ordered using the Move(Int32, Int32) method.

Note Note

GemBox.Spreadsheet currently does not provide functionality to read/calculate the actual data from the PivotTable.

It provides the functionality to arrange and customize PivotFields, PivotItems and PivotTable appearance.

Because of that PivotTable data is not rendered/written to other output formats like PDF, HTML, etc. When output XLSX file with Pivot Table is opened in Microsoft Excel application, its data is automatically retrieved and calculated from the data source by Microsoft Excel.

Examples

Following code demonstrates how to create and customize PivotTable using GemBox.Spreadsheet component to get the following information out of the sample records:

  • Relative difference of average salary between GemBox and other companies grouped over departments (and genders).
  • Relative difference of max salary between GemBox and other companies grouped over departments (and genders).

This information might help you decide if working for GemBox is worth it if you are a man or a woman and in which department would it be the best to work (considering department salaries of other companies).

Input workbook can be downloaded from PivotTableData.xlsx.

// Load workbook from XLSX file on Desktop.
var workbook = ExcelFile.Load(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "PivotTableData.xlsx"));

// Create pivot cache from named range 'Sheet1!PivotDataRange'.
var pivotCache = workbook.PivotCaches.AddWorksheetSource("Sheet1!PivotDataRange");

// Create pivot table using the specified pivot cache, name it 'PivotTable1' and add it to the 'Sheet1' worksheet at the cell location 'G2'.
var pivotTable = workbook.Worksheets["Sheet1"].PivotTables.Add(pivotCache, "PivotTable1", "G2");

// Aggregate 'Salary' values into average value and show it as a percentage of difference from average value for 'GemBox' company.
var field = pivotTable.DataFields.Add("Salary");
field.Function = PivotFieldCalculationType.Average;
field.NumberFormat = "[$$-409]#,##0.00";
field.ShowDataAs = PivotFieldDisplayFormat.PercentageOfDifference;
field.BaseField = pivotTable.PivotFields["Company"];
field.BaseItem = field.BaseField.PivotItems["GemBox"];

// Aggregate 'Salary' values into max value and show it as a percentage of difference from max value for 'GemBox' company.
field = pivotTable.DataFields.Add("Salary");
field.Function = PivotFieldCalculationType.Max;
field.NumberFormat = "[$$-409]#,##0.00";
field.ShowDataAs = PivotFieldDisplayFormat.PercentageOfDifference;
field.BaseField = pivotTable.PivotFields["Company"];
field.BaseItem = field.BaseField.PivotItems["GemBox"];

// Group rows first into 'Departments' and then into 'Companies'.
pivotTable.RowFields.Add("Department");
pivotTable.RowFields.Add("Company");

// Group columns first into 'Data' (average 'Salary' and max 'Salary) and then into 'Genders'.
pivotTable.ColumnFields.Add(pivotTable.DataPivotField);
pivotTable.ColumnFields.Add("Gender");

// Do not show grand totals for columns because we are only interested in a relative difference between 'GemBox' and other companies.
pivotTable.ColumnGrandTotals = false;

// Style pivot table.
pivotTable.BuiltInStyle = BuiltInPivotStyleName.PivotStyleLight16;

// Save workbook to XLSX file on Desktop.
workbook.Save(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "PivotTable.xlsx"));
See Also