PivotTable Class

Namespace:

GemBox.Spreadsheet.PivotTables

Assembly:

GemBox.Spreadsheet.dll

Represents a pivot table.

• C#
• VB.NET

public sealed class PivotTable

Public NotInheritable Class PivotTable

Inheritance:

System.Object

PivotTable

Remarks

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

Source of data for Pivot Table can be:

• WorksheetSource - CellRange, NamedRange, table, etc.
• External - database, OLAP cube, textual file, web query, etc.
• Other - multiple consolidation ranges in the workbook, another Pivot Table, etc.

note

GemBox.Spreadsheet currently supports creating only WorksheetSource.

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 can be refreshed by using the Refresh() method.

PivotTable is created from a PivotCache with the specified name and the specified cell in the worksheet where it is placed using the Add(PivotCache, String, String) method. PivotTable contains the PivotFields collection which PivotField elements can be Add(Int32) 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

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.

GemBox.Spreadsheet provides functionality to calculate PivotTables by using the Calculate() method which updates the cells in the ExcelWorksheet. The Calculate() method uses values stored in PivotCache. If the cache is currently empty, or the values are outdated (because the underlying data source has changed), the cache needs to be refreshed using the Refresh() method.

Properties

BuiltInStyle

Gets or sets the built-in style used in the body of the PivotTable.

• C#
• VB.NET

public BuiltInPivotStyleName BuiltInStyle { get; set; }

Public Property BuiltInStyle As BuiltInPivotStyleName

Property Value

BuiltInPivotStyleName

The built-in style used in the body of the PivotTable.

ClassicPivotTableLayout

Gets or sets a value indicating whether the classic layout should be used for PivotTable display.

• C#
• VB.NET

public bool ClassicPivotTableLayout { get; set; }

Public Property ClassicPivotTableLayout As Boolean

Property Value

System.Boolean

true to display PivotTable with classic layout; otherwise, false.

ColumnFields

Gets the collection of all PivotFields that are currently shown as column fields.

• C#
• VB.NET

public PivotAreaFieldCollection ColumnFields { get; }

Public ReadOnly Property ColumnFields As PivotAreaFieldCollection

Property Value

PivotAreaFieldCollection

The collection of all PivotFields that are currently shown as column fields.

ColumnGrandTotals

Gets or sets a value indicating whether the PivotTable shows grand totals for columns.

• C#
• VB.NET

public bool ColumnGrandTotals { get; set; }

Public Property ColumnGrandTotals As Boolean

Property Value

System.Boolean

true to show grand totals for columns; otherwise, false.

ColumnHeaderCaption

Gets or sets the System.String to be displayed in column header in compact mode.

• C#
• VB.NET

public string ColumnHeaderCaption { get; set; }

Public Property ColumnHeaderCaption As String

Property Value

System.String

The System.String to be displayed in column header in compact mode.

DataFields

Gets the collection of all PivotFields that are currently shown as data fields.

• C#
• VB.NET

public PivotAreaFieldCollection DataFields { get; }

Public ReadOnly Property DataFields As PivotAreaFieldCollection

Property Value

PivotAreaFieldCollection

The collection of all PivotFields that are currently shown as data fields.

DataPivotField

Gets a PivotField object that represents all the data fields in this PivotTable.

It contains non-null value only if there are two or more data fields in the DataFields.

It is only used to add DataPivotField to the PivotTable row/column area.

By default, it is added in the row area (automatically, when there are two or more data fields in the DataFields).

• C#
• VB.NET

public PivotField DataPivotField { get; }

Public ReadOnly Property DataPivotField As PivotField

Property Value

PivotField

A PivotField object that represents all the data fields in this PivotTable if there are two or more data fields in the DataFields or null otherwise.

DataRange

Gets the location of the data in this PivotTable.

• C#
• VB.NET

public CellRange DataRange { get; }

Public ReadOnly Property DataRange As CellRange

Property Value

CellRange

The location of the data in this PivotTable.

Remarks

The value is set if the location was read from the file or if the PivotTable was calculated. Otherwise the value is null.

ErrorCaption

Gets or sets the System.String to be displayed in cells that contain errors.

• C#
• VB.NET

public string ErrorCaption { get; set; }

Public Property ErrorCaption As String

Property Value

System.String

The System.String to be displayed in cells that contain errors.

FieldListSortAscending

Gets or sets a value indicating whether fields in the PivotTable are sorted in non-default order in the field list.

