Interface IPivotTables

Represents collection of IPivotTable in the worksheet.

Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.dll
Syntax
public interface IPivotTables

Properties

Count

Gets a number of pivot tables in the worksheet.

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

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use a Count 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("SamplePivot.xlsx");
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];

        //Gets a number of pivot tables in the worksheet
        int pivotTableCount = pivotSheet.PivotTables.Count;
        if(pivotTableCount > 0)
        {
            //Your Code Here
        }

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Item[Int32]

Gets a IPivotTable from the collection with the specified index.

Declaration
IPivotTable this[int index] { get; }
Parameters
Type Name Description
System.Int32 index

Zero-based index of the pivot table.
Property Value
Type
IPivotTable
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use a index property of the pivot table collections.

      using (ExcelEngine excelEngine = new ExcelEngine())
      {
        //Create a worksheet.        
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Open("SamplePivot.xlsx");
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];

        //Gets a pivot table from the collection with the specified index
        IPivotTable pivotTable = pivotSheet.PivotTables[0];
        pivotTable.Name = "Sample Pivot Table";
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark10;

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Item[String]

Gets a IPivotTable from the collection with the specified name.

Declaration
IPivotTable this[string name] { get; }
Parameters
Type Name Description
System.String name

Name of the pivot table.
Property Value
Type
IPivotTable
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use name property in the pivot table collections.

      using (ExcelEngine excelEngine = new ExcelEngine())
      {
        //Create a worksheet.        
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Open("SamplePivot.xlsx");
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];

        //Gets a pivot table from the collection with the specified name
        IPivotTable pivotTable = pivotSheet.PivotTables["PivotTable1"];
        pivotTable.Name = "Sample Pivot Table";
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark10;

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Methods

Add(String, IRange, IPivotCache)

Adds a IPivotTable to the collection.

Declaration
IPivotTable Add(string name, IRange location, IPivotCache cache)
Parameters
Type Name Description
System.String name

Name of the pivot table.
IRange location

Pivot table location.
IPivotCache cache

Pivot cache of the pivot table.
Returns
Type Description
IPivotTable

Returns the pivot table object.
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code illustrates how to add pivot table using Add 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"]);

        //Adds a pivot table to the collection
        IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache); 

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Remove(String)

Removes pivot table with specified name from this collection.

Declaration
void Remove(string name)
Parameters
Type Name Description
System.String name

Name of the pivot table to remove.
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use Remove method in the pivot table collections.

      using (ExcelEngine excelEngine = new ExcelEngine())
      {
        //Create a worksheet.        
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Open("SamplePivot.xlsx");
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];

        //Removes pivot table with specified name from this collection
        pivotSheet.PivotTables.Remove("PivotTable1");

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

RemoveAt(Int32)

Removes a pivot table with the specified index from this collection.

Declaration
void RemoveAt(int index)
Parameters
Type Name Description
System.Int32 index

Index of the pivot table to remove.
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use RemoveAt method in the pivot table collections.

      using (ExcelEngine excelEngine = new ExcelEngine())
      {
        //Create a worksheet.        
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Open("SamplePivot.xlsx");
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];

        //Removes a pivot table with the specified index from this collection
        pivotSheet.PivotTables.RemoveAt(0);

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Extension Methods