Filtering Page of Pivot in Python

Aspose.Cells Product Family
, ,
1

Hi, appreciate your help.

I have a file with sample pivots in the attached
Sample.zip (10.2 KB)

And I have python codes below to try and replicate one of the pivot tables

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook, PuvotFieldType

workbook = Workbook(insert Filepath)

wsPivotTable = workbook.getWorksheets().get(0)
pivotTablesCollection = wsPivotTable.getPivotTables()
pivotTableIndex = pivotTablesCollection.add(“=A1:C5”, “E1”, “PythonPivotTable”)
newPivotTable = pivotTablesCollection.get(pivotTableIndex)
newPivotTable.setRowGrand(False)
newPivotTable.addFieldToArea(PivotFieldType.ROW, 1)
newPivotTable.addFieldToArea(PivotFieldType.DATA, 2)
newPivotTable.addFieldToArea(PivotFieldType.PAGE, 0)

workbook.save(insert Filepath)

jpype.shutdownJVM()

The pivot page filter is defaulted to “ALL”, how do I set the page filter similar to the attached for both single and multiple select filters?

2

@myDAN,

Thanks for the sample Excel containing your desired pivot tables with filters.

Please see the following sample code to accomplish your tasks for your reference.
e.g.
Sample code:

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook, CellsHelper, CalculationOptions, SaveFormat, PdfSaveOptions, PivotFieldType, PivotTableStyleType

workbook = Workbook("Sample.xlsx")

wsPivotTable = workbook.getWorksheets().get(0)
pivotTablesCollection = wsPivotTable.getPivotTables()
pivotTableIndex = pivotTablesCollection.add("=A1:C5", "F12", "PythonPivotTable1")
newPivotTable = pivotTablesCollection.get(pivotTableIndex)
newPivotTable.setRowGrand(False)
newPivotTable.addFieldToArea(PivotFieldType.ROW, 1)
newPivotTable.addFieldToArea(PivotFieldType.DATA, 2)
newPivotTable.addFieldToArea(PivotFieldType.PAGE, 0)

newPivotTable.setPivotTableStyleType(PivotTableStyleType.PIVOT_TABLE_STYLE_LIGHT_16)

pivotField = newPivotTable.getPageFields().get(0)
pageItemCount = pivotField.getPivotItems().getCount();

# Single filter - Select first item ("A")
# pivotField.setCurrentPageItem(0);

# Multiple select filters - Select all items except "A"
for i in range(pageItemCount):
   item = pivotField.getPivotItems().get(i).getName()    
   if item == "A":
     pivotField.getPivotItems().get(i).setHidden(True)
   else:
     pivotField.getPivotItems().get(i).setHidden(False)

workbook.save("out1.xlsx")

jpype.shutdownJVM()

Hope, this helps a bit.

1 Like
3

Thank you! This is exactly what I needed

4

@myDAN,

Good to know that the suggested code segment works for your needs well. Please feel free to contact us any time if you have further queries or comments, we will be happy to assist you soon.