Table of Contents

Class ExcelPivotTable

Namespace
OfficeOpenXml.Table.PivotTable
Assembly
EPPlus.dll

An Excel Pivottable

public class ExcelPivotTable : XmlHelper
Inheritance
ExcelPivotTable
Inherited Members

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

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 

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

TableStyle

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

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

Property Value

TableStyles

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

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.