How to collapse a row item in an excel pivot using python

Aspose.Cells Product Family
, , ,
1

Hi, by default when creating pivot tables in Excel every row items are expanded showing the drilldown items in the succeeding row item fields. How do I select a specific row item or multiple row items in a particular row field in the pivot and then collapse the drilldown. Appreciate if you could provide a sample code, thanks.

2

@myDAN
You can find the PivotItem that need to be hidden by traversing the PivotField’s PivotItems collection.

If you are using Aspose.Cells for Python via JAVA, you can control whether to hide details through the PivotItem.setHideDetail(value) method.

If you are using Aspose.Cells for Python via NET, you can use PivotItem.is_hide_detail property to control whether to hide details.

Hope helps a bit.

3

@myDAN
We have created a simple example file. Please refer to the attachment (18.7 KB) for sample files and results.

Sample code for Python via Net:

import aspose.cells
from aspose.cells import Workbook, PdfSaveOptions

book = Workbook("pivot.xlsx")
pivot = book.worksheets[0].pivot_tables[0]
rowField = pivot.row_fields[0]
rowField.pivot_items[0].is_hide_detail = True
pivot.refresh_data_flag = True
pivot.refresh_data()
pivot.calculate_data()
pivot.refresh_data_flag = False
book.save("out_python_net.xlsx")

Hope helps a bit.

4

Hi John,

I tried using Python via Java since this is what we’re using, but I’m having an object has no attribute error kindly correct my code

PivotField = PivotTable.getRowFields().get(0)
PivotItem = PivotField.getPivotItems().get(0)
PivotItem.setHideDetail(True)

Attribute Error message:
‘com.aspose.cells.PivotItem’ object has no attribute ‘setHideDetail’

5

@myDAN
It seems that you are using old versions of Aspose.Cells for Python via Java.
Please try our lastest version Aspose.Cells for Python via Java 23.11.

6

Our version of aspose-cells is 21.6.0, are there similar setHideDetail function in the older versions as a work around or is this function added just recently?

7

@myDAN,

There is no alternative to setHideDetail method, so you have to upgrade to latest APIs set for availing maximum features and other enhancements.