Class ExcelPivotTable¶

Namespace: OfficeOpenXml.Table.PivotTable
Assembly: EPPlus.dll

An Excel Pivottable

public class ExcelPivotTable : XmlHelper

Inheritance¶

object ← XmlHelper ← ExcelPivotTable

Inherited Members¶

object.Equals(object?, object?)
object.Equals(object?)
object.GetHashCode()
object.GetType()
object.MemberwiseClone()
object.ReferenceEquals(object?, object?)
object.ToString()

Fields¶

PivotNullValue¶

Represents a null value in a pivot table caches shared items list.

public static PivotNull PivotNullValue

Field Value¶

PivotNull

Properties¶

Address¶

The location of the pivot table

public ExcelAddressBase Address { get; }

Property Value¶

ExcelAddressBase

ApplyBorderFormats¶

If true apply legacy table autoformat border properties

public bool ApplyBorderFormats { get; set; }

Property Value¶

bool

ApplyFontFormats¶

If true apply legacy table autoformat font properties

public bool ApplyFontFormats { get; set; }

Property Value¶

bool

ApplyNumberFormats¶

if true apply legacy table autoformat number format properties.

public bool ApplyNumberFormats { get; set; }

Property Value¶

bool

ApplyPatternFormats¶

If true apply legacy table autoformat pattern properties

public bool ApplyPatternFormats { get; set; }

Property Value¶

bool

ApplyWidthHeightFormats¶

If true apply legacy table autoformat width/height properties.

public bool ApplyWidthHeightFormats { get; set; }

Property Value¶

bool

CacheDefinition¶

Reference to the pivot table cache definition object

public ExcelPivotCacheDefinition CacheDefinition { get; }

Property Value¶

ExcelPivotCacheDefinition

CalculatedData¶

Access to the calculated data when the pivot table has been calculated. Calculate(bool) IsCalculated GetPivotData(string, IList<PivotDataFieldItemSelection>)

public ExcelPivotTableCalculatedData CalculatedData { get; }

Property Value¶

ExcelPivotTableCalculatedData

ColumnFields¶

Column label fields

public ExcelPivotTableRowColumnFieldCollection ColumnFields { get; }

Property Value¶

ExcelPivotTableRowColumnFieldCollection

ColumnGrandTotals¶

If the grand totals should be displayed for the PivotTable columns

public bool ColumnGrandTotals { get; set; }

Property Value¶

bool

ColumnHeaderCaption¶

The text to be displayed in column header in compact mode.

public string ColumnHeaderCaption { get; set; }

Property Value¶

string

Compact¶

A boolean that indicates if new fields should have their compact flag set to true

public bool Compact { get; set; }

Property Value¶

bool

CompactData¶

A boolean that indicates if the field next to the data field in the PivotTable should be displayed in the same column of the spreadsheet.

public bool CompactData { get; set; }

Property Value¶

bool

ConditionalFormattings¶

A collection of Conditional Formatting's to apply to the pivot table.

public ExcelPivotTableConditionalFormattingCollection ConditionalFormattings { get; }

Property Value¶

ExcelPivotTableConditionalFormattingCollection

DataCaption¶

Specifies the name of the value area field header in the PivotTable. This caption is shown when the PivotTable when two or more fields are in the values area.

public string DataCaption { get; set; }

Property Value¶

string

DataFields¶

Value fields

public ExcelPivotTableDataFieldCollection DataFields { get; }

Property Value¶

ExcelPivotTableDataFieldCollection

DataOnRows¶

If multiple datafields are displayed in the row area or the column area

public bool DataOnRows { get; set; }

Property Value¶

bool

EnableDrill¶

If the user is prevented from drilling down on a PivotItem or aggregate value

public bool EnableDrill { get; set; }

Property Value¶

bool

ErrorCaption¶

The string to be displayed in cells that contain errors.

public string ErrorCaption { get; set; }

Property Value¶

string

FieldPrintTitles¶

If the row and column titles from the PivotTable should be printed.

public bool FieldPrintTitles { get; set; }

Property Value¶

bool

Fields¶

The fields in the table

public ExcelPivotTableFieldCollection Fields { get; }

Property Value¶

ExcelPivotTableFieldCollection

Filters¶

Filters applied to the pivot table

public ExcelPivotTableFilterCollection Filters { get; }

Property Value¶

ExcelPivotTableFilterCollection

