PivotField Class

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

Represents a field within a PivotTable.

public sealed class PivotField
Public NotInheritable Class PivotField
Inheritance:
System.Object
PivotField
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

AllDrilled

Specifies a boolean value that indicates whether all items in the field are expanded. Applies only to OLAP PivotTables.

public bool AllDrilled { get; set; }
Public Property AllDrilled As Boolean
Property Value
System.Boolean

true indicates all items in the field are expanded.

false indicates all items are not expanded. However some items might be expanded.

BaseField

Gets or sets the base field used for a custom calculation.

This property is applicable only for data fields.

public PivotField BaseField { get; set; }
Public Property BaseField As PivotField
Property Value
PivotField

The base field used for a custom calculation.

Exceptions
System.ArgumentNullException

Value can't be null.

System.ArgumentException

Base field is not part of the parent PivotTable.

BaseItem

Gets or sets the item in the BaseField used for a custom calculation.

This property is applicable only for data fields.

Use BaseItemPosition property to specify Previous or Next item, otherwise use this property.

public PivotItem BaseItem { get; set; }
Public Property BaseItem As PivotItem
Property Value
PivotItem

The item in the BaseField used for a custom calculation.

Exceptions
System.ArgumentNullException

Value can't be null.

System.ArgumentException

BaseField is null.

System.ArgumentException

Base item is not part of the BaseField.

BaseItemPosition

Gets or sets the item position in the BaseField used for a custom calculation.

This property is applicable only for data fields.

Use this property to specify Previous or Next item, otherwise use BaseItem property.

public PivotItemPosition BaseItemPosition { get; set; }
Public Property BaseItemPosition As PivotItemPosition
Property Value
PivotItemPosition

The item position in the BaseField used for a custom calculation.

Exceptions
System.ArgumentException

BaseField is null.

CurrentPageItem

Gets or sets the current page item showing for the page field.

This property is applicable only for page fields.

If set to null, no current page item will be defined.

public PivotItem CurrentPageItem { get; set; }
Public Property CurrentPageItem As PivotItem
Property Value
PivotItem

The current page item showing for the page field.

Exceptions
System.ArgumentException

Page item is not part of this page field.

DragToColumn

Gets or sets a value indicating whether this field can be dragged to the column position.

public bool DragToColumn { get; set; }
Public Property DragToColumn As Boolean
Property Value
System.Boolean

true if this field can be dragged to the column position; otherwise, false.

DragToData

Gets or sets a value indicating whether this field can be dragged to the data position.

public bool DragToData { get; set; }
Public Property DragToData As Boolean
Property Value
System.Boolean

true if this field can be dragged to the data position; otherwise, false.

DragToHide

Gets or sets a value indicating whether this field can be dragged to the hide position.

public bool DragToHide { get; set; }
Public Property DragToHide As Boolean
Property Value
System.Boolean

true if this field can be dragged to the hide position; otherwise, false.

DragToPage

Gets or sets a value indicating whether this field can be dragged to the page position.

public bool DragToPage { get; set; }
Public Property DragToPage As Boolean
Property Value
System.Boolean

true if this field can be dragged to the page position; otherwise, false.

DragToRow

Gets or sets a value indicating whether this field can be dragged to the row position.

public bool DragToRow { get; set; }
Public Property DragToRow As Boolean
Property Value
System.Boolean

true if this field can be dragged to the row position; otherwise, false.

EnableMultiplePageItems

Gets or sets a value indicating whether this field can have multiple items selected in the page field.

public bool EnableMultiplePageItems { get; set; }
Public Property EnableMultiplePageItems As Boolean
Property Value
System.Boolean

true if this field can have multiple items selected in the page field; otherwise, false.

Formula

Gets the PivotField's formula in A1-style notation.

public string Formula { get; }
Public ReadOnly Property Formula As String
Property Value
System.String

The PivotField's formula in A1-style notation.

Function

Gets or sets the function used to summarize/aggregate this PivotField.

This property is applicable only for data fields.

public PivotFieldCalculationType Function { get; set; }
Public Property Function As PivotFieldCalculationType
Property Value
PivotFieldCalculationType

The function used to summarize/aggregate this PivotField.

InsertBlankRow

Gets or sets a value indicating whether to insert a blank row after each item.

public bool InsertBlankRow { get; set; }
Public Property InsertBlankRow As Boolean
Property Value
System.Boolean

true if a blank row will be inserted after each item; otherwise, false.

InsertPageBreak

Gets or sets a value indicating whether to insert a page break after each item.

public bool InsertPageBreak { get; set; }
Public Property InsertPageBreak As Boolean
Property Value
System.Boolean

true if a page break will be inserted after each item; otherwise, false.

IsCalculated

Gets a value indicating whether this PivotField is a calculated field.

public bool IsCalculated { get; }
Public ReadOnly Property IsCalculated As Boolean
Property Value
System.Boolean

true if this PivotField is a calculated field; otherwise, false.

LayoutForm

Gets or sets the layout form for this PivotField.

public PivotFieldLayoutForm LayoutForm { get; set; }
Public Property LayoutForm As PivotFieldLayoutForm
Property Value
PivotFieldLayoutForm

The layout form for this PivotField.

Name

Gets or sets the custom name (label text) of this PivotField.

public string Name { get; set; }
Public Property Name As String
Property Value
System.String

The custom name (label text) of this PivotField.

Exceptions
System.ArgumentException

Value cannot be null or System.String.Empty.

NumberFormat

Gets or sets the number format which indicates how to format the numeric value of this field.

Default value is General.

If set to null, number format will be set to default.

public string NumberFormat { get; set; }
Public Property NumberFormat As String
Property Value
System.String

The number format which indicates how to format the numeric value of this field.

PivotItems

Gets the collection of all (visible and hidden) PivotItems in this PivotField.

public PivotItemCollection PivotItems { get; }
Public ReadOnly Property PivotItems As PivotItemCollection
Property Value
PivotItemCollection

The collection of all (visible and hidden) PivotItems in this PivotField.

PivotTable

Gets the parent PivotTable.

public PivotTable PivotTable { get; }
Public ReadOnly Property PivotTable As PivotTable
Property Value
PivotTable

The parent PivotTable.

RepeatItemLabels

Gets or sets a value indicating whether to repeat item labels.

public bool RepeatItemLabels { get; set; }
Public Property RepeatItemLabels As Boolean
Property Value
System.Boolean

true to repeat item labels for each nested item; otherwise, false.

ShowAll

Gets or sets a value indicating whether to show all items for this field.

public bool ShowAll { get; set; }
Public Property ShowAll As Boolean
Property Value
System.Boolean

true to show all items for this field; otherwise, false.

ShowDataAs

Gets or sets the display format for this PivotField.

This property is applicable only for data fields.

public PivotFieldDisplayFormat ShowDataAs { get; set; }
Public Property ShowDataAs As PivotFieldDisplayFormat
Property Value
PivotFieldDisplayFormat

The display format for this PivotField.

SortOptions

Gets the sort options of this pivot field.

public PivotTableSortOptions SortOptions { get; }
Public ReadOnly Property SortOptions As PivotTableSortOptions
Property Value
PivotTableSortOptions

SourceName

Gets the source name (cache field name) of this PivotField.

public string SourceName { get; }
Public ReadOnly Property SourceName As String
Property Value
System.String

The source name (cache field name) of this PivotField.

Subtotals

Gets or sets the subtotals displayed with this PivotField.

This property is applicable only for non-data fields.

public PivotFieldSubtotalTypes Subtotals { get; set; }
Public Property Subtotals As PivotFieldSubtotalTypes
Property Value
PivotFieldSubtotalTypes

The subtotals displayed with this PivotField.

Methods

ClearSort()

Removes the sort options from this pivot field.

public void ClearSort()
Public Sub ClearSort

Collapse(Boolean)

Collapse/expand entire PivotField.

public void Collapse(bool collapsed)
Public Sub Collapse(collapsed As Boolean)
Parameters
collapsed
System.Boolean

true if entire PivotField is collapsed; otherwise, false.

Sort(Boolean)

Sorts this pivot field by the pivot items in it.

public void Sort(bool descending)
Public Sub Sort(descending As Boolean)
Parameters
descending
System.Boolean

True to sort in descending order, false otherwise.

Sort(Boolean, PivotField, PivotItem[])

Sorts this pivot field by the data field.

Pivot items can be used to specify the row (or column) that will be used to sort the pivot field.

public void Sort(bool descending, PivotField sortByDataField, params PivotItem[] sortByPivotItems)
Public Sub Sort(descending As Boolean, sortByDataField As PivotField, ParamArray sortByPivotItems As PivotItem())
Parameters
descending
System.Boolean

True to sort in descending order, false otherwise.

sortByDataField
PivotField

Data field that is used to sort this pivot field.

sortByPivotItems
PivotItem[]

Pivot items that specify the row (or column) that will be used to sort the pivot field.

Examples