How to choose "(blank)" when a criteria does not exist in Pivot Items

Aspose.Cells Product Family
,
1

Hi, appreciate your assistance, I have the following code below

pivotPageFilter = newPivotTable.getPageFields().get(0)
pivotPageFilter.setCurrentPageItem(0)

How do I automatically select “(blank)” if my criteria does not exist in Page Item, and also how to select “(blank)” when multiple criteria does not exist in other Pivot Fields so that it won’t pick up other items in the pivot that isn’t the criteria?

You may use the sample file from my previous thread: Filtering Page of Pivot in Python

2

@myDAN,

Could you please provide a sample Excel file containing your desired “(blank)” as selected in Page area for a pivot table? You may create the file in MS Excel manually. We will check on how to do it via Aspose.Cells API.

1 Like
3

Hi @amjad.sahi please see attached sample file for your reference
Sample.zip (9.5 KB)

4

@myDAN
You can use the following sample code to select “(blank)”:

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

workbook = Workbook("Sample.xlsx")

wsPivot = workbook.getWorksheets().get(0)
pivots = wsPivot.getPivotTables()
pivotCount = pivots.getCount()

for i in range(pivotCount):
    pivot = pivots.get(i)
    pageFields = pivot.getPageFields()
    pageFieldCount = pageFields.getCount()
    for j in range(pageFieldCount):
        pageField = pageFields.get(j)
        pivotItem = pageField.getPivotItems().get("(blank)")

        if pageField.isMultipleItemSelectionAllowed():
            pivotItem.setHidden(False)
        else:
            blankIndex = (int)(pivotItem.getIndex())
            pageField.setCurrentPageItem(blankIndex)

workbook.save("Sample_python.xlsx")

jpype.shutdownJVM()

Hope helps a bit.

1 Like
5

Thank you @John.He for the sample codes, I will try this approach

6

@myDAN
You are welcome. If you have any questions, please feel free to contact us.

1 Like