PivotTableCollection Class

Represents the collection of all the PivotTable objects on the specified worksheet.
Inheritance Hierarchy

Namespace:  Aspose.Cells.Pivot
Assembly:  Aspose.Cells (in Aspose.Cells.dll) Version: 22.5.0.0 (22.5.0)
Syntax
public class PivotTableCollection : CollectionBase, 
	IDisposable

The PivotTableCollection type exposes the following members.

Properties
Methods
  NameDescription
Public methodAdd(String, String, String)
Adds a new PivotTable cache to a PivotCaches collection.
Public methodAdd(PivotTable, String, String)
Adds a new PivotTable Object to the collection from another PivotTable.
Public methodAdd(String, Int32, Int32, String)
Adds a new PivotTable cache to a PivotCaches collection.
Public methodAdd(String, String, String, Boolean)
Adds a new PivotTable cache to a PivotCaches collection.
Public methodAdd(PivotTable, Int32, Int32, String)
Adds a new PivotTable Object to the collection from another PivotTable.
Public methodAdd(String, Int32, Int32, String, Boolean)
Adds a new PivotTable cache to a PivotCaches collection.
Public methodAdd(String, Boolean, PivotPageFields, String, String)
Adds a new PivotTable Object to the collection with multiple consolidation ranges as data source.
Public methodAdd(String, Boolean, PivotPageFields, Int32, Int32, String)
Adds a new PivotTable Object to the collection with multiple consolidation ranges as data source.
Public methodClear
Clear all pivot tables.
Public methodDispose
Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
Public methodEquals (Inherited from Object.)
Protected methodFinalize (Inherited from Object.)
Public methodGetEnumerator (Inherited from CollectionBase.)
Public methodGetHashCode (Inherited from Object.)
Public methodGetType (Inherited from Object.)
Protected methodMemberwiseClone (Inherited from Object.)
Protected methodOnClear (Inherited from CollectionBase.)
Protected methodOnClearComplete (Inherited from CollectionBase.)
Protected methodOnInsert (Inherited from CollectionBase.)
Protected methodOnInsertComplete (Inherited from CollectionBase.)
Protected methodOnRemove (Inherited from CollectionBase.)
Protected methodOnRemoveComplete (Inherited from CollectionBase.)
Protected methodOnSet (Inherited from CollectionBase.)
Protected methodOnSetComplete (Inherited from CollectionBase.)
Protected methodOnValidate (Inherited from CollectionBase.)
Public methodRemove(PivotTable)
Deletes the specified PivotTable and delete the PivotTable data
Public methodRemove(PivotTable, Boolean)
Deletes the specified PivotTable
Public methodRemoveAt(Int32)
Deletes the PivotTable at the specified index and delete the PivotTable data
Public methodRemoveAt(Int32, Boolean)
Deletes the PivotTable at the specified index
Public methodToString (Inherited from Object.)
Examples
[C#]

Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
cells[0, 0].Value = "fruit";
cells[1, 0].Value = "grape";
cells[2, 0].Value = "blueberry";
cells[3, 0].Value = "kiwi";
cells[4, 0].Value = "cherry";
cells[5, 0].Value = "grape";
cells[6, 0].Value = "blueberry";
cells[7, 0].Value = "kiwi";
cells[8, 0].Value = "cherry";

cells[0, 1].Value = "year";
cells[1, 1].Value = 2020;
cells[2, 1].Value = 2020;
cells[3, 1].Value = 2020;
cells[4, 1].Value = 2020;
cells[5, 1].Value = 2021;
cells[6, 1].Value = 2021;
cells[7, 1].Value = 2021;
cells[8, 1].Value = 2021;

cells[0, 2].Value = "amount";
cells[1, 2].Value = 50;
cells[2, 2].Value = 60;
cells[3, 2].Value = 70;
cells[4, 2].Value = 80;
cells[5, 2].Value = 90;
cells[6, 2].Value = 100;
cells[7, 2].Value = 110;
cells[8, 2].Value = 120;

PivotTableCollection pivots = sheet.PivotTables;

int pivotIndex = pivots.Add("=Sheet1!A1:C9", "A12", "TestPivotTable");
PivotTable pivot = pivots[pivotIndex];
pivot.AddFieldToArea(PivotFieldType.Row, "fruit");
pivot.AddFieldToArea(PivotFieldType.Column, "year");
pivot.AddFieldToArea(PivotFieldType.Data, "amount");

pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10;

//Change PivotField's attributes
PivotField rowField = pivot.RowFields[0];
rowField.DisplayName = "custom display name";

//Add PivotFilter
int index = pivot.PivotFilters.Add(0, PivotFilterType.Count);
PivotFilter filter = pivot.PivotFilters[index];
filter.AutoFilter.FilterTop10(0, false, false, 2);

//Add PivotFormatCondition
int formatIndex = pivot.PivotFormatConditions.Add();
PivotFormatCondition pfc = pivot.PivotFormatConditions[formatIndex];
FormatConditionCollection fcc = pfc.FormatConditions;
fcc.AddArea(pivot.DataBodyRange);
int idx = fcc.AddCondition(FormatConditionType.CellValue);
FormatCondition fc = fcc[idx];
fc.Formula1 = "100";
fc.Operator = OperatorType.GreaterOrEqual;
fc.Style.BackgroundColor = Color.Red;

pivot.RefreshData();
pivot.CalculateData();

//do your business

book.Save("out.xlsx");

[Visual Basic]

Dim book As Workbook = New Workbook()
Dim sheet As Worksheet = book.Worksheets(0)
Dim cells As Cells = sheet.Cells

cells(0, 0).Value = "fruit"
cells(1, 0).Value = "grape"
cells(2, 0).Value = "blueberry"
cells(3, 0).Value = "kiwi"
cells(4, 0).Value = "cherry"
cells(5, 0).Value = "grape"
cells(6, 0).Value = "blueberry"
cells(7, 0).Value = "kiwi"
cells(8, 0).Value = "cherry"

cells(0, 1).Value = "year"
cells(1, 1).Value = 2020
cells(2, 1).Value = 2020
cells(3, 1).Value = 2020
cells(4, 1).Value = 2020
cells(5, 1).Value = 2021
cells(6, 1).Value = 2021
cells(7, 1).Value = 2021
cells(8, 1).Value = 2021

cells(0, 2).Value = "amount"
cells(1, 2).Value = 50
cells(2, 2).Value = 60
cells(3, 2).Value = 70
cells(4, 2).Value = 80
cells(5, 2).Value = 90
cells(6, 2).Value = 100
cells(7, 2).Value = 110
cells(8, 2).Value = 120

Dim pivots As PivotTableCollection = sheet.PivotTables
Dim pivotIndex As Int32 = pivots.Add("=Sheet1!A1:C9", "A12", "TestPivotTable")
Dim pivot As PivotTable = pivots(pivotIndex)
pivot.AddFieldToArea(PivotFieldType.Row, "fruit")
Pivot.AddFieldToArea(PivotFieldType.Column, "year")
Pivot.AddFieldToArea(PivotFieldType.Data, "amount")

pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10

'Change PivotField's attributes
Dim rowField As PivotField = pivot.RowFields(0)
rowField.DisplayName = "custom display name"

'Add PivotFilter
Dim filterIndex As Int32 = pivot.PivotFilters.Add(0, PivotFilterType.Count)
Dim filter As PivotFilter = pivot.PivotFilters(filterIndex)
filter.AutoFilter.FilterTop10(0, False, False, 2)

'Add PivotFormatCondition
Dim formatIndex As Int32 = pivot.PivotFormatConditions.Add()
Dim pfc As PivotFormatCondition = pivot.PivotFormatConditions(formatIndex)
Dim fcc As FormatConditionCollection = pfc.FormatConditions
fcc.AddArea(pivot.DataBodyRange)
Dim idx As Int32 = fcc.AddCondition(FormatConditionType.CellValue)
Dim fc As FormatCondition = fcc(idx)
fc.Formula1 = "100"
fc.Operator = OperatorType.GreaterOrEqual
fc.Style.BackgroundColor = Color.Red

pivot.RefreshData()
pivot.CalculateData()

book.Save("out_vb.xlsx")
See Also