FirstDataCol¶

The first column of the PivotTable data, relative to the top left cell in the range.

public int FirstDataCol { get; set; }

Property Value¶

int

FirstDataRow¶

The first column of the PivotTable data, relative to the top left cell in the range

public int FirstDataRow { get; set; }

Property Value¶

int

FirstHeaderRow¶

The first row of the PivotTable header, relative to the top left cell in the ref value

public int FirstHeaderRow { get; set; }

Property Value¶

int

GrandTotalCaption¶

Specifies the string to be displayed for grand totals.

public string GrandTotalCaption { get; set; }

Property Value¶

string

GridDropZones¶

A boolean that indicates if the in-grid drop zones should be displayed at runtime, and if classic layout is applied

public bool GridDropZones { get; set; }

Property Value¶

bool

Indent¶

The indentation increment for compact axis and can be used to set the Report Layout to Compact Form

public int Indent { get; set; }

Property Value¶

int

IsCalculated¶

True if the pivot table has been calculated.

public bool IsCalculated { get; }

Property Value¶

bool

ItemPrintTitles¶

If the row and column titles from the PivotTable should be printed.

public bool ItemPrintTitles { get; set; }

Property Value¶

bool

MissingCaption¶

The text to be displayed in cells with no value

public string MissingCaption { get; set; }

Property Value¶

string

MultipleFieldFilters¶

A boolean that indicates if the fields of a PivotTable can have multiple filters set on them

public bool MultipleFieldFilters { get; set; }

Property Value¶

bool

Name¶

Name of the pivottable object in Excel

public string Name { get; set; }

Property Value¶

string

Outline¶

A boolean that indicates whether new fields should have their outline flag set to true

public bool Outline { get; set; }

Property Value¶

bool

OutlineData¶

A boolean that indicates whether data fields in the PivotTable should be displayed in outline form

public bool OutlineData { get; set; }

Property Value¶

bool

PageFields¶

Report filter fields

public ExcelPivotTableRowColumnFieldCollection PageFields { get; }

Property Value¶

ExcelPivotTableRowColumnFieldCollection

PageWrap¶

The number of page fields to display before starting another row or column

public int PageWrap { get; set; }

Property Value¶

int

PivotTableStyle¶

The pivot table style. If this property is Custom, the style from the StyleName propery is used.

public PivotTableStyles PivotTableStyle { get; set; }

Property Value¶

PivotTableStyles

PivotTableUri¶

The package internal URI to the pivottable Xml Document.

public Uri PivotTableUri { get; }

Property Value¶

Uri

PivotTableXml¶

Provides access to the XML data representing the pivottable in the package.

public XmlDocument PivotTableXml { get; }

Property Value¶

XmlDocument

PrintDrill¶

If the drill indicators expand collapse buttons should be printed.

public bool PrintDrill { get; set; }

Property Value¶

bool

RowFields¶

Row label fields

public ExcelPivotTableRowColumnFieldCollection RowFields { get; }

Property Value¶

ExcelPivotTableRowColumnFieldCollection

RowGrandTotals¶

If the grand totals should be displayed for the PivotTable rows

public bool RowGrandTotals { get; set; }

Property Value¶

bool

RowHeaderCaption¶

The text to be displayed in row header in compact mode.

public string RowHeaderCaption { get; set; }

Property Value¶

string

ShowCalcMember¶

Show the drill indicators

public bool ShowCalcMember { get; set; }

Property Value¶

bool

ShowColumnHeaders¶

Whether to show column headers for the pivot table.

public bool ShowColumnHeaders { get; set; }

Property Value¶

bool

ShowColumnStripes¶

Whether to show column stripe formatting for the pivot table.

public bool ShowColumnStripes { get; set; }

Property Value¶

bool

ShowDataTips¶

If the tooltips should be displayed for PivotTable data cells.

public bool ShowDataTips { get; set; }

Property Value¶

bool

ShowDrill¶

Show the drill down buttons

public bool ShowDrill { get; set; }

Property Value¶

bool

ShowError¶

Indicates whether to show error messages in cells.

public bool ShowError { get; set; }

Property Value¶

bool

ShowHeaders¶

Show field headers

public bool ShowHeaders { get; set; }

Property Value¶

bool

ShowLastColumn¶

Whether to show the last column for the pivot table.

public bool ShowLastColumn { get; set; }