• C#
• VB.NET

public bool FieldListSortAscending { get; set; }

Public Property FieldListSortAscending As Boolean

Property Value

System.Boolean

true if fields in the PivotTable are sorted in non-default order in the field list; otherwise, false.

Filter

Gets the pivot table PivotTableFilter.

• C#
• VB.NET

public PivotTableFilter Filter { get; }

Public ReadOnly Property Filter As PivotTableFilter

Property Value

PivotTableFilter

The pivot table PivotTableFilter.

MissingCaption

Gets or sets the System.String to be displayed in cells with no value.

• C#
• VB.NET

public string MissingCaption { get; set; }

Public Property MissingCaption As String

Property Value

System.String

The System.String to be displayed in cells with no value.

Name

Gets the name of the PivotTable.

• C#
• VB.NET

public string Name { get; set; }

Public Property Name As String

Property Value

System.String

The name of the PivotTable.

Exceptions

System.ArgumentException

Value cannot be null or System.String.Empty.

PageFields

Gets the collection of all PivotFields that are currently showing as page fields.

• C#
• VB.NET

public PivotAreaFieldCollection PageFields { get; }

Public ReadOnly Property PageFields As PivotAreaFieldCollection

Property Value

PivotAreaFieldCollection

the collection of all PivotFields that are currently showing as page fields.

PageOverThenDown

Gets or sets a value indicating how the page fields are laid out when there are multiple PivotFields in the page area.

• C#
• VB.NET

public bool PageOverThenDown { get; set; }

Public Property PageOverThenDown As Boolean

Property Value

System.Boolean

true to lay out page fields over then down; otherwise, false.

PivotCache

Gets the PivotCache associated with this PivotTable.

• C#
• VB.NET

public PivotCache PivotCache { get; }

Public ReadOnly Property PivotCache As PivotCache

Property Value

PivotCache

The PivotCache associated with this PivotTable.

PivotFields

Gets the collection of all (visible and hidden) PivotFields.

• C#
• VB.NET

public PivotFieldCollection PivotFields { get; }

Public ReadOnly Property PivotFields As PivotFieldCollection

Property Value

PivotFieldCollection

The collection of all (visible and hidden) PivotFields.

RowFields

Gets the collection of all PivotFields that are currently showing as row fields.

• C#
• VB.NET

public PivotAreaFieldCollection RowFields { get; }

Public ReadOnly Property RowFields As PivotAreaFieldCollection

Property Value

PivotAreaFieldCollection

The collection of all PivotFields that are currently showing as row fields.

RowGrandTotals

Gets or sets a value indicating whether the PivotTable shows grand totals for rows.

• C#
• VB.NET

public bool RowGrandTotals { get; set; }

Public Property RowGrandTotals As Boolean

Property Value

System.Boolean

true to show grand totals for rows; otherwise, false.

RowHeaderCaption

Gets or sets the System.String to be displayed in row header in compact mode.

• C#
• VB.NET

public string RowHeaderCaption { get; set; }

Public Property RowHeaderCaption As String

Property Value

System.String

The System.String to be displayed in row header in compact mode.

ShowDrill

Gets or sets a value indicating whether drill indicators should be shown.

• C#
• VB.NET

public bool ShowDrill { get; set; }

Public Property ShowDrill As Boolean

Property Value

System.Boolean

true to show drill indicators; otherwise, false.

StyleOptions

Gets or sets the options to be applied to the PivotTable style.

• C#
• VB.NET

public PivotTableStyleOptions StyleOptions { get; set; }

Public Property StyleOptions As PivotTableStyleOptions

Property Value

PivotTableStyleOptions

The options to be applied to the PivotTable style.

TopLeftCell

Gets the location of this PivotTable in the worksheet.

• C#
• VB.NET

public ExcelCell TopLeftCell { get; }

Public ReadOnly Property TopLeftCell As ExcelCell

Property Value

ExcelCell

The location of this PivotTable in the worksheet.

Methods

Calculate()

Calculates the values of this PivotTable.

• C#
• VB.NET

public void Calculate()

Public Sub Calculate

Remarks

If the data source or pivot table changed since it was created or loaded from a file, PivotCache needs to be refreshed.

The following features are currently not supported when calculating pivot tables:

• Calculated pivot fields and pivot items.
• Grouped fields.

Examples

Create Excel Pivot Tables in C# and VB.NET