Interface IPivotTables

Represents collection of IPivotTable in the worksheet.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.Base.dll

Syntax

public interface IPivotTables

Properties

Count

Gets a number of pivot tables in the worksheet.

Declaration

int Count { get; }

Property Value

Type	Description
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	Description
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	Description
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();
      }