PivotItemCollection Class

Namespace:
GemBox.Spreadsheet.PivotTables
Assembly:
GemBox.Spreadsheet.dll

Represents a collection of PivotItems.

public class PivotItemCollection : IEnumerable<PivotItem>, IEnumerable
Public Class PivotItemCollection
    Implements IEnumerable(Of PivotItem), IEnumerable
Inheritance:
System.Object
PivotItemCollection
Implements
System.Collections.Generic.IEnumerable<PivotItem>
System.Collections.IEnumerable
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:

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 PivotItems contained in the collection.

public int Count { get; }
Public ReadOnly Property Count As Integer
Property Value
System.Int32

The number of PivotItems contained in the collection.

Item[Int32]

Gets the PivotItem at the specified index.

public PivotItem this[int index] { get; }
Public ReadOnly Property Item(index As Integer) As PivotItem
Parameters
index
System.Int32

The zero-based index of the PivotItem.

Property Value
PivotItem

The PivotItem at the specified index.

Item[String]

Gets the PivotItem with the specified name.

public PivotItem this[string itemName] { get; }
Public ReadOnly Property Item(itemName As String) As PivotItem
Parameters
itemName
System.String

The name of the PivotItem.

Property Value
PivotItem

The PivotItem with the specified name.

Methods

AddCalculated(String, String)

Adds a calculated PivotItem at the end of the collection.

public PivotItem AddCalculated(string itemName, string formula)
Public Function AddCalculated(itemName As String, formula As String) As PivotItem
Parameters
itemName
System.String

The name of the calculated PivotItem.

formula
System.String

The formula of the calculated PivotItem.

Returns
PivotItem

Newly created PivotItem.

Exceptions
System.InvalidOperationException

PivotTable created from non-worksheet data source cannot be modified.

System.ArgumentException

itemName can't be null or System.String.Empty.

System.ArgumentException

formula can't be null or System.String.Empty.

System.ArgumentException

Item with the specified name already exists.

Contains(PivotItem)

Determines whether an PivotItem element is in the collection.

public bool Contains(PivotItem item)
Public Function Contains(item As PivotItem) As Boolean
Parameters
item
PivotItem

The PivotItem to locate in the collection.

Returns
System.Boolean

true if PivotItem is found in the collection; otherwise, false.

GetEnumerator()

Returns an enumerator for the collection.

public IEnumerator<PivotItem> GetEnumerator()
Public Function GetEnumerator As IEnumerator(Of PivotItem)
Returns
System.Collections.Generic.IEnumerator<PivotItem>

An enumerator for the collection.

IndexOf(PivotItem)

Searches for the specified PivotItem and returns the zero-based index of the first occurrence within the entire collection.

public int IndexOf(PivotItem item)
Public Function IndexOf(item As PivotItem) As Integer
Parameters
item
PivotItem

The PivotItem to locate in the collection.

Returns
System.Int32

The zero-based index of the first occurrence of PivotItem within the entire collection, if found; otherwise, –1.

Move(Int32, Int32)

Moves the PivotItem at the specified index to a new location in the collection.

public void Move(int oldIndex, int newIndex)
Public Sub Move(oldIndex As Integer, newIndex As Integer)
Parameters
oldIndex
System.Int32

The zero-based index specifying the location of the PivotItem to be moved.

newIndex
System.Int32

The zero-based index specifying the new location of the PivotItem.

Exceptions
System.ArgumentOutOfRangeException

oldIndex is less than 0 or is equal to or greater than item count.

System.ArgumentOutOfRangeException

newIndex is less than 0 or is equal to or greater than item count.

RemoveCalculated(String)

Removes the calculated PivotItem from the collection.

public void RemoveCalculated(string itemName)
Public Sub RemoveCalculated(itemName As String)
Parameters
itemName
System.String

The name of the calculated PivotItem.

Exceptions
System.InvalidOperationException

PivotTable created from non-worksheet data source cannot be modified.

System.ArgumentException

itemName can't be null or System.String.Empty.

System.ArgumentException

Item with the specified name doesn't exist.

System.ArgumentException

Item with the specified name is not a calculated item.

Implements

System.Collections.Generic.IEnumerable<T>
System.Collections.IEnumerable

Examples