Class PivotTableImpl
Represents a PivotTable on a worksheet.
Inherited Members
Namespace: Syncfusion.XlsIO.Implementation.PivotTables
Assembly: Syncfusion.XlsIO.UWP.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
Field Value
Type |
---|
TBIFFRecord |
DefaultDataFieldStart
Default starting name of the pivot data field.
Declaration
public const string DefaultDataFieldStart = "Sum of "
Field Value
Type |
---|
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 |
---|
System.Byte |
Properties
Application
Gets the application for this object.
Declaration
public IApplication Application { get; }
Property Value
Type |
---|
IApplication |
BuiltInStyle
Gets or sets the built-in style of the pivot table.
Declaration
public Nullable<PivotBuiltInStyles> BuiltInStyle { get; set; }
Property Value
Type |
---|
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 |
---|
PivotCacheImpl |
CacheIndex
Gets or sets the index number of the PivotTable cache. Read-only.
Declaration
public int CacheIndex { get; set; }
Property Value
Type |
---|
System.Int32 |
CalculatedFields
Gets the collection of calculated fields in the pivot table. Read-only.
Declaration
public IPivotCalculatedFields CalculatedFields { get; }
Property Value
Type |
---|
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 |
---|
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 |
---|
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 |
---|
System.Int32 |
CustomStyleName
Gets or sets name of custom style for the pivot table.
Declaration
public string CustomStyleName { get; set; }
Property Value
Type |
---|
System.String |
DataFields
Gets the collection of data fields in pivot table. Read-only.
Declaration
public PivotDataFields DataFields { get; }
Property Value
Type |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
System.Boolean |
EndLocation
Gets pivot table end location. Read-only.
Declaration
public IRange EndLocation { get; set; }
Property Value
Type |
---|
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 |
---|
System.String |
Fields
Gets the collection of pivot fields. Read-only.
Declaration
public PivotTableFields Fields { get; }
Property Value
Type |
---|
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 |
---|
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 |
---|
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 |
---|
System.Int32 |
IsChanged
Represents the pivot table modified.
Declaration
public bool IsChanged { get; set; }
Property Value
Type |
---|
System.Boolean |
Location
Gets or sets the pivot table location in the worksheet.
Declaration
public IRange Location { get; set; }
Property Value
Type |
---|
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 |
---|
System.Boolean |
MergeLabels
True if the specified PivotTable reports outer-row item, column item, subtotal, and grand total labels use merged cells.
Declaration
public bool MergeLabels { get; set; }
Property Value
Type |
---|
System.Boolean |
Name
Gets or sets the pivot table name.
Declaration
public string Name { get; set; }
Property Value
Type |
---|
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 |
---|
System.String |
Options
Gets the pivot table options. Read-only.
Declaration
public IPivotTableOptions Options { get; }
Property Value
Type |
---|
IPivotTableOptions |
PageFieldOrder
Returns or sets the order in which page fields are added to the PivotTable reports layout.
Declaration
public ExcelPagesOrder PageFieldOrder { get; set; }
Property Value
Type |
---|
ExcelPagesOrder |
PageFields
Gets the collection of page fields in the pivot table. Read-only.
Declaration
public IPivotFields PageFields { get; }
Property Value
Type |
---|
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 |
---|
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 |
---|
System.Int32 |
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
System.Boolean |
ShowDrillIndicators
Declaration
public bool ShowDrillIndicators { get; set; }
Property Value
Type |
---|
System.Boolean |
ShowLastCol
Specifies a boolean value that indicates whether to show the last column.
Declaration
public bool ShowLastCol { get; set; }
Property Value
Type |
---|
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 |
---|
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 |
---|
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 |
---|
System.Boolean |
Workbook
Gets parent workbook. Read-only.
Declaration
public WorkbookImpl Workbook { get; }
Property Value
Type |
---|
WorkbookImpl |
Worksheet
Gets parent worksheet. Read-only.
Declaration
public WorksheetImpl Worksheet { get; }
Property Value
Type |
---|
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 |
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
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 |
---|
IPivotDataFields |
IPivotTable.Fields
Returns collection of pivot fields. Read-only.
Declaration
IPivotFields IPivotTable.Fields { get; }
Returns
Type |
---|
IPivotFields |