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

MethodDescription
add(PivotTable pivotTable, int row, int column, String tableName)Adds a new PivotTable Object to the collection from another PivotTable.
add(PivotTable pivotTable, String destCellName, String tableName)Adds a new PivotTable Object to the collection from 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 cache to a PivotCaches collection.
add(String sourceData, int row, int column, String tableName, boolean useSameSource)Adds a new PivotTable cache to a PivotCaches collection.
add(String sourceData, String destCellName, String tableName)Adds a new PivotTable cache to a PivotCaches collection.
add(String sourceData, String destCellName, String tableName, boolean useSameSource)Adds a new PivotTable cache to a PivotCaches collection.
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 Object to the collection from another PivotTable.

Parameters:

ParameterTypeDescription
pivotTablePivotTableThe source pivotTable.
rowintRow index of the cell in the upper-left corner of the PivotTable report’s destination range.
columnintColumn index of the cell in the upper-left corner of the PivotTable report’s destination range.
tableNamejava.lang.StringThe 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 Object to the collection from another PivotTable.

Parameters:

ParameterTypeDescription
pivotTablePivotTableThe source pivotTable.
destCellNamejava.lang.StringThe cell in the upper-left corner of the PivotTable report’s destination range.
tableNamejava.lang.StringThe 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:

ParameterTypeDescription
ojava.lang.ObjectThe 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 cache to a PivotCaches collection.

Parameters:

ParameterTypeDescription
sourceDatajava.lang.StringThe data cell range for the new PivotTable.Example : Sheet1!A1:C8
rowintRow index of the cell in the upper-left corner of the PivotTable report’s destination range.
columnintColumn index of the cell in the upper-left corner of the PivotTable report’s destination range.
tableNamejava.lang.StringThe name of the new PivotTable report.

Returns: int - The new added cache index.

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

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

Adds a new PivotTable cache to a PivotCaches collection.

Parameters:

ParameterTypeDescription
sourceDatajava.lang.StringThe data cell range for the new PivotTable.Example : Sheet1!A1:C8
rowintRow index of the cell in the upper-left corner of the PivotTable report’s destination range.
columnintColumn index of the cell in the upper-left corner of the PivotTable report’s destination range.
tableNamejava.lang.StringThe name of the new PivotTable report.
useSameSourcebooleanIndicates 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 sourceData, String destCellName, String tableName)

public int add(String sourceData, String destCellName, String tableName)

Adds a new PivotTable cache to a PivotCaches collection.

Parameters:

ParameterTypeDescription
sourceDatajava.lang.StringThe data for the new PivotTable cache.
destCellNamejava.lang.StringThe cell in the upper-left corner of the PivotTable report’s destination range.
tableNamejava.lang.StringThe name of the new PivotTable report.

Returns: int - The new added cache index.

add(String sourceData, String destCellName, String tableName, boolean useSameSource)

public int add(String sourceData, String destCellName, String tableName, boolean useSameSource)

Adds a new PivotTable cache to a PivotCaches collection.

Parameters:

ParameterTypeDescription
sourceDatajava.lang.StringThe data for the new PivotTable cache.
destCellNamejava.lang.StringThe cell in the upper-left corner of the PivotTable report’s destination range.
tableNamejava.lang.StringThe name of the new PivotTable report.
useSameSourcebooleanIndicates 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[] sourceData, boolean isAutoPage, PivotPageFields pageFields, int row, int column, String tableName)

public int 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.

Parameters:

ParameterTypeDescription
sourceDatajava.lang.String[]The multiple consolidation ranges,such as {“Sheet1!A1:C8”,“Sheet2!A1:B8”}
isAutoPagebooleanWhether auto create a single page field. If true,the following param pageFields will be ignored
pageFieldsPivotPageFieldsThe pivot page field items.
rowintRow index of the cell in the upper-left corner of the PivotTable report’s destination range.
columnintColumn index of the cell in the upper-left corner of the PivotTable report’s destination range.
tableNamejava.lang.StringThe name of the new PivotTable report.

