Interface IPivotTable

Represents a PivotTable on a worksheet.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.UWP.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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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();
      }

Extension Methods

DateTimeExtension.ToDateTime(Object)