Class PivotTableCollection

Namespace: Aspose.Cells.Pivot
Assembly: Aspose.Cells.dll (25.2.0)

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

public class PivotTableCollection : CollectionBase<pivottable>, IList<pivottable>, ICollection<pivottable>, IEnumerable<pivottable>, ICollection, IEnumerable, IDisposable

Inheritance

object ← CollectionBase<pivottable> ← PivotTableCollection

Implements

IList<pivottable>, ICollection<pivottable>, IEnumerable<pivottable>, ICollection, IEnumerable, IDisposable

Inherited Members

Examples

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");

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")

Properties

this[int]

Gets the PivotTable report by index.

public PivotTable this[int index] { get; }

Property Value

PivotTable

this[string]

Gets the PivotTable report by pivottable’s name.

public PivotTable this[string name] { get; }

Property Value

PivotTable

this[int, int]

Gets the PivotTable report by pivottable’s position.

public PivotTable this[int row, int column] { get; }

Property Value

PivotTable

Methods

Add(string, string, string)

Adds a new PivotTable.

public int Add(string sourceData, string destCellName, string tableName)

Parameters

sourceData string

The data for the new PivotTable cache.

destCellName string

The cell in the upper-left corner of the PivotTable report’s destination range.

tableName string

The name of the new PivotTable report.

Returns

int

The new added cache index.

Add(string, string, string, bool)

Adds a new PivotTable.

public int Add(string sourceData, string destCellName, string tableName, bool useSameSource)

Parameters

sourceData string

The data for the new PivotTable cache.

destCellName string

The cell in the upper-left corner of the PivotTable report’s destination range.

tableName string

The name of the new PivotTable report.

useSameSource bool

Indicates whether using same data source when another existing pivot table has used this data source. If the property is true, it will save memory.

Returns

int

The new added cache index.

Add(string, int, int, string)

Adds a new PivotTable.

public int Add(string sourceData, int row, int column, string tableName)

Parameters

sourceData string

The data cell range for the new PivotTable.Example : Sheet1!A1:C8

row int

Row index of the cell in the upper-left corner of the PivotTable report’s destination range.

column int

Column index of the cell in the upper-left corner of the PivotTable report’s destination range.

tableName string

The name of the new PivotTable report.

Returns

int

The new added cache index.

Add(string, int, int, string, bool)

Adds a new PivotTable.

public int Add(string sourceData, int row, int column, string tableName, bool useSameSource)

Parameters

sourceData string

The data cell range for the new PivotTable.Example : Sheet1!A1:C8

row int

Row index of the cell in the upper-left corner of the PivotTable report’s destination range.

column int

Column index of the cell in the upper-left corner of the PivotTable report’s destination range.

tableName string

The name of the new PivotTable report.

useSameSource bool

Indicates whether using same data source when another existing pivot table has used this data source. If the property is true, it will save memory.

Returns

int

The new added cache index.

Add(string, int, int, string, bool, bool)

Adds a new PivotTable.

public int Add(string sourceData, int row, int column, string tableName, bool useSameSource, bool isXlsClassic)

Parameters

sourceData string

The data cell range for the new PivotTable.Example : Sheet1!A1:C8

row int

Row index of the cell in the upper-left corner of the PivotTable report’s destination range.

column int

Column index of the cell in the upper-left corner of the PivotTable report’s destination range.

tableName string

The name of the new PivotTable report.

useSameSource bool

Indicates whether using same data source when another existing pivot table has used this data source. If the property is true, it will save memory.

isXlsClassic bool

Indicates whether add classic pivot table of Excel 97-2003.

Returns

int

The new added cache index.

Add(string, string, string, bool, bool)

Adds a new PivotTable.

public int Add(string sourceData, string cell, string tableName, bool useSameSource, bool isXlsClassic)

Parameters

sourceData string

The data cell range for the new PivotTable.Example : Sheet1!A1:C8

cell string

The cell in the upper-left corner of the PivotTable report’s destination range.

tableName string

The name of the new PivotTable report.

useSameSource bool

Indicates whether using same data source when another existing pivot table has used this data source. If the property is true, it will save memory.

isXlsClassic bool

Indicates whether add classic pivot table of Excel 97-2003.

Returns

int

The new added cache index.

Add(PivotTable, string, string)

Adds a new PivotTable based on another PivotTable.

public int Add(PivotTable pivotTable, string destCellName, string tableName)

Parameters

pivotTable PivotTable

The source pivotTable.

destCellName string

The cell in the upper-left corner of the PivotTable report’s destination range.

tableName string

The name of the new PivotTable report.

Returns

int

The new added PivotTable index.

Add(PivotTable, int, int, string)

Adds a new PivotTable based on another PivotTable.

public int Add(PivotTable pivotTable, int row, int column, string tableName)

Parameters

pivotTable PivotTable

The source pivotTable.

row int

Row index of the cell in the upper-left corner of the PivotTable report’s destination range.

column int

Column index of the cell in the upper-left corner of the PivotTable report’s destination range.

tableName string

The name of the new PivotTable report.

Returns

int

The new added PivotTable index.

Add(string[], bool, PivotPageFields, string, string)

Adds a new PivotTable Object to the collection with multiple consolidation ranges as data source.

public int Add(string[] sourceData, bool isAutoPage, PivotPageFields pageFields, string destCellName, string tableName)

Parameters

sourceData string[]

The multiple consolidation ranges,such as {“Sheet1!A1:C8”,“Sheet2!A1:B8”}

isAutoPage bool

Whether auto create a single page field. If true,the following param pageFields will be ignored.

pageFields PivotPageFields

The pivot page field items.

destCellName string

destCellName The name of the new PivotTable report.

tableName string

the name of the new PivotTable report.

Returns

int

The new added PivotTable index.

Add(string[], bool, PivotPageFields, int, int, string)

Adds a new PivotTable Object to the collection with multiple consolidation ranges as data source.

public int Add(string[] sourceData, bool isAutoPage, PivotPageFields pageFields, int row, int column, string tableName)

Parameters

sourceData string[]

The multiple consolidation ranges,such as {“Sheet1!A1:C8”,“Sheet2!A1:B8”}

isAutoPage bool

Whether auto create a single page field. If true,the following param pageFields will be ignored

pageFields PivotPageFields

The pivot page field items.

row int

Row index of the cell in the upper-left corner of the PivotTable report’s destination range.

column int

Column index of the cell in the upper-left corner of the PivotTable report’s destination range.

tableName string

The name of the new PivotTable report.

Returns

int

The new added PivotTable index.

Clear()

Clear all pivot tables.

public void Clear()

Dispose()

Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.

public void Dispose()

Remove(PivotTable)

Deletes the specified PivotTable and delete the PivotTable data

public void Remove(PivotTable pivotTable)

Parameters

pivotTable PivotTable

PivotTable object

Remove(PivotTable, bool)

Deletes the specified PivotTable

public void Remove(PivotTable pivotTable, bool keepData)

Parameters

pivotTable PivotTable

PivotTable object

keepData bool

Whether to keep the PivotTable data

RemoveAt(int)

Deletes the PivotTable at the specified index and delete the PivotTable data

public void RemoveAt(int index)

Parameters

index int

the position index in PivotTable collection

RemoveAt(int, bool)

Deletes the PivotTable at the specified index

public void RemoveAt(int index, bool keepData)

Parameters

index int

the position index in PivotTable collection

keepData bool

Whether to keep the PivotTable data </pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable></pivottable>