Returns: int - The new added PivotTable index.

add(String[] sourceData, boolean isAutoPage, PivotPageFields pageFields, String destCellName, String tableName)

public int 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.

Parameters:

ParameterTypeDescription
sourceDatajava.lang.String[]The multiple consolidation ranges,such as {“Sheet1!A1:C8”,“Sheet2!A1:B8”}
isAutoPagebooleanWhether auto create a single page field. If true,the following param pageFields will be ignored.
pageFieldsPivotPageFieldsThe pivot page field items.
destCellNamejava.lang.StringdestCellName The name of the new PivotTable report.
tableNamejava.lang.Stringthe name of the new PivotTable report.

Returns: int - The new added PivotTable index.

clear()

public void clear()

Clear all pivot tables.

contains(Object o)

public boolean contains(Object o)

Return whether instance contains this object

Parameters:

ParameterTypeDescription
ojava.lang.Objecttest object

Returns: boolean - Whether instance contains this object

dispose()

public void dispose()

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

equals(Object arg0)

public boolean equals(Object arg0)

Parameters:

ParameterTypeDescription
arg0java.lang.Object

Returns: boolean

get(int index)

public PivotTable get(int index)

Gets the PivotTable report by index.

Parameters:

ParameterTypeDescription
indexint

Returns: PivotTable

get(int row, int column)

public PivotTable get(int row, int column)

Gets the PivotTable report by pivottable’s position.

Parameters:

ParameterTypeDescription
rowint
columnint

Returns: PivotTable

get(String name)

public PivotTable get(String name)

Gets the PivotTable report by pivottable’s name.

Parameters:

ParameterTypeDescription
namejava.lang.String

Returns: PivotTable

getClass()

public final native Class<?> getClass()

Returns: java.lang.Class

getCount()

public int getCount()

Gets the number of elements contained in the CollectionBase instance.

Returns: int - The number of elements contained in the CollectionBase instance.

hashCode()

public native int hashCode()

Returns: int

indexOf(Object o)

public int indexOf(Object o)

Determines the index of a specific item in the CollectionBase instance.

Parameters:

ParameterTypeDescription
ojava.lang.ObjectDetermines the index of a specific item in the CollectionBase instance.

Returns: int - The index of value if found in the list; otherwise, -1.

iterator()

public Iterator iterator()

Returns an enumerator that iterates through the CollectionBase instance.

Returns: java.util.Iterator - An iterator for the CollectionBase instance.

notify()

public final native void notify()

notifyAll()

public final native void notifyAll()

remove(PivotTable pivotTable)

public void remove(PivotTable pivotTable)

Deletes the specified PivotTable and delete the PivotTable data

Parameters:

ParameterTypeDescription
pivotTablePivotTablePivotTable object

remove(PivotTable pivotTable, boolean keepData)

public void remove(PivotTable pivotTable, boolean keepData)

Deletes the specified PivotTable

Parameters:

ParameterTypeDescription
pivotTablePivotTablePivotTable object
keepDatabooleanWhether to keep the PivotTable data

removeAt(int index)

public void removeAt(int index)

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

Parameters:

ParameterTypeDescription
indexintthe position index in PivotTable collection

removeAt(int index, boolean keepData)

public void removeAt(int index, boolean keepData)

Deletes the PivotTable at the specified index

Parameters:

ParameterTypeDescription
indexintthe position index in PivotTable collection
keepDatabooleanWhether to keep the PivotTable data

toString()

public String toString()

Returns: java.lang.String

wait()

public final void wait()

wait(long arg0)

public final native void wait(long arg0)

Parameters:

ParameterTypeDescription
arg0long

wait(long arg0, int arg1)

public final void wait(long arg0, int arg1)

Parameters:

ParameterTypeDescription
arg0long
arg1int