在数据透视表中添加筛选器
Contents
[
Hide
]
可能的使用场景
当你使用已知数据创建数据透视表并想筛选透视表时，你需要学习并使用筛选器。它可以帮助你有效筛选出所需数据。通过使用Aspose.Cells for Java脚本的C++ API，你可以在数据透视表的字段值上添加筛选器。
使用Aspose.Cells for Java脚本通过C++在数据透视表中添加筛选器
请参阅以下示例代码。它设置数据并基于它创建数据透视表。然后在数据透视表的行字段上添加筛选器。最后，以[filterout.xlsx]格式保存工作簿。执行示例代码后，工作表中将添加一个带有Top10筛选器的数据透视表。
示例代码
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells PivotTable Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Worksheet, Cell, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
// Creating a new Workbook object
const workbook = new Workbook();
// Obtaining the reference of the newly added worksheet
const ws = workbook.worksheets.get(0);
const cells = ws.cells;
// Setting the value to the cells (converted putValue -> value)
let cell = cells.get("A1");
cell.value = "Fruit";
cell = cells.get("B1");
cell.value = "Count";
cell = cells.get("A2");
cell.value = "Apple";
cell = cells.get("A3");
cell.value = "Mango";
cell = cells.get("A4");
cell.value = "Blackberry";
cell = cells.get("A5");
cell.value = "Cherry";
cell = cells.get("A6");
cell.value = "Guava";
cell = cells.get("A7");
cell.value = "Carambola";
cell = cells.get("A8");
cell.value = "Banana";
cell = cells.get("B2");
cell.value = 5;
cell = cells.get("B3");
cell.value = 3;
cell = cells.get("B4");
cell.value = 6;
cell = cells.get("B5");
cell.value = 4;
cell = cells.get("B6");
cell.value = 5;
cell = cells.get("B7");
cell.value = 2;
cell = cells.get("B8");
cell.value = 20;
// Adding a PivotTable to the worksheet (converted getPivotTables -> pivotTables)
const i = ws.pivotTables.add("=A1:B8", "D10", "PivotTable1");
// Accessing the instance of the newly added PivotTable
const pivotTable = ws.pivotTables.get(i);
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, 0);
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Count");
// Converted getDataFields().get(0).setFunction(...) -> dataFields.get(0).function = ...
pivotTable.dataFields.get(0).function = AsposeCells.ConsolidationFunction.Sum;
// Converted getRowFields().get(0) -> rowFields.get(0)
const field = pivotTable.rowFields.get(0);
// Converted setIsAutoSort(true) -> isAutoSort = true
field.isAutoSort = true;
// Converted setIsAscendSort(false) -> isAscendSort = false
field.isAscendSort = false;
// Converted setAutoSortField(0) -> autoSortField = 0
field.autoSortField = 0;
// Add top10 filter (converted getPivotFilters -> pivotFilters and getAutoFilter -> autoFilter)
const index = pivotTable.pivotFilters.add(0, AsposeCells.PivotFilterType.Count);
const filter = pivotTable.pivotFilters.get(index);
filter.autoFilter.filterTop10(0, true, false, 5);
pivotTable.refreshData();
pivotTable.calculateData();
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'out.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">PivotTable created successfully! Click the download link to get the file.</p>';
});
</script>
</html>