在数据透视表中添加筛选器
Contents
[
Hide
]
可能的使用场景
当你创建了一个带有已知数据的数据透视表,并且想要筛选数据透视表时,你需要学习并使用筛选器。它可以帮助你有效地筛选出你想要的数据。通过使用Aspose.Cells for Python via .NET API,你可以在数据透视表中的字段值上添加筛选器。
使用Aspose.Cells for Python Excel库在数据透视表中添加筛选器。
请参阅以下示例代码。它设置数据并基于它创建数据透视表。然后在数据透视表的行字段上添加筛选器。最后,以[filterout.xlsx]格式保存工作簿。执行示例代码后,工作表中将添加一个带有Top10筛选器的数据透视表。
示例代码
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") |