PivotTableCollection

Inheritance: java.lang.Object, com.aspose.cells.CollectionBase

public class PivotTableCollection extends CollectionBase

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

Example

         Workbook book = new Workbook();
         Worksheet sheet = book.getWorksheets().get(0);
         Cells cells = sheet.getCells();
         cells.get(0, 0).setValue("fruit");
         cells.get(1, 0).setValue("grape");
         cells.get(2, 0).setValue("blueberry");
         cells.get(3, 0).setValue("kiwi");
         cells.get(4, 0).setValue("cherry");
         cells.get(5, 0).setValue("grape");
         cells.get(6, 0).setValue("blueberry");
         cells.get(7, 0).setValue("kiwi");
         cells.get(8, 0).setValue("cherry");
 
         cells.get(0, 1).setValue("year");
         cells.get(1, 1).setValue(2020);
         cells.get(2, 1).setValue(2020);
         cells.get(3, 1).setValue(2020);
         cells.get(4, 1).setValue(2020);
         cells.get(5, 1).setValue(2021);
         cells.get(6, 1).setValue(2021);
         cells.get(7, 1).setValue(2021);
         cells.get(8, 1).setValue(2021);
 
         cells.get(0, 2).setValue("amount");
         cells.get(1, 2).setValue(50);
         cells.get(2, 2).setValue(60);
         cells.get(3, 2).setValue(70);
         cells.get(4, 2).setValue(80);
         cells.get(5, 2).setValue(90);
         cells.get(6, 2).setValue(100);
         cells.get(7, 2).setValue(110);
         cells.get(8, 2).setValue(120);
 
         PivotTableCollection pivots = sheet.getPivotTables();
 
         int pivotIndex = pivots.add("=Sheet1!A1:C9", "A12", "TestPivotTable");
         PivotTable pivot = pivots.get(pivotIndex);
         pivot.addFieldToArea(PivotFieldType.ROW, "fruit");
         pivot.addFieldToArea(PivotFieldType.COLUMN, "year");
         pivot.addFieldToArea(PivotFieldType.DATA, "amount");
 
         pivot.setPivotTableStyleType(PivotTableStyleType.PIVOT_TABLE_STYLE_MEDIUM_10);
 
         //Change PivotField's attributes
         PivotField rowField = pivot.getRowFields().get(0);
         rowField.setDisplayName("custom display name");
 
         //Add PivotFilter
         int index = pivot.getPivotFilters().add(0, PivotFilterType.COUNT);
         PivotFilter filter = pivot.getPivotFilters().get(index);
         filter.getAutoFilter().filterTop10(0, false, false, 2);
 
         //Add PivotFormatCondition
         int formatIndex = pivot.getPivotFormatConditions().add();
         PivotFormatCondition pfc = pivot.getPivotFormatConditions().get(formatIndex);
         FormatConditionCollection fcc = pfc.getFormatConditions();
         fcc.addArea(pivot.getDataBodyRange());
         int idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
         FormatCondition fc = fcc.get(idx);
         fc.setFormula1("100");
         fc.setOperator(OperatorType.GREATER_OR_EQUAL);
         fc.getStyle().setBackgroundColor(Color.getRed());
 
         pivot.refreshData();
         pivot.calculateData();
 
         //do your business
 
         book.save("out.xlsx");

Methods

Method	Description
add(PivotTable pivotTable, int row, int column, String tableName)	Adds a new PivotTable based on another PivotTable.
add(PivotTable pivotTable, String destCellName, String tableName)	Adds a new PivotTable based on another PivotTable.
add(Object o)	Adds an item to the CollectionBase instance.
add(String sourceData, int row, int column, String tableName)	Adds a new PivotTable.
add(String sourceData, int row, int column, String tableName, boolean useSameSource)	Adds a new PivotTable.
add(String sourceData, int row, int column, String tableName, boolean useSameSource, boolean isXlsClassic)	Adds a new PivotTable.
add(String sourceData, String destCellName, String tableName)	Adds a new PivotTable.
add(String sourceData, String destCellName, String tableName, boolean useSameSource)	Adds a new PivotTable.
add(String sourceData, String cell, String tableName, boolean useSameSource, boolean isXlsClassic)	Adds a new PivotTable.
add(String[] sourceData, boolean isAutoPage, PivotPageFields pageFields, int row, int column, String tableName)	Adds a new PivotTable Object to the collection with multiple consolidation ranges as data source.
add(String[] sourceData, boolean isAutoPage, PivotPageFields pageFields, String destCellName, String tableName)	Adds a new PivotTable Object to the collection with multiple consolidation ranges as data source.
clear()	Clear all pivot tables.
contains(Object o)	Return whether instance contains this object
dispose()	Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
equals(Object arg0)
get(int index)	Gets the PivotTable report by index.
get(int row, int column)	Gets the PivotTable report by pivottable’s position.
get(String name)	Gets the PivotTable report by pivottable’s name.
getClass()
getCount()	Gets the number of elements contained in the CollectionBase instance.
hashCode()
indexOf(Object o)	Determines the index of a specific item in the CollectionBase instance.
iterator()	Returns an enumerator that iterates through the CollectionBase instance.
notify()
notifyAll()
remove(PivotTable pivotTable)	Deletes the specified PivotTable and delete the PivotTable data
remove(PivotTable pivotTable, boolean keepData)	Deletes the specified PivotTable
removeAt(int index)	Deletes the PivotTable at the specified index and delete the PivotTable data
removeAt(int index, boolean keepData)	Deletes the PivotTable at the specified index
toString()
wait()
wait(long arg0)
wait(long arg0, int arg1)

add(PivotTable pivotTable, int row, int column, String tableName)

public int add(PivotTable pivotTable, int row, int column, String tableName)

Adds a new PivotTable based on another PivotTable.

Parameters:

Parameter	Type	Description
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	java.lang.String	The name of the new PivotTable report.

Returns: int - The new added PivotTable index.

add(PivotTable pivotTable, String destCellName, String tableName)

public int add(PivotTable pivotTable, String destCellName, String tableName)

Adds a new PivotTable based on another PivotTable.

Parameters:

Parameter	Type	Description
pivotTable	PivotTable	The source pivotTable.
destCellName	java.lang.String	The cell in the upper-left corner of the PivotTable report’s destination range.
tableName	java.lang.String	The name of the new PivotTable report.

Returns: int - The new added PivotTable index.

add(Object o)

public int add(Object o)

Adds an item to the CollectionBase instance.

Parameters:

Parameter	Type	Description
o	java.lang.Object	The Object to add to the CollectionBase instance.

Returns: int - The position into which the new element was inserted.

add(String sourceData, int row, int column, String tableName)

public int add(String sourceData, int row, int column, String tableName)

Adds a new PivotTable.

Parameters:

Parameter	Type	Description
sourceData	java.lang.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	java.lang.String	The name of the new PivotTable report.