Benutzerdefiniertes Sortieren in der Pivot Tabelle
Wie man eine benutzerdefinierte Sortierung in Pivot-Tabellen mit Aspose.Cells for Node.js via C++ festlegt
Mit der Aspose.Cells for Node.js via C++ API können Sie Pivot-Tabellen nach Feldwerten sortieren. Der folgende Code lädt die Beispiel-Excel-Datei und fügt drei Pivot-Tabellen hinzu. Die erste Pivot-Tabelle hat keine benutzerdefinierte Sortierung, die zweite ist nach Zeilenwerten „SeaFood“ sortiert, und die dritte ist nach Spaltenwerten „28/07/2000“ sortiert.
Die Beispieldatei und die Ausgabedateien können von hier heruntergeladen werden, um den Beispielcode zu testen:
Beispielcode
const AsposeCells = require("aspose.cells.node"); | |
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
//Source directory | |
var sourceDir = RunExamples.Get_SourceDirectory(); | |
var outputDir = RunExamples.Get_OutputDirectory(); | |
var wb = new AsposeCells.Workbook(sourceDir + "SamplePivotSort.xlsx"); | |
// Obtaining the reference of the newly added worksheet | |
var sheet = wb.getWorksheets().get(0); | |
var pivotTables = sheet.getPivotTables(); | |
// source PivotTable | |
// Adding a PivotTable to the worksheet | |
var index = pivotTables.add("=Sheet1!A1:C10", "E3", "PivotTable2"); | |
//Accessing the instance of the newly added PivotTable | |
var pivotTable = pivotTables.get(index); | |
// Unshowing grand totals for rows. | |
pivotTable.setRowGrand(false); | |
pivotTable.setColumnGrand(false); | |
// Dragging the first field to the row area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, 1); | |
var rowField = pivotTable.getRowFields().get(0); | |
rowField.setIsAutoSort(true); | |
rowField.setIsAscendSort(true); | |
// Dragging the second field to the column area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, 0); | |
var colField = pivotTable.getColumnFields().get(0); | |
colField.setNumberFormat("dd/mm/yyyy"); | |
colField.setIsAutoSort(true); | |
colField.setIsAscendSort(true); | |
// Dragging the third field to the data area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, 2) | |
pivotTable.refreshData(); | |
pivotTable.calculateData(); | |
// end of source PivotTable | |
// sort the PivotTable on "SeaFood" row field values | |
// Adding a PivotTable to the worksheet | |
index = pivotTables.add("=Sheet1!A1:C10", "E10", "PivotTable2"); | |
// Accessing the instance of the newly added PivotTable | |
pivotTable = pivotTables.get(index); | |
// Unshowing grand totals for rows. | |
pivotTable.setRowGrand(false); | |
pivotTable.setColumnGrand(false); | |
// Dragging the first field to the row area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, 1); | |
rowField = pivotTable.getRowFields().get(0); | |
rowField.setIsAutoSort(true); | |
rowField.setIsAscendSort(true); | |
// Dragging the second field to the column area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, 0); | |
colField = pivotTable.getColumnFields().get(0); | |
colField.setNumberFormat("dd/mm/yyyy"); | |
colField.setIsAutoSort(true); | |
colField.setIsAscendSort(true); | |
colField.setAutoSortField(0); | |
//Dragging the third field to the data area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, 2) | |
pivotTable.refreshData(); | |
pivotTable.calculateData(); | |
// end of sort the PivotTable on "SeaFood" row field values | |
// sort the PivotTable on "28/07/2000" column field values | |
// Adding a PivotTable to the worksheet | |
index = pivotTables.add("=Sheet1!A1:C10", "E18", "PivotTable2"); | |
// Accessing the instance of the newly added PivotTable | |
pivotTable = pivotTables.get(index); | |
// Unshowing grand totals for rows. | |
pivotTable.setRowGrand(false); | |
pivotTable.setColumnGrand(false); | |
// Dragging the first field to the row area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, 1); | |
rowField = pivotTable.getRowFields().get(0); | |
rowField.setIsAutoSort(true); | |
rowField.setIsAscendSort(true); | |
rowField.setAutoSortField(0); | |
// Dragging the second field to the column area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, 0); | |
colField = pivotTable.getColumnFields().get(0); | |
colField.setNumberFormat("dd/mm/yyyy"); | |
colField.setIsAutoSort(true); | |
colField.setIsAscendSort(true); | |
//Dragging the third field to the data area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, 2) | |
pivotTable.refreshData(); | |
pivotTable.calculateData(); | |
// end of sort the PivotTable on "28/07/2000" column field values | |
//Saving the Excel file | |
wb.save(outputDir + "out_java.xlsx"); | |
var options = new AsposeCells.PdfSaveOptions(); | |
options.setOnePagePerSheet(true); | |
wb.save(outputDir + "out_java.pdf", options); |