PivotAreaFieldCollection Class

Namespace:

GemBox.Spreadsheet.PivotTables

Assembly:

GemBox.Spreadsheet.dll

Represents a collection of visible (row, column, page or data) PivotFields.

• C#
• VB.NET

public sealed class PivotAreaFieldCollection : PivotFieldBaseCollection, IEnumerable<PivotField>, IEnumerable

Public NotInheritable Class PivotAreaFieldCollection
    Inherits PivotFieldBaseCollection
    Implements IEnumerable(Of PivotField), IEnumerable

Inheritance:

System.Object

PivotFieldBaseCollection

PivotAreaFieldCollection

Implements

System.Collections.Generic.IEnumerable<PivotField>

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:

• 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.

Methods

Add(PivotField)

Adds the PivotField to this area collection.

• C#
• VB.NET

public PivotField Add(PivotField pivotField)

Public Function Add(pivotField As PivotField) As PivotField

Parameters

pivotField

PivotField

The PivotField in the PivotFields, RowFields, ColumnFields, PageFields or DataFields collection.

Returns

PivotField

The PivotField added to this area collection.

Exceptions

System.InvalidOperationException

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

System.ArgumentNullException

pivotField can't be null.

System.ArgumentException

Field is not part of the parent PivotTable.

Add(Int32)

Adds the PivotField to this area collection.

• C#
• VB.NET

public PivotField Add(int fieldIndex)

Public Function Add(fieldIndex As Integer) As PivotField

Parameters

fieldIndex

System.Int32

The PivotField index in the PivotFields collection.

Returns

PivotField

The PivotField added to this area collection.

Exceptions

System.InvalidOperationException

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

System.ArgumentOutOfRangeException

fieldIndex is less than 0 or is equal to or greater than PivotFields count.

Add(String)

Adds the PivotField to this area collection.

• C#
• VB.NET

public PivotField Add(string fieldName)

Public Function Add(fieldName As String) As PivotField

Parameters

fieldName

System.String

The PivotField name in the PivotFields collection.

Returns

PivotField

The PivotField added to this area collection.

Exceptions

System.InvalidOperationException

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

System.ArgumentException

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

System.ArgumentException

Field with specified name doesn't exist.

Clear()

Removes all PivotFields from this area collection.

• C#
• VB.NET

public void Clear()

Public Sub Clear

Exceptions

System.InvalidOperationException

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

Move(Int32, Int32)

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

• C#
• VB.NET

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 PivotField to be moved.

newIndex

System.Int32

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

Exceptions

System.ArgumentOutOfRangeException

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

System.ArgumentOutOfRangeException

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

System.InvalidOperationException

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

Remove(PivotField)

Removes PivotField from this area collection.

• C#
• VB.NET

public void Remove(PivotField pivotField)

Public Sub Remove(pivotField As PivotField)

Parameters

pivotField

PivotField

The PivotField in this area collection.

Exceptions

System.InvalidOperationException

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

System.ArgumentNullException

pivotField can't be null.

System.ArgumentException

Field is not part of this pivot area.

Remove(Int32)

Removes PivotField at the specified index from this area collection.

• C#
• VB.NET

public void Remove(int fieldIndex)

Public Sub Remove(fieldIndex As Integer)

Parameters

fieldIndex

System.Int32

The PivotField index in this area collection.

Exceptions

System.InvalidOperationException

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

System.ArgumentOutOfRangeException

fieldIndex is less than 0 or is equal to or greater than field count.

Remove(String)

Removes PivotField with the specified name from this area collection.

• C#
• VB.NET

public void Remove(string fieldName)

Public Sub Remove(fieldName As String)

Parameters

fieldName

System.String

The PivotField name.

Exceptions

System.InvalidOperationException

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

System.ArgumentException

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

System.ArgumentException

Field with specified name doesn't exist.

Inherited Properties

Count	Gets the number of PivotFields contained in the collection. (Inherited from PivotFieldBaseCollection)
Item[System.Int32]	Gets the PivotField at the specified index. (Inherited from PivotFieldBaseCollection)
Item[System.String]	Gets the PivotField with the specified name. (Inherited from PivotFieldBaseCollection)

Inherited Methods

Contains(PivotField)	Determines whether the PivotField is contained in the collection. (Inherited from PivotFieldBaseCollection)
GetEnumerator()	Returns an enumerator for the collection. (Inherited from PivotFieldBaseCollection)
IndexOf(PivotField)	Searches for the specified PivotField and returns the zero-based index of the first occurrence within the entire collection. (Inherited from PivotFieldBaseCollection)

Implements

System.Collections.Generic.IEnumerable<T>

System.Collections.IEnumerable

Examples

Create Excel Pivot Tables in C# and VB.NET