Pivot Table Hide and Sort data

Pivot Table Hide and Sort data Using Aspose.Cells for Python Excel Library

Aspose.Cells for Python via .NET supports hiding and sorting data in the pivot table. The following code snippet demonstrates this feature by sorting the Score column in descending order and then hiding the rows with a score less than 60.

from aspose.cells import Workbook

#  directories
sourceDir = RunExamples.Get_SourceDirectory()
outputDir = RunExamples.Get_OutputDirectory()
workbook = Workbook(sourceDir + "PivotTableHideAndSortSample.xlsx")
worksheet = workbook.worksheets[0]
pivotTable = worksheet.pivot_tables[0]
dataBodyRange = pivotTable.data_body_range
currentRow = 3
rowsUsed = dataBodyRange.end_row
#  Sorting score in descending
field = pivotTable.row_fields[0]
field.is_auto_sort = True
field.is_ascend_sort = False
field.auto_sort_field = 0
pivotTable.refresh_data()
pivotTable.calculate_data()
#  Hiding rows with score less than 60
while currentRow < rowsUsed:
    cell = worksheet.cells.get(currentRow, 1)
    score = float(cell.value)
    if score < 60:
        worksheet.cells.hide_row(currentRow)
    currentRow
    currentRow = currentRow + 1
pivotTable.refresh_data()
pivotTable.calculate_data()
#  Saving the Excel file
workbook.save(outputDir + "PivotTableHideAndSort_out.xlsx")

The source and output excel files used in the code snippet are attached for reference.

Source File

Output File