GrapeCity.Documents.Excel.IPivotTable

IPivotTable Interface

Represents a PivotTable report on a worksheet.

An IPivotTable is a member of the IPivotTables collection and is used to summarize and analyze data from a IPivotCache on a worksheet.

Namespace: GrapeCity.Documents.Excel

Assembly: DS.Documents.Excel.dll

Syntax

public interface IPivotTable

Public Interface IPivotTable

Examples

worksheet.Range["A1:B4"].Value = new object[,] {
    {"Name", "Value"},
    {"A", 100},
    {"B", 200},
    {"A", 100}
};
IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:B4"]);
IPivotTable pivotTable = worksheet.PivotTables.Add(pivotCache, worksheet.Range["D1"], "PivotTable1");
pivotTable.Name = "SalesPivot";

Properties

Name	Description
AllowMultipleFilters	Gets or sets whether pivot fields in the pivot table can have multiple filters applied at the same time. Use this property to determine whether the pivot table allows a single pivot field to keep more than one filter condition simultaneously.
CalculatedFields	Gets the collection of calculated fields in this PivotTable report. Use the returned ICalculatedFields collection to access or add calculated fields for the current PivotTable.
ColumnFields	Gets the collection of fields that are currently shown as column fields in the PivotTable report. The returned collection is read-only and contains the fields whose orientation is set to column fields.
ColumnGrand	Gets or sets whether the PivotTable report shows grand totals for columns. This property indicates whether a grand total is displayed for the column area of the PivotTable report. The default value is `true`.
ColumnRange	Gets the range that contains the column area in the PivotTable report. The returned range covers the cells used for column fields in the current PivotTable layout.
CompactRowIndent	Gets or sets the indent increment for PivotItems when compact row layout is enabled. This value controls how much each nested PivotItem is indented when the PivotTable uses compact row layout.
DataBodyRange	Gets the range that contains the data area of the PivotTable report. The returned range represents the cells between the header area and the insert row.
DataFields	Gets the collection of fields that are currently shown as data fields in the PivotTable report. The returned collection is read-only and contains the fields placed in the Values area of the PivotTable.
DeferLayoutUpdate	Gets or sets whether layout updates for the PivotTable are deferred. When this property is `true`, layout changes can be accumulated and then applied by calling Update().
DisplayContextTooltips	Gets or sets whether tooltips are displayed for PivotTable cells. This property indicates whether context tooltips are shown for cells in the PivotTable report.
DisplayErrorString	Gets or sets whether the PivotTable report displays a custom error string in cells that contain errors. When this property is `true`, the PivotTable uses the string specified by ErrorString for cells that contain errors. The default value is `false`.
DisplayFieldCaptions	Gets or sets a value indicating whether filter buttons and pivot field captions for rows and columns are displayed in the grid. Default value is true.
DisplayMemberPropertyTooltips	Gets or sets whether member properties are displayed in PivotTable tooltips. When this property is `true`, tooltips can include member property information for PivotTable items.
DisplayNullString	Gets or sets whether the PivotTable report displays a custom string in cells that contain null values. When this property is `true`, the PivotTable uses the string specified by NullString for cells that contain null values. The default value is `true`.
ErrorString	Gets or sets the string displayed in cells that contain errors when DisplayErrorString is `true`. The default value is an empty string (`""`). Use ErrorString to specify the custom error text shown by the PivotTable for error cells.
GrandTotalName	Gets or sets the text label displayed in the grand total row heading or grand total column heading of the PivotTable report. The default label is `"Grand Total"`.
HasAutoFormat	Gets or sets a value indicating whether the PivotTable report automatically fits column widths when it is refreshed. The default value is `true`.
LayoutRowDefault	Gets the default row layout type that is applied when PivotFields are added to the PivotTable for the first time. This setting determines the initial row layout used for newly added PivotFields, such as compact, outline, or tabular layout.
MergeLabels	Gets or sets whether the specified PivotTable report uses merged cells for outer-row items, column items, subtotals, and grand total labels. Use this property to determine whether repeated labels in the PivotTable layout are displayed by merging adjacent cells.
Name	Gets or sets the name of the PivotTable. The name identifies the PivotTable on the worksheet and can be used to reference the PivotTable programmatically.
NullString	Gets or sets the string displayed in PivotTable cells that contain null values when DisplayNullString is `true`. The default value is an empty string (`""`).
PageFieldOrder	Gets or sets the order in which page fields are added to the PivotTable report layout. The returned value is one of the Order constants: DownThenOver or OverThenDown. The default value is DownThenOver.
PageFieldWrapCount	Gets or sets the number of page fields displayed in each column or row of the PivotTable report. This value determines how page fields are wrapped in the report filter area when the PivotTable contains multiple page fields. Use PageFieldWrapCount to change the layout.
PageFields	Gets the collection of fields that are currently displayed as page fields in the PivotTable. The returned collection is read-only and contains the fields whose orientation is set to the page area of the PivotTable report.
PageRange	Gets the range that contains the page area in the PivotTable report. The page area is the report filter area that displays fields whose orientation is set to page fields. This property returns `null` if the PivotTable does not currently have a page area.
PivotCache	Gets the IPivotCache used by this PivotTable report. The returned cache represents the source data cache associated with the PivotTable.
PivotColumnAxis	Gets the IPivotAxis object that represents the entire column axis of the PivotTable report. Use this axis to work with the column-side structure of a PivotTable, such as the pivot lines displayed across the top of the report.
PivotFields	Gets the collection of PivotTable fields in the PivotTable report. The returned IPivotFields collection includes both visible and hidden fields from the PivotTable data source. You can use this collection to access individual fields and configure how they are used in the report layout.
PivotFormulas	Gets the collection of formulas for the specified PivotTable report. Use this property to access the IPivotFormulas collection that contains the custom formulas defined for the PivotTable, such as formulas created by calculated items.
PivotRowAxis	Gets the IPivotAxis object that represents the entire row axis of the PivotTable report. Use this axis to work with the row-side structure of a PivotTable, such as the pivot lines displayed down the left side of the report.
RowFields	Gets the collection of fields that are currently shown as row fields in the PivotTable report. The returned IPivotFields collection is read-only and contains the fields whose orientation is set to row fields.
RowGrand	Gets or sets whether the PivotTable report shows grand totals for rows. Use this property to determine whether the PivotTable displays a grand total row for the row fields.
RowRange	Gets the range that includes the row area in the PivotTable report. The returned range represents the cells used by row fields and row items in the current PivotTable layout.
ShowTableStyleColumnHeaders	Gets or sets whether column headers are displayed in the PivotTable. This property indicates whether the PivotTable shows its column header area using the current table style settings.
ShowTableStyleColumnStripes	Gets or sets whether banded columns are displayed in the PivotTable style. When this property is `true`, even columns are formatted differently from odd columns to make the PivotTable easier to read.
ShowTableStyleLastColumn	Gets or sets whether the last column style is displayed in the PivotTable. This property indicates whether the PivotTable style is applied to the last column of the PivotTable report.
ShowTableStyleRowHeaders	Gets or sets whether row headers are displayed in the PivotTable style. Use this property to determine whether the current PivotTable style shows row header formatting.
ShowTableStyleRowStripes	Gets or sets whether banded rows are displayed in the PivotTable so that even rows are formatted differently from odd rows. When this property is `true`, the PivotTable style applies alternating row formatting to improve readability.
Style	Gets or sets the style applied to the pivot table. This property returns the current ITableStyle associated with the pivot table. If no style has been applied, this property returns `null`.
TableRange1	Gets the range that contains the entire PivotTable report, excluding page fields. Use this property to access the rendered PivotTable area without including any page field region that is displayed separately from the main report.
TableRange2	Gets the range that contains the entire PivotTable report, including page fields. Use this property to retrieve the full displayed PivotTable area, including filter areas, rather than only the main report body.
TableStyle	Gets or sets the style used in the body of the PivotTable report. The default value is a null string, which indicates that no style is applied.
Tag	Gets or sets the custom text metadata associated with the PivotTable report. The value is saved with the PivotTable report.

Methods

Name	Description
AddDataField(IPivotField, string, ConsolidationFunction)	Adds a data field to the PivotTable report. Use this method to add a source field to the values area and specify the summary function used for that data field.
ClearAllFilters()	Deletes all filters currently applied to the pivot table. This method removes filters in the pivot table's filter collection, clears manual filtering, and resets all pivot fields in the Report Filter area to their default item.
ClearTable()	Clears the PivotTable. Clearing a PivotTable removes all fields and deletes any filtering and sorting applied to the report. This method resets the PivotTable to the state it had immediately after creation, before any fields were added.
PivotValueCell(int, int)	Retrieves the IPivotValueCell at the specified row and column position in the PivotTable data area. Use this method to access a calculated value cell in a PivotTable report and then inspect its value or related IPivotCell information.
Refresh()	Refreshes the PivotTable report from its source data. Use this method after the source data has changed and you need the PivotTable to reflect the latest data. This method performs the following actions: Refreshes the underlying `PivotCache`. Synchronizes `PivotFields` with the refreshed cache. Recalculates and updates the PivotTable report on the worksheet. If only the PivotTable layout or display settings have changed and the source data has not changed, use Update() instead.
RepeatAllLabels(PivotFieldRepeatLabels)	Sets whether to repeat item labels for all pivot fields in this pivot table. This setting is applied to all existing pivot fields in the pivot table.
SetRowAxisLayout(LayoutRowType)	This method is used for simultaneously setting layout options for all existing PivotFields.
SubtotalLocation(SubtotalLocationType)	Changes the subtotal location for all existing pivot fields. Changing the subtotal location has an immediate visual effect only for fields in outline form, but the setting is also applied to fields in tabular form. If the row axis layout is TabularRow, the `location` must be Bottom.
Update()	Updates the PivotTable report layout and displayed results. A common use case is to enable DeferLayoutUpdate, make multiple layout changes, and then call this method once to apply them. This method does not refresh the PivotCache from the source data. If the source data has changed and the PivotCache must be refreshed first, call Refresh() instead.