menu

Xamarin.Forms

  • Code Examples
  • Upgrade Guide
  • User Guide
  • Demos
  • Support
  • Forums
  • Download
Class PivotTableImpl - Xamarin.Forms API Reference | Syncfusion

    Show / Hide Table of Contents

    Class PivotTableImpl

    Represents a PivotTable on a worksheet.

    Inheritance
    System.Object
    CommonObject
    PivotTableImpl
    Implements
    IParentApplication
    System.IDisposable
    ICloneParent
    IPivotTable
    Inherited Members
    CommonObject.AddReference()
    CommonObject.AppImplementation
    CommonObject.CheckDisposed()
    CommonObject.Dispose()
    CommonObject.Finalize()
    CommonObject.FindParent(Object, Type)
    CommonObject.FindParent(Object, Type, Boolean)
    CommonObject.FindParent(Type)
    CommonObject.FindParent(Type, Boolean)
    CommonObject.FindParent(Type[])
    CommonObject.FindParents(Type[])
    CommonObject.m_bIsDisposed
    CommonObject.OnDispose()
    CommonObject.Parent
    CommonObject.ReferenceCount
    CommonObject.ReleaseReference()
    CommonObject.SetParent(Object)
    Namespace: Syncfusion.XlsIO.Implementation.PivotTables
    Assembly: Syncfusion.XlsIO.Portable.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 report’s 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 report’s 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

    PivotEngineValues

    Gets or sets the pivot engine of the pivot table.

    Declaration
    public PivotEngine PivotEngineValues { get; set; }
    Property Value
    Type
    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
    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

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

    Implements

    IParentApplication
    System.IDisposable
    ICloneParent
    IPivotTable
    Back to top Generated by DocFX
    Copyright © 2001 - 2025 Syncfusion Inc. All Rights Reserved