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

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

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

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.