清除数据透视表中的筛选器
Contents
[
Hide
]
可能的使用场景
当您使用已知数据创建数据透视表并希望筛选数据透视表时,您需要学习和使用筛选器。使用Aspose.Cells for Python via .NET API,您可以在数据透视表中操作字段值上的筛选器。
如何在Excel中清除数据透视表中的筛选器
在 Excel 中清除数据透视表中的筛选,按照以下步骤操作:
- 选择要清除筛选的数据透视表。
- 单击数据透视表中要清除筛选的下拉箭头。
- 从下拉菜单中选择“清除筛选”。
- 如果您要清除数据透视表中的所有筛选,还可以在 Excel 的“数据透视表分析”选项卡上单击“清除筛选”按钮。
如何使用Aspose.Cells for Python Excel库清除数据透视表中的筛选器
使用Aspose.Cells for Python via .NET清除数据透视表中的筛选器。请参阅以下示例代码。
- 设置数据并创建基于该数据的数据透视表。
- 在数据透视表的行字段上添加筛选。
- 以 output XLSX 格式保存工作簿。执行示例代码后,将在工作表中添加带有 top10 筛选的数据透视表。
- 清除特定数据透视字段上的筛选。执行清除筛选的代码后,将清除特定数据透视字段上的筛选。请检查 output XLSX。
示例代码
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(field.base_index, 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_add.xlsx") | |
# Clear PivotFilter from the specific PivotField | |
pivotTable.pivot_filters.clear_filter(field.base_index) | |
pivotTable.refresh_data() | |
pivotTable.calculate_data() | |
workbook.save("out_delete.xlsx") |