Class ExcelPivotTable

An Excel Pivottable

Inheritance

System.Object

XmlHelper

ExcelPivotTable

Inherited Members

System.Object.Equals(System.Object)

System.Object.Equals(System.Object, System.Object)

System.Object.GetHashCode()

System.Object.GetType()

System.Object.MemberwiseClone()

System.Object.ReferenceEquals(System.Object, System.Object)

System.Object.ToString()

Namespace: OfficeOpenXml.Table.PivotTable

Assembly: EPPlus.dll

Syntax

public class ExcelPivotTable : XmlHelper

Fields

PivotNullValue

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

Declaration

public static PivotNull PivotNullValue

Field Value

Type	Description
PivotNull

Properties

Address

The location of the pivot table

Declaration

public ExcelAddressBase Address { get; }

Property Value

Type	Description
ExcelAddressBase

ApplyBorderFormats

If true apply legacy table autoformat border properties

Declaration

public bool ApplyBorderFormats { get; set; }

Property Value

Type	Description
System.Boolean

ApplyFontFormats

If true apply legacy table autoformat font properties

Declaration

public bool ApplyFontFormats { get; set; }

Property Value

Type	Description
System.Boolean

ApplyNumberFormats

if true apply legacy table autoformat number format properties.

Declaration

public bool ApplyNumberFormats { get; set; }

Property Value

Type	Description
System.Boolean

ApplyPatternFormats

If true apply legacy table autoformat pattern properties

Declaration

public bool ApplyPatternFormats { get; set; }

Property Value

Type	Description
System.Boolean

ApplyWidthHeightFormats

If true apply legacy table autoformat width/height properties.

Declaration

public bool ApplyWidthHeightFormats { get; set; }

Property Value

Type	Description
System.Boolean

CacheDefinition

Reference to the pivot table cache definition object

Declaration

public ExcelPivotCacheDefinition CacheDefinition { get; }

Property Value

Type	Description
ExcelPivotCacheDefinition

CalculatedData

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

Declaration

public ExcelPivotTableCalculatedData CalculatedData { get; }

Property Value

Type	Description
ExcelPivotTableCalculatedData

ColumnFields

Column label fields

Declaration

public ExcelPivotTableRowColumnFieldCollection ColumnFields { get; }

Property Value

Type	Description
ExcelPivotTableRowColumnFieldCollection

ColumnGrandTotals

If the grand totals should be displayed for the PivotTable columns

Declaration

public bool ColumnGrandTotals { get; set; }

Property Value

Type	Description
System.Boolean

ColumnHeaderCaption

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

Declaration

public string ColumnHeaderCaption { get; set; }

Property Value

Type	Description
System.String

Compact

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

Declaration

public bool Compact { get; set; }

Property Value

Type	Description
System.Boolean

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.

Declaration

public bool CompactData { get; set; }

Property Value

Type	Description
System.Boolean

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.

Declaration

public string DataCaption { get; set; }

Property Value

Type	Description
System.String

DataFields

Value fields

Declaration

public ExcelPivotTableDataFieldCollection DataFields { get; }

Property Value

Type	Description
ExcelPivotTableDataFieldCollection

DataOnRows

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

Declaration

public bool DataOnRows { get; set; }

Property Value

Type	Description
System.Boolean

EnableDrill

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

Declaration

public bool EnableDrill { get; set; }

Property Value

Type	Description
System.Boolean

ErrorCaption

The string to be displayed in cells that contain errors.

Declaration

public string ErrorCaption { get; set; }

Property Value

Type	Description
System.String

FieldPrintTitles

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

Declaration

public bool FieldPrintTitles { get; set; }

Property Value

Type	Description
System.Boolean

Fields

The fields in the table

Declaration

public ExcelPivotTableFieldCollection Fields { get; }

Property Value

Type	Description
ExcelPivotTableFieldCollection

Filters

Filters applied to the pivot table

Declaration

public ExcelPivotTableFilterCollection Filters { get; }

Property Value

Type	Description
ExcelPivotTableFilterCollection

FirstDataCol

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

Declaration

public int FirstDataCol { get; set; }

Property Value

Type	Description
System.Int32

FirstDataRow

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

Declaration

public int FirstDataRow { get; set; }

Property Value

Type	Description
System.Int32

FirstHeaderRow

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

Declaration

public int FirstHeaderRow { get; set; }

Property Value

Type	Description
System.Int32

GrandTotalCaption

Specifies the string to be displayed for grand totals.

Declaration

public string GrandTotalCaption { get; set; }

Property Value

Type	Description
System.String

GridDropZones

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

Declaration

public bool GridDropZones { get; set; }

Property Value

Type	Description
System.Boolean

Indent

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

Declaration

public int Indent { get; set; }

Property Value

Type	Description
System.Int32

IsCalculated

True if the pivot table has been calculated.

Declaration

public bool IsCalculated { get; }

Property Value

Type	Description
System.Boolean

ItemPrintTitles

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

Declaration

public bool ItemPrintTitles { get; set; }

Property Value

Type	Description
System.Boolean

MissingCaption

The text to be displayed in cells with no value

Declaration

public string MissingCaption { get; set; }

Property Value

Type	Description
System.String

MultipleFieldFilters

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

Declaration

public bool MultipleFieldFilters { get; set; }

Property Value

Type	Description
System.Boolean

Name

Name of the pivottable object in Excel

Declaration

public string Name { get; set; }

Property Value

Type	Description
System.String

Outline

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

Declaration

public bool Outline { get; set; }

Property Value

Type	Description
System.Boolean

OutlineData

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

Declaration

public bool OutlineData { get; set; }

Property Value

Type	Description
System.Boolean

PageFields

Report filter fields

Declaration

public ExcelPivotTableRowColumnFieldCollection PageFields { get; }

Property Value

Type	Description
ExcelPivotTableRowColumnFieldCollection

PageWrap

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

Declaration

public int PageWrap { get; set; }

Property Value

Type	Description
System.Int32

PivotTableStyle

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

Declaration

public PivotTableStyles PivotTableStyle { get; set; }

Property Value

Type	Description
PivotTableStyles

PivotTableUri

The package internal URI to the pivottable Xml Document.

Declaration

public Uri PivotTableUri { get; }

Property Value

Type	Description
System.Uri

PivotTableXml

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

Declaration

public XmlDocument PivotTableXml { get; }

Property Value

Type	Description
System.Xml.XmlDocument

PrintDrill

If the drill indicators expand collapse buttons should be printed.

Declaration

public bool PrintDrill { get; set; }

Property Value

Type	Description
System.Boolean

RowFields

Row label fields

Declaration

public ExcelPivotTableRowColumnFieldCollection RowFields { get; }

Property Value

Type	Description
ExcelPivotTableRowColumnFieldCollection

RowGrandTotals

If the grand totals should be displayed for the PivotTable rows

Declaration

public bool RowGrandTotals { get; set; }

Property Value

Type	Description
System.Boolean

RowHeaderCaption

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

Declaration

public string RowHeaderCaption { get; set; }

Property Value

Type	Description
System.String

ShowCalcMember

Show the drill indicators

Declaration

public bool ShowCalcMember { get; set; }

Property Value

Type	Description
System.Boolean

ShowColumnHeaders

Whether to show column headers for the pivot table.

Declaration

public bool ShowColumnHeaders { get; set; }

Property Value

Type	Description
System.Boolean

ShowColumnStripes

Whether to show column stripe formatting for the pivot table.

Declaration

public bool ShowColumnStripes { get; set; }

Property Value

Type	Description
System.Boolean

ShowDataTips

If the tooltips should be displayed for PivotTable data cells.

Declaration

public bool ShowDataTips { get; set; }

Property Value

Type	Description
System.Boolean

ShowDrill

Show the drill down buttons

Declaration

public bool ShowDrill { get; set; }

Property Value

Type	Description
System.Boolean

ShowError

Indicates whether to show error messages in cells.

Declaration

public bool ShowError { get; set; }

Property Value

Type	Description
System.Boolean

ShowHeaders

Show field headers

Declaration

public bool ShowHeaders { get; set; }

Property Value

Type	Description
System.Boolean

ShowLastColumn

Whether to show the last column for the pivot table.

Declaration

public bool ShowLastColumn { get; set; }

Property Value

Type	Description
System.Boolean

ShowMemberPropertyTips

Show member property information

Declaration

public bool ShowMemberPropertyTips { get; set; }

Property Value

Type	Description
System.Boolean

ShowRowHeaders

Whether to show row headers for the pivot table.

Declaration

public bool ShowRowHeaders { get; set; }

Property Value

Type	Description
System.Boolean

ShowRowStripes

Whether to show row stripe formatting for the pivot table.

Declaration

public bool ShowRowStripes { get; set; }

Property Value

Type	Description
System.Boolean

ShowValuesRow

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

Declaration

public bool ShowValuesRow { get; set; }

Property Value

Type	Description
System.Boolean

StyleName

Pivot style name. Used for custom styles

Declaration

public string StyleName { get; set; }

Property Value

Type	Description
System.String

Styles

Individual styles for the pivot table.

Declaration

public ExcelPivotTableAreaStyleCollection Styles { get; }

Property Value

Type	Description
ExcelPivotTableAreaStyleCollection

TableStyle

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

Declaration

[Obsolete("Use the PivotTableStyle property for more options")]
public TableStyles TableStyle { get; set; }

Property Value

Type	Description
TableStyles

UseAutoFormatting

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

Declaration

public bool UseAutoFormatting { get; set; }

Property Value

Type	Description
System.Boolean

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.

Declaration

public int ValuesFieldPosition { get; set; }

Property Value

Type	Description
System.Int32

WorkSheet

The worksheet where the pivottable is located

Declaration

public ExcelWorksheet WorkSheet { get; set; }

Property Value

Type	Description
ExcelWorksheet

Methods

Calculate(Boolean)

Calculates the pivot table. Also see Calculate(Boolean) and CalculateAllPivotTables(Boolean)

Declaration

public void Calculate(bool refreshCache = false)

Parameters

Type	Name	Description
System.Boolean	refreshCache	If the pivot cache should be refreshed from the source data, before calculating the pivot table.

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(Boolean) IsCalculated Refresh()

Declaration

public object GetPivotData(string dataFieldName)

Parameters

Type	Name	Description
System.String	dataFieldName	The name of the data field. If a data field with the name does exist in the table, a #REF! error is returned-

Returns

Type	Description
System.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(Boolean) IsCalculated Refresh()

Declaration

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

Parameters

Type	Name	Description
System.String	dataFieldName	The name of the data field. If a data field with the name does exist in the table, a #REF! error is returned-
System.Collections.Generic.IList<PivotDataFieldItemSelection>	fieldItemSelection	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

Type	Description
System.Object	The calculated value

SetCompact(Boolean)

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

Declaration

public void SetCompact(bool value = true)

Parameters

Type	Name	Description
System.Boolean	value	The the value for the Compact property.