在数据透视表中添加筛选器
Contents
[
Hide
]
可能的使用场景
当您以已知数据创建数据透视表并希望筛选数据透视表时,您需要学习并使用筛选器。 它可以帮助您有效地筛选出所需的数据。 通过使用Aspose.Cells Java API,您可以对数据透视表中的字段值添加筛选器。
在数据透视表中添加筛选器
请参阅以下示例代码。 它设置数据并创建基于数据的数据透视表。 然后,在数据透视表的行字段上添加筛选器。 最后,以 output 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
//Instantiating an Workbook object | |
Workbook workbook = new Workbook(); | |
//Obtaining the reference of the newly added worksheet | |
Worksheet ws = workbook.getWorksheets().get(0); | |
Cells cells = ws.getCells(); | |
//Setting the value to the cells | |
Cell cell = cells.get("A1"); | |
cell.putValue("Fruit"); | |
cell = cells.get("B1"); | |
cell.putValue("Count"); | |
cell = cells.get("A2"); | |
cell.putValue("Apple"); | |
cell = cells.get("A3"); | |
cell.putValue("Mango"); | |
cell = cells.get("A4"); | |
cell.putValue("Blackberry"); | |
cell = cells.get("A5"); | |
cell.putValue("Cherry"); | |
cell = cells.get("A6"); | |
cell.putValue("Guava"); | |
cell = cells.get("A7"); | |
cell.putValue("Carambola"); | |
cell = cells.get("A8"); | |
cell.putValue("Banana"); | |
cell = cells.get("B2"); | |
cell.putValue(5); | |
cell = cells.get("B3"); | |
cell.putValue(3); | |
cell = cells.get("B4"); | |
cell.putValue(6); | |
cell = cells.get("B5"); | |
cell.putValue(4); | |
cell = cells.get("B6"); | |
cell.putValue(5); | |
cell = cells.get("B7"); | |
cell.putValue(2); | |
cell = cells.get("B8"); | |
cell.putValue(20); | |
//Adding a PivotTable to the worksheet | |
int i = ws.getPivotTables().add("=A1:B8", "D10", "PivotTable1"); | |
//Accessing the instance of the newly added PivotTable | |
PivotTable pivotTable = ws.getPivotTables().get(i); | |
pivotTable.addFieldToArea(PivotFieldType.ROW, 0); | |
pivotTable.addFieldToArea(PivotFieldType.DATA, "Count"); | |
pivotTable.getDataFields().get(0).setFunction(ConsolidationFunction.SUM); | |
PivotField field = pivotTable.getRowFields().get(0); | |
field.setAutoSort(true); | |
field.setAscendSort(false); | |
field.setAutoSortField(0); | |
//Add top10 filter | |
PivotField filterField = pivotTable.getRowFields().get(0); | |
filterField.filterTop10(0, PivotFilterType.COUNT, false, 5); | |
pivotTable.refreshData(); | |
pivotTable.calculateData(); | |
workbook.save("out.xlsx"); |