PivotTableCollection

PivotTableCollection class

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

public class PivotTableCollection : CollectionBase<PivotTable>, IDisposable

Properties

Name Description
Capacity { get; set; }
Count { get; }
Item { get; } Gets the PivotTable report by index. (3 indexers)
Item { get; set; }

Methods

Name Description
Add(PivotTable, string, string) Adds a new PivotTable Object to the collection from another PivotTable.
Add(string, string, string) Adds a new PivotTable cache to a PivotCaches collection.
Add(PivotTable, int, int, string) Adds a new PivotTable Object to the collection from another PivotTable.
Add(string, int, int, string) Adds a new PivotTable cache to a PivotCaches collection.
Add(string, string, string, bool) Adds a new PivotTable cache to a PivotCaches collection.
Add(string, int, int, string, bool) Adds a new PivotTable cache to a PivotCaches collection.
Add(string[], bool, PivotPageFields, string, string) Adds a new PivotTable Object to the collection with multiple consolidation ranges as data source.
Add(string[], bool, PivotPageFields, int, int, string) Adds a new PivotTable Object to the collection with multiple consolidation ranges as data source.
BinarySearch(PivotTable)
BinarySearch(PivotTable, IComparer<PivotTable>)
BinarySearch(int, int, PivotTable, IComparer<PivotTable>)
Clear() Clear all pivot tables. (2 methods)
Contains(PivotTable)
CopyTo(PivotTable[])
CopyTo(PivotTable[], int)
CopyTo(int, PivotTable[], int, int)
Dispose() Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
Exists(Predicate<PivotTable>)
Find(Predicate<PivotTable>)
FindAll(Predicate<PivotTable>)
FindIndex(Predicate<PivotTable>)
FindIndex(int, Predicate<PivotTable>)
FindIndex(int, int, Predicate<PivotTable>)
FindLast(Predicate<PivotTable>)
FindLastIndex(Predicate<PivotTable>)
FindLastIndex(int, Predicate<PivotTable>)
FindLastIndex(int, int, Predicate<PivotTable>)
GetEnumerator()
IndexOf(PivotTable)
IndexOf(PivotTable, int)
IndexOf(PivotTable, int, int)
LastIndexOf(PivotTable)
LastIndexOf(PivotTable, int)
LastIndexOf(PivotTable, int, int)
Remove(PivotTable) Deletes the specified PivotTable and delete the PivotTable data
Remove(PivotTable, bool) Deletes the specified PivotTable
RemoveAt(int) Deletes the PivotTable at the specified index and delete the PivotTable data (2 methods)
RemoveAt(int, bool) Deletes the PivotTable at the specified index

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