Property Value¶

bool

ShowMemberPropertyTips¶

Show member property information

public bool ShowMemberPropertyTips { get; set; }

Property Value¶

bool

ShowRowHeaders¶

Whether to show row headers for the pivot table.

public bool ShowRowHeaders { get; set; }

Property Value¶

bool

ShowRowStripes¶

Whether to show row stripe formatting for the pivot table.

public bool ShowRowStripes { get; set; }

Property Value¶

bool

ShowValuesRow¶

If the pivot tables value row is visible or not. This property only applies when GridDropZones is set to false.

public bool ShowValuesRow { get; set; }

Property Value¶

bool

StyleName¶

Pivot style name. Used for custom styles

public string StyleName { get; set; }

Property Value¶

string

Styles¶

Individual styles for the pivot table.

public ExcelPivotTableAreaStyleCollection Styles { get; }

Property Value¶

ExcelPivotTableAreaStyleCollection

UseAutoFormatting¶

A boolean that indicates whether legacy auto formatting has been applied to the PivotTable view

public bool UseAutoFormatting { get; set; }

Property Value¶

bool

ValuesFieldPosition¶

The position of the values in the row- or column- fields list. Position is dependent on DataOnRows. If DataOnRows is true then the position is within the ColumnFields collection, a value of false the position is within the RowFields collection. A negative value or a value out of range of the add the "Σ values" field to the end of the collection.

public int ValuesFieldPosition { get; set; }

Property Value¶

int

WorkSheet¶

The worksheet where the pivottable is located

public ExcelWorksheet WorkSheet { get; set; }

Property Value¶

ExcelWorksheet

Methods¶

Calculate(bool)¶

Calculates the pivot table. Only pivot tables with workbook internal worksheet sources are calculated. Other sources are ignored and not calculated. Also see Calculate(bool) and CalculateAllPivotTables(bool)

public void Calculate(bool refreshCache = false)

Parameters¶

refreshCache bool

If the pivot cache should be refreshed from the source data, before calculating the pivot table.

Copy(ExcelRange, string)¶

Copies the pivot table to another range. The range must be within the same workbook.

public ExcelPivotTable Copy(ExcelRange destinationRange, string name)

Parameters¶

destinationRange ExcelRange

The destination range.

name string

The name of the new pivot table.

Returns¶

ExcelPivotTable

Exceptions¶

ArgumentException

Copy(ExcelRange)¶

Copies the pivot table to another range. The range must be within the same workbook.

public ExcelPivotTable Copy(ExcelRange destinationRange)

Parameters¶

destinationRange ExcelRange

The destination range.

Returns¶

ExcelPivotTable

The new copy of the ExcelPivotTable

Exceptions¶

ArgumentException

GetPivotData(string)¶

Returns the calculated grand total for the pivot table. This function works similar to the GetPivotData function used in formulas. If the pivot table is created in EPPlus without refreshing the cache, the cache will be created. Please note the any source data containing formulas must be calculated before the pivot table is calculated. Calculate(bool) IsCalculated Refresh

public object GetPivotData(string dataFieldName)

Parameters¶

dataFieldName string

The name of the data field. If a data field with the name does exist in the table, a #REF! error is returned-

Returns¶

object

The calculated value

GetPivotData(string, IList<PivotDataFieldItemSelection>)¶

Returns a calculated value for a row or column field. This function works similar to the GetPivotData function. If a row or column field is omitted, the subtotal for that field is retrieved. If the pivot table is not calculated a calculation will be performed without refreshing the pivot cache. If the pivot table is created in EPPlus without refreshing the cache, the cache will be created. Please note the any source data containing formulas must be calculated before the pivot table is calculated. Calculate(bool) IsCalculated Refresh

public object GetPivotData(string dataFieldName, IList<PivotDataFieldItemSelection> fieldItemSelection)

Parameters¶

dataFieldName string

The name of the data field. If a data field with the name does exist in the table, a #REF! error is returned-

fieldItemSelection IList<PivotDataFieldItemSelection>

A list of criterias to determin which value to retrieve. If the fieldItemSelection does not exist in the pivot tabvle a #REF! error is returned.

Returns¶

object

The calculated value

SetCompact(bool)¶

Sets all pivot table fields Compact property to the value supplied.

public void SetCompact(bool value = true)

Parameters¶

value bool

The the value for the Compact property.