Add Filter in Pivot Table
Contents
[
Hide
]
Possible Usage Scenarios
When you create a pivot table with known data and want to filter the pivot table, you need to learn and use filter. It can help you filter out the data you want effectively. By using the Aspose.Cells for Python via .NET API, you can add filter on field values in Pivot Tables.
Add Filter in Pivot Table Using Aspose.Cells for Python Excel Library
Please see the following sample code. It sets the data and creates a PivotTable based on it. Then add a filter on the row field of the pivot table. Finally, it saves the workbook in output XLSX format. After executing the example code, a pivot table with top10 filter is added to the worksheet.
Sample Code
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from aspose.cells import ConsolidationFunction, Workbook | |
| from aspose.cells.pivot import PivotFieldType, PivotFilterType | |
| # Instantiating an Workbook object | |
| workbook = Workbook() | |
| # Obtaining the reference of the newly added worksheet | |
| ws = workbook.worksheets[0] | |
| cells = ws.cells | |
| # Setting the value to the cells | |
| cell = cells.get("A1") | |
| cell.put_value("Fruit") | |
| cell = cells.get("B1") | |
| cell.put_value("Count") | |
| cell = cells.get("A2") | |
| cell.put_value("Apple") | |
| cell = cells.get("A3") | |
| cell.put_value("Mango") | |
| cell = cells.get("A4") | |
| cell.put_value("Blackberry") | |
| cell = cells.get("A5") | |
| cell.put_value("Cherry") | |
| cell = cells.get("A6") | |
| cell.put_value("Guava") | |
| cell = cells.get("A7") | |
| cell.put_value("Carambola") | |
| cell = cells.get("A8") | |
| cell.put_value("Banana") | |
| cell = cells.get("B2") | |
| cell.put_value(5) | |
| cell = cells.get("B3") | |
| cell.put_value(3) | |
| cell = cells.get("B4") | |
| cell.put_value(6) | |
| cell = cells.get("B5") | |
| cell.put_value(4) | |
| cell = cells.get("B6") | |
| cell.put_value(5) | |
| cell = cells.get("B7") | |
| cell.put_value(2) | |
| cell = cells.get("B8") | |
| cell.put_value(20) | |
| # Adding a PivotTable to the worksheet | |
| i = ws.pivot_tables.add("=A1:B8", "D10", "PivotTable1") | |
| # Accessing the instance of the newly added PivotTable | |
| pivotTable = ws.pivot_tables[i] | |
| pivotTable.add_field_to_area(PivotFieldType.ROW, 0) | |
| pivotTable.add_field_to_area(PivotFieldType.DATA, "Count") | |
| pivotTable.data_fields[0].function = ConsolidationFunction.SUM | |
| field = pivotTable.row_fields[0] | |
| field.is_auto_sort = True | |
| field.is_ascend_sort = False | |
| field.auto_sort_field = 0 | |
| # Add top10 filter | |
| index = pivotTable.pivot_filters.add(0, PivotFilterType.COUNT) | |
| filter = pivotTable.pivot_filters[index] | |
| filter.auto_filter.filter_top10(0, True, False, 5) | |
| pivotTable.refresh_data() | |
| pivotTable.calculate_data() | |
| workbook.save("out.xlsx") |