PivotTableCollection Class
Represents a collection of all PivotTable objects in the worksheet.
public sealed class PivotTableCollection : IEnumerable<PivotTable>, IEnumerable
Public NotInheritable Class PivotTableCollection
Implements IEnumerable(Of PivotTable), IEnumerable
- Inheritance:
- System.ObjectPivotTableCollection
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
Count
Gets the number of PivotTables contained in the collection.
Property Value
- System.Int32
The number of PivotTables contained in the collection.
Item[Int32]
Gets the PivotTable at the specified index.
public PivotTable this[int index] { get; }
Public ReadOnly Property Item(index As Integer) As PivotTable
Parameters
index
- System.Int32
The zero-based index of the PivotTable.
Property Value
The PivotTable at the specified index.
Methods
Add(PivotCache, String, ExcelCell)
Adds a new PivotTable to the collection.
public PivotTable Add(PivotCache pivotCache, string tableName, ExcelCell topLeftCell)
Public Function Add(pivotCache As PivotCache, tableName As String, topLeftCell As ExcelCell) As PivotTable
Parameters
pivotCache
- PivotCache
The PivotCache on which the new PivotTable is based.
tableName
- System.String
The name of the new PivotTable.
topLeftCell
- ExcelCell
The location where PivotTable will be placed in the sheet.
Returns
Newly created 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.
Exceptions
- System.ArgumentNullException
pivotCache
can't be null.
- System.ArgumentException
pivotCache
must belong to the parent workbook.
- System.ArgumentException
tableName
can't be null or System.String.Empty.
- System.ArgumentNullException
topLeftCell
can't be null.
- System.ArgumentException
topLeftCell
must belong to the parent sheet.
Add(PivotCache, String, String)
Adds a new PivotTable to the collection.
public PivotTable Add(PivotCache pivotCache, string tableName, string cellReference)
Public Function Add(pivotCache As PivotCache, tableName As String, cellReference As String) As PivotTable
Parameters
pivotCache
- PivotCache
The PivotCache on which the new PivotTable is based.
tableName
- System.String
The name of the new PivotTable.
cellReference
- System.String
The location where PivotTable will be placed in the sheet.
Returns
Newly created 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.
Exceptions
- System.ArgumentNullException
pivotCache
can't be null.
- System.ArgumentException
pivotCache
must belong to the parent workbook.
- System.ArgumentException
tableName
can't be null or System.String.Empty.
- System.ArgumentException
cellReference
can't be null or System.String.Empty.
Clear()
Removes all PivotTables from the collection.
GetEnumerator()
Returns an enumerator for the collection.
public IEnumerator<PivotTable> GetEnumerator()
Public Function GetEnumerator As IEnumerator(Of PivotTable)
Returns
- System.Collections.Generic.IEnumerator<PivotTable>
An enumerator for the collection.
RemoveAt(Int32)
Removes the PivotTable at the specified index.
Parameters
index
- System.Int32
The zero-based index of the PivotTable.
Exceptions
- System.ArgumentOutOfRangeException
index
is less than 0 or is equal to or greater than Count.