Xamarin.Forms

  • Code Examples
  • Upgrade Guide
  • User Guide
  • Demos
  • Support
  • Forums
  • Download
Interface IPivotTable

    Show / Hide Table of Contents

    Interface IPivotTable

    Represents a PivotTable on a worksheet.

    Namespace: Syncfusion.XlsIO
    Assembly: Syncfusion.XlsIO.Portable.dll
    Syntax
    public interface IPivotTable

    Properties

    BuiltInStyle

    Gets or sets the PivotBuiltInStyles of the pivot table.

    Declaration
    Nullable<PivotBuiltInStyles> BuiltInStyle { get; set; }
    Property Value
    Type Description
    System.Nullable<PivotBuiltInStyles>
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache); 
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
    
            //Sets the built-in style of the pivot table
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    CacheIndex

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

    Declaration
    int CacheIndex { get; }
    Property Value
    Type Description
    System.Int32
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cacheStuName = workbook.PivotCaches.Add(sheet["A1:C3"]);
            IPivotCache cacheStuMark = workbook.PivotCaches.Add(sheet["A1:C4"]);
            IPivotCache cacheStuMarkName = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cacheStuMarkName);
    
            //Gets the index number of the PivotTable cache
            int pivotCacheIndex = pivotTable.CacheIndex;
            if(pivotCacheIndex >= 0)
            {
                //Your Code Here
            }
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    CalculatedFields

    Gets the collection of IPivotCalculatedFields in the pivot table. Read-only.

    Declaration
    IPivotCalculatedFields CalculatedFields { get; }
    Property Value
    Type Description
    IPivotCalculatedFields
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark1";
            sheet.Range["D1"].Text = "Mark2";
            sheet.Range["A1:D1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            sheet.Range["D2"].Number = 87;
            sheet.Range["D3"].Number = 68;
            sheet.Range["D4"].Number = 98;
            sheet.Range["D5"].Number = 45;
            sheet.Range["D6"].Number = 70;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
    
            //Gets the collection of calculated fields in the pivot table
            IPivotCalculatedFields calculatedFields = pivotTable.CalculatedFields;
            calculatedFields.Add("AverageMark", "(Mark1+Mark2)/2");
    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    ColumnFields

    Gets the collection of IPivotFields in the pivot table. Read-only.

    Declaration
    IPivotFields ColumnFields { get; }
    Property Value
    Type Description
    IPivotFields
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark";
            sheet.Range["D1"].Text = "Student City";
            sheet.Range["A1:D1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 88;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            sheet.Range["D2"].Text = "Chennai";
            sheet.Range["D3"].Text = "Bangalore";
            sheet.Range["D4"].Text = "Chennai";
            sheet.Range["D5"].Text = "Mysore";
            sheet.Range["D6"].Text = "Bangalore";
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
    
            //Gets the collection of row fields in the pivot table
            IPivotFields columnFields = pivotTable.ColumnFields;
            columnFields[0].CanDragToRow = false;               
    
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    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
    bool ColumnGrand { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Sets value indicating whether the PivotTable contains column with grand totals for rows
            pivotTable.ColumnGrand = false;
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    ColumnsPerPage

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

    Declaration
    int ColumnsPerPage { get; }
    Property Value
    Type Description
    System.Int32
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
    
            //Gets the number of columns per page for this PivotTable
            int columnPerPage = pivotSheet.PivotTables[0].ColumnsPerPage;
            if(columnPerPage != 0)
            {
                //Your Code Here
            }                                                                    
    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    DataFields

    Gets the collection of IPivotDataFields in pivot table. Read-only.

    Declaration
    IPivotDataFields DataFields { get; }
    Property Value
    Type Description
    IPivotDataFields
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates the use of DataFields property in pivot table.

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Name = "Sample Pivot Table";
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
    
            //Add the DataField to the Data fields collection
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    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
    bool DisplayFieldCaptions { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    Following code illustrates use of DisplayFieldCaptions property in the pivot table.

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Sets a Boolean value to pivot field captions are displayed in the grid
            pivotTable.DisplayFieldCaptions = false;
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    Fields

    Gets the collection of IPivotFields. Read-only.

    Declaration
    IPivotFields Fields { get; }
    Property Value
    Type Description
    IPivotFields
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates the use of Fields property in pivot table.

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Name = "Sample Pivot Table";
    
            //Sets the value for pivot field using index from pivot fields collection
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
    
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    Location

    Gets or sets IRange of pivot table location in the worksheet.

    Declaration
    IRange Location { get; set; }
    Property Value
    Type Description
    IRange
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Sets the pivot table location in the worksheet
            pivotTable.Location = pivotSheet["B2"];
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    Name

    Gets or sets the pivot table name. This property allows you to Read and Write a pivot table name.

    Declaration
    string Name { get; set; }
    Property Value
    Type Description
    System.String
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how the use of Name property in pivot table.

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Sets the pivot table name
            pivotTable.Name = "Sample Pivot Table";
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    Options

    Gets the IPivotTableOptions of pivot table. Read-only.

    Declaration
    IPivotTableOptions Options { get; }
    Property Value
    Type Description
    IPivotTableOptions
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Gets the pivot table options
            IPivotTableOptions pivotTableOptions = pivotTable.Options;
            pivotTableOptions.ColumnHeaderCaption = "Student Details";
            pivotTableOptions.RowHeaderCaption = "Student Records";
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    PageFields

    Gets the collection of IPivotFields in the pivot table. Read-only.

    Declaration
    IPivotFields PageFields { get; }
    Property Value
    Type Description
    IPivotFields
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark";
            sheet.Range["D1"].Text = "Student City";
            sheet.Range["A1:D1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 88;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            sheet.Range["D2"].Text = "Chennai";
            sheet.Range["D3"].Text = "Bangalore";
            sheet.Range["D4"].Text = "Chennai";
            sheet.Range["D5"].Text = "Mysore";
            sheet.Range["D6"].Text = "Bangalore";
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[3].Axis = PivotAxisTypes.Page;
    
            //Gets the collection of page fields in the pivot table
            IPivotFields pageFields = pivotTable.PageFields;
            pageFields[0].CanDragToRow = false;               
    
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    RepeatItemsOnEachPrintedPage

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

    Declaration
    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. To know more about Pivot Tables refer this link.

    Examples

    Following code illustrates use of RepeatItemsOnEachPrintedPage property in the pivot table.

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Sets a Boolean value indicating whether labels must be repeated on every page
            pivotTable.RepeatItemsOnEachPrintedPage = true;
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    RowFields

    Gets the collection of IPivotFields in the pivot table. Read-only.

    Declaration
    IPivotFields RowFields { get; }
    Property Value
    Type Description
    IPivotFields
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark";
            sheet.Range["D1"].Text = "Student City";
            sheet.Range["A1:D1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 88;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            sheet.Range["D2"].Text = "Chennai";
            sheet.Range["D3"].Text = "Bangalore";
            sheet.Range["D4"].Text = "Chennai";
            sheet.Range["D5"].Text = "Mysore";
            sheet.Range["D6"].Text = "Bangalore";
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
    
            //Gets the collection of row fields in the pivot table
            IPivotFields rowFields = pivotTable.RowFields;
            rowFields[0].CanDragToColumn = false;
    
            pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    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
    bool RowGrand { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Sets value indicating whether the PivotTable contains row with grand totals for columns
            pivotTable.RowGrand = false;
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    RowsPerPage

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

    Declaration
    int RowsPerPage { get; }
    Property Value
    Type Description
    System.Int32
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
    
            //Gets the number of rows per page for this PivotTable
            int rowsPerPage = pivotSheet.PivotTables[0].RowsPerPage;
            if(rowsPerPage != 0)
            {
                //Your Code Here
            }  
    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    ShowColumnGrand

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

    Declaration
    bool ShowColumnGrand { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Sets a Boolean value indicating whether the PivotTable contains grand totals for columns
            pivotTable.ShowColumnGrand = false;
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    ShowDataFieldInRow

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

    Declaration
    bool ShowDataFieldInRow { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark";
            sheet.Range["D1"].Text = "Student City";
            sheet.Range["A1:D1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 88;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            sheet.Range["D2"].Text = "Chennai";
            sheet.Range["D3"].Text = "Bangalore";
            sheet.Range["D4"].Text = "Chennai";
            sheet.Range["D5"].Text = "Mysore";
            sheet.Range["D6"].Text = "Bangalore";
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;   
    
            //Sets a Boolean value to the PivotTable data fields are shown in rows
            pivotTable.ShowDataFieldInRow = true;
    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    ShowDrillIndicators

    Gets or sets a Boolean value indicating whether to toggle the display of drill indicators in the PivotTable. Default value is true.

    Declaration
    bool ShowDrillIndicators { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark";
            sheet.Range["D1"].Text = "Student City";
            sheet.Range["A1:D1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 88;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            sheet.Range["D2"].Text = "Chennai";
            sheet.Range["D3"].Text = "Bangalore";
            sheet.Range["D4"].Text = "Chennai";
            sheet.Range["D5"].Text = "Mysore";
            sheet.Range["D6"].Text = "Bangalore";
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[0].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[3].Axis = PivotAxisTypes.Column;                            
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
    
            //Sets a Boolean value to display of drill indicators in the PivotTable
            pivotTable.ShowDrillIndicators = false;
    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    ShowRowGrand

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

    Declaration
    bool ShowRowGrand { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

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

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Sets a Boolean value to PivotTable contains grand totals for rows
            pivotTable.ShowRowGrand = false;
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    Methods

    ClearTable()

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

    Declaration
    void ClearTable()
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    Following code illustrates how to clear the all fields, filters and sorting using ClearTable method.

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);         
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
    
            //Clears all the fields, deletes all filtering and sorting applied to the PivotTable
            pivotTable.ClearTable();
    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    GetCellFormat(String)

    Get cell format for the given pivot range.

    Declaration
    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

    Examples

    Following code snippet illustrates how to get pivot cell format from the pivot table.

          using (ExcelEngine excelEngine = new ExcelEngine())
          {
            //Create a worksheet.        
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            sheet.Range["A1"].Text = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].AutofitColumns();
            sheet.Range["A2"].Number = 1;
            sheet.Range["A3"].Number = 2;
            sheet.Range["A4"].Number = 3;
            sheet.Range["A5"].Number = 4;
            sheet.Range["A6"].Number = 5;
            sheet.Range["B2"].Text = "Andrew";
            sheet.Range["B3"].Text = "Marson";
            sheet.Range["B4"].Text = "Jack";
            sheet.Range["B5"].Text = "Sam";
            sheet.Range["B6"].Text = "Breto";
            sheet.Range["C2"].Number = 77;
            sheet.Range["C3"].Number = 45;
            sheet.Range["C4"].Number = 92;
            sheet.Range["C5"].Number = 39;
            sheet.Range["C6"].Number = 55;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);         
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
    
            //Get the cell format for "A1" pivot range.
            IPivotCellFormat cellFormat = pivotTable.GetCellFormat("A1");
            cellFormat.BackColor = ExcelKnownColors.Red;
    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }
    Back to top Generated by DocFX
    Copyright © 2001 - 2023 Syncfusion Inc. All Rights Reserved