Class PivotTableImpl

Represents a PivotTable on a worksheet.

Inheritance

System.Object

CommonObject

PivotTableImpl

Implements

IParentApplication

System.IDisposable

ICloneParent

IPivotTable

Inherited Members

CommonObject.m_bIsDisposed

CommonObject.FindParent(Type)

CommonObject.FindParent(Type, Boolean)

CommonObject.FindParent(Object, Type)

CommonObject.FindParent(Object, Type, Boolean)

CommonObject.FindParents(Type[])

CommonObject.FindParent(Type[])

CommonObject.SetParent(Object)

CommonObject.CheckDisposed()

CommonObject.AddReference()

CommonObject.ReleaseReference()

CommonObject.Dispose()

CommonObject.OnDispose()

CommonObject.Parent

CommonObject.AppImplementation

CommonObject.ReferenceCount

System.Object.ToString()

System.Object.Equals(System.Object)

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

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

System.Object.GetHashCode()

System.Object.GetType()

System.Object.MemberwiseClone()

Namespace: Syncfusion.XlsIO.Implementation.PivotTables

Assembly: Syncfusion.XlsIO.Base.dll

Syntax

public class PivotTableImpl : CommonObject, IParentApplication, IDisposable, ICloneParent, IPivotTable

Constructors

PivotTableImpl(IApplication, Object)

Initializes a new instance of pivot table.

Declaration

public PivotTableImpl(IApplication application, object parent)

Parameters

Type	Name	Description
IApplication	application	Application object that represents the Excel application.
System.Object	parent	Parent object of this collection.

PivotTableImpl(IApplication, Object, Int32, IRange)

Initializes a new instance of pivot table with the specified cache index and pivot table location.

Declaration

public PivotTableImpl(IApplication application, object parent, int cacheIndex, IRange location)

Parameters

Type	Name	Description
IApplication	application	Application object that represents the Excel application.
System.Object	parent	Parent object of this collection.
System.Int32	cacheIndex	Cache index.
IRange	location	Pivot table location.

Fields

DEF_FIRSTRECORD_CODE

Code of the first record for the pivot table.

Declaration

public const TBIFFRecord DEF_FIRSTRECORD_CODE = TBIFFRecord.PivotViewDefinition

Field Value

Type	Description
TBIFFRecord

DefaultDataFieldStart

Default starting name of the pivot data field.

Declaration

public const string DefaultDataFieldStart = "Sum of "

Field Value

Type	Description
System.String

Excel2007Version

When Pivot table create, updated and refreshed then Excel 2007 stores this version

Declaration

public const byte Excel2007Version = 3

Field Value

Type	Description
System.Byte

Properties

Application

Gets the application for this object.

Declaration

public IApplication Application { get; }

Property Value

Type	Description
IApplication

BuiltInStyle

Gets or sets the built-in style of the pivot table.

Declaration

public PivotBuiltInStyles? BuiltInStyle { get; set; }

Property Value

Type	Description
System.Nullable<PivotBuiltInStyles>

Examples

Following code illustrates how to access the BuiltInStyle property of the pivot table.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark12;

Cache

Gets cache used by this pivot table. Read-only.

Declaration

public PivotCacheImpl Cache { get; }

Property Value

Type	Description
PivotCacheImpl

CacheIndex

Gets or sets the index number of the PivotTable cache. Read-only.

Declaration

public int CacheIndex { get; set; }

Property Value

Type	Description
System.Int32

CalculatedFields

Gets the collection of calculated fields in the pivot table. Read-only.

Declaration

public IPivotCalculatedFields CalculatedFields { get; }

Property Value

Type	Description
IPivotCalculatedFields

Examples

Following code illustrates how to access the CalculatedFields property of the pivot table.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("PivotTable.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

IPivotTable pivotTable = sheet.PivotTables[0];

//Add calculated field to the first pivot table
IPivotField field = pivotTable.CalculatedFields.Add("Percent", "Sales/Total*100");

workbook.SaveAs("PivotTable.xlsx");
workbook.Close();
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();

ColumnFields

Gets the collection of column fields in the pivot table. Read-only.

Declaration

public IPivotFields ColumnFields { get; }

Property Value

Type	Description
IPivotFields

ColumnGrand

Gets or sets value indicating whether the PivotTable contains column with grand totals for rows (same as ColumnGrand in VBA). Default value is true.

Declaration

public bool ColumnGrand { get; set; }

Property Value

Type	Description
System.Boolean

Examples

Following code illustrates how to access the ColumnGrand property of the pivot table.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.ColumnGrand = false;

ColumnsPerPage

Gets the number of columns per page for this PivotTable that the filter area will occupy. Read-only.

Declaration

public int ColumnsPerPage { get; set; }

Property Value

Type	Description
System.Int32

CustomStyleName

Gets or sets name of custom style for the pivot table.

Declaration

public string CustomStyleName { get; set; }

Property Value

Type	Description
System.String

DataFields

Gets the collection of data fields in pivot table. Read-only.

Declaration

public PivotDataFields DataFields { get; }

Property Value

Type	Description
PivotDataFields

DisplayErrorString

True if the PivotTable report displays a custom error string in cells that contain errors. False otherwise. Read/Write Boolean.

Declaration

public bool DisplayErrorString { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

Use the ErrorString property to set the custom error string. This property is particularly useful for suppressing divide-by-zero errors when calculated fields are pivoted.

DisplayFieldCaptions

Gets or sets a Boolean value indicating whether filter buttons and pivot field captions for rows and columns are displayed in the grid. The default value is True.

Declaration

public bool DisplayFieldCaptions { get; set; }

Property Value

Type	Description
System.Boolean

DisplayNullString

True if the PivotTable report displays a custom string in cells that contain null values. False otherwise. Read/Write Boolean.

Declaration

public bool DisplayNullString { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

Use the NullString property to set the custom null string. The default value is True.

EnableDrilldown

True if drilldown into pivot table data is enabled. False otherwise. The default value is True.

Declaration

public bool EnableDrilldown { get; set; }

Property Value

Type	Description
System.Boolean

EnableFieldDialog

True if the PivotTable Field dialog box is available for the user. False otherwise. The default value is True.

Declaration

public bool EnableFieldDialog { get; set; }

Property Value

Type	Description
System.Boolean

EnableWizard

True if the PivotTable Wizard is available. False otherwise. The default value is True.

Declaration

public bool EnableWizard { get; set; }

Property Value

Type	Description
System.Boolean

EndLocation

Gets pivot table end location. Read-only.

Declaration

public IRange EndLocation { get; set; }

Property Value

Type	Description
IRange

ErrorString

Gets or sets the string displayed in cells that contain errors when the DisplayErrorString property is True.

Declaration

public string ErrorString { get; set; }

Property Value

Type	Description
System.String

Fields

Gets the collection of pivot fields. Read-only.

Declaration

public PivotTableFields Fields { get; }

Property Value

Type	Description
PivotTableFields

FirstDataCol

Specifies the first column of the PivotTable data, relative to the top left cell in the ref value

Declaration

public int FirstDataCol { get; set; }

Property Value

Type	Description
System.Int32

FirstDataRow

Specifies the first column of the PivotTable data, relative to the top left cell in the ref value

Declaration

public int FirstDataRow { get; set; }

Property Value

Type	Description
System.Int32

FirstHeaderRow

Specifies 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

IsChanged

Represents the pivot table modified.

Declaration

public bool IsChanged { get; set; }

Property Value

Type	Description
System.Boolean

Location

Gets or sets the pivot table location in the worksheet.

Declaration

public IRange Location { get; set; }

Property Value

Type	Description
IRange

ManualUpdate

True if the PivotTable report is recalculated only at the user's request. The default value is False.

Declaration

public bool ManualUpdate { get; set; }

Property Value

Type	Description
System.Boolean

MergeLabels

True if the specified PivotTable report’s outer-row item, column item, subtotal, and grand total labels use merged cells.

Declaration

public bool MergeLabels { get; set; }

Property Value

Type	Description
System.Boolean

Name

Gets or sets the pivot table name.

Declaration

public string Name { get; set; }

Property Value

Type	Description
System.String

NullString

Gets or sets the string displayed in cells that contain null values when the DisplayNullString property is True.

Declaration

public string NullString { get; set; }

Property Value

Type	Description
System.String

Options

Gets the pivot table options. Read-only.

Declaration

public IPivotTableOptions Options { get; }

Property Value

Type	Description
IPivotTableOptions

PageFieldOrder

Returns or sets the order in which page fields are added to the PivotTable report’s layout.

Declaration

public ExcelPagesOrder PageFieldOrder { get; set; }

Property Value

Type	Description
ExcelPagesOrder

PageFields

Gets the collection of page fields in the pivot table. Read-only.

Declaration

public IPivotFields PageFields { get; }

Property Value

Type	Description
IPivotFields

PageFieldStyle

Returns or sets the style used in the bound page field area. The default value is a null string (no style is applied by default).

Declaration

public string PageFieldStyle { get; set; }

Property Value

Type	Description
System.String

PageFieldWrapCount

Returns or sets the number of page fields in each column or row in the PivotTable report.

Declaration

public int PageFieldWrapCount { get; set; }

Property Value

Type	Description
System.Int32

PivotEngineValues

Gets or sets the pivot engine of the pivot table.

Declaration

public PivotEngine PivotEngineValues { get; set; }

Property Value

Type	Description
PivotEngine

Remarks

This method is not supported in WinRT, Windows Phone, Portable and Silverlight Platforms.

RepeatItemsOnEachPrintedPage

Gets or sets a Boolean value indicating whether labels must be repeated on every page.The default value is True.

Declaration

public bool RepeatItemsOnEachPrintedPage { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

True if row, column, and item labels appear on the first row of each page when the specified PivotTable report is printed. False if labels are printed only on the first page.

RowFields

Gets the collection of Row fields in the pivot table. Read-only.

Declaration

public IPivotFields RowFields { get; }

Property Value

Type	Description
IPivotFields

RowGrand

Gets or sets value indicating whether the PivotTable contains row with grand totals for columns (same as RowGrand in VBA). Default value is true.

Declaration

public bool RowGrand { get; set; }

Property Value

Type	Description
System.Boolean

Examples

Following code illustrates how to access the RowGrand property of the pivot table.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.RowGrand = false;

RowsPerPage

Gets the number of rows per page for this PivotTable that the filter area will occupy. Read-only.

Declaration

public int RowsPerPage { get; set; }

Property Value

Type	Description
System.Int32

ShowColHeaderStyle

Specifies a boolean value that indicates whether to show column headers for the table.

Declaration

public bool ShowColHeaderStyle { get; set; }

Property Value

Type	Description
System.Boolean

ShowColStripes

Specifies a boolean value that indicates whether to show column stripe formatting for the table.

Declaration

public bool ShowColStripes { get; set; }

Property Value

Type	Description
System.Boolean

ShowColumnGrand

Gets or sets a Boolean value indicating whether the PivotTable contains grand totals for columns.

Declaration

public bool ShowColumnGrand { get; set; }

Property Value

Type	Description
System.Boolean

ShowDataFieldInRow

Gets or sets a Boolean value indicating whether the PivotTable data fields are shown in rows.

Declaration

public bool ShowDataFieldInRow { get; set; }

Property Value

Type	Description
System.Boolean

ShowDrillIndicators

Declaration

public bool ShowDrillIndicators { get; set; }

Property Value

Type	Description
System.Boolean

ShowLastCol

Specifies a boolean value that indicates whether to show the last column.

Declaration

public bool ShowLastCol { get; set; }

Property Value

Type	Description
System.Boolean

ShowRowGrand

Gets or sets a Boolean value indicating whether the PivotTable contains grand totals for rows.

Declaration

public bool ShowRowGrand { get; set; }

Property Value

Type	Description
System.Boolean

ShowRowHeaderStyle

Specifies a boolean value that indicates whether to show row headers for the table.

Declaration

public bool ShowRowHeaderStyle { get; set; }

Property Value

Type	Description
System.Boolean

ShowRowStripes

Specifies a boolean value that indicates whether to show row stripe formatting for the table.

Declaration

public bool ShowRowStripes { get; set; }

Property Value

Type	Description
System.Boolean

Workbook

Gets parent workbook. Read-only.

Declaration

public WorkbookImpl Workbook { get; }

Property Value

Type	Description
WorkbookImpl

Worksheet

Gets parent worksheet. Read-only.

Declaration

public WorksheetImpl Worksheet { get; }

Property Value

Type	Description
WorksheetImpl

Methods

AutoFitPivotTable(PivotTableImpl)

This method autofit the tabular layout pivot table only. Note: Compact layout and outline layout are autofitted by MS Excel itself. this method should be removed once tabular form layout is supported using Pivot engine layout.

Declaration

public void AutoFitPivotTable(PivotTableImpl m_pivotTable)

Parameters

Type	Name	Description
PivotTableImpl	m_pivotTable

ClearTable()

Clears all the fields, deletes all filtering and sorting applied to the PivotTable.

Declaration

public void ClearTable()

Clone(Object)

Creates a new object that is a copy of the current instance.

Declaration

public object Clone(object parent)

Parameters

Type	Name	Description
System.Object	parent	Parent object for a copy of this instance.

Returns

Type	Description
System.Object	A new object that is a copy of this instance.

Clone(Object, Int32, Dictionary<String, String>)

Creates a new object that is a copy of the current instance.

Declaration

public object Clone(object parent, int cacheIndex, Dictionary<string, string> hashWorksheetNames)

Parameters

Type	Name	Description
System.Object	parent	Parent object for a copy of this instance.
System.Int32	cacheIndex
System.Collections.Generic.Dictionary<System.String, System.String>	hashWorksheetNames

Returns

Type	Description
System.Object	A new object that is a copy of this instance.

GetCellFormat(String)

Get cell format for the given pivot range.

Declaration

public IPivotCellFormat GetCellFormat(string range)

Parameters

Type	Name	Description
System.String	range	Pivot range to get the cell format.

Returns

Type	Description
IPivotCellFormat	Pivot cell format

Layout()

Draws a layout for the pivot table.

Declaration

public void Layout()

Remarks

This method is not supported in WinRT, Windows Phone, Portable and Silverlight Platforms.

Examples

Following code snippet illustrates how to layout a pivot table similar to MS Excel.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("PivotData.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];

IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[4].Axis = PivotAxisTypes.Page;
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
IPivotField datafield = pivotSheet.PivotTables[0].Fields[5];
pivotTable.DataFields.Add(datafield, "Sum of Units", PivotSubtotalTypes.Sum);
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark12;

//The following code sample must be included to XlsIO layout the pivot table like MS Excel.
pivotTable.Layout();

workbook.SaveAs("PivotTable.xlsx");
workbook.Close();
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();

Parse(IList, Int32)

Parses pivot table.

Declaration

public int Parse(IList data, int iPos)

Parameters

Type	Name	Description
System.Collections.IList	data	Records with pivot table data.
System.Int32	iPos	Offset to the first pivot table record.

Returns

Type	Description
System.Int32	Offset to the record after table records.

Serialize(OffsetArrayList)

Saves pivot table into OffsetArrayList.

Declaration

[CLSCompliant(false)]
public void Serialize(OffsetArrayList records)

Parameters

Type	Name	Description
OffsetArrayList	records	OffsetArrayList that will get all pivot table records.

Explicit Interface Implementations

IPivotTable.DataFields

Gets collection of pivot table data fields. Read-only.

Declaration

IPivotDataFields IPivotTable.DataFields { get; }

Returns

Type	Description
IPivotDataFields

IPivotTable.Fields

Returns collection of pivot fields. Read-only.

Declaration

IPivotFields IPivotTable.Fields { get; }

Returns

Type	Description
IPivotFields

Implements

IParentApplication

System.IDisposable

ICloneParent

IPivotTable