Erstellen von Pivot Tabellen und Pivot Diagrammen
Eine Pivot-Tabelle ist eine interaktive Zusammenfassung von Datensätzen. Sie können beispielsweise Hunderte von Rechnungseinträgen in einer Liste in einem Arbeitsblatt haben. Eine Pivot-Tabelle kann die Rechnungen nach Kunden, Produkt oder Datum zusammenfassen. Mit Microsoft Excel ist es möglich, die Informationen in der Pivot-Tabelle schnell neu anzuordnen, indem Sie Schaltflächen an eine neue Position ziehen.
Ein Pivot-Diagramm ist eine interaktive grafische Darstellung der Daten in einer Pivot-Tabelle. Pivot-Diagramme wurden in Excel 2000 eingeführt. Durch die Verwendung eines Pivot-Diagramms wird es noch einfacher, die Daten zu verstehen, da die Pivot-Tabelle automatisch Teil- und Gesamtsummen erstellt.
Aspose.Cells for Node.js via C++ unterstützt Pivot-Tabellen und Pivot-Diagramme.
Pivot-Tabellen und Diagramme mit Aspose.Cells for Node.js via C++ hinzufügen
Aspose.Cells for Node.js via C++ bietet eine spezielle Klasse zur Erstellung von Pivot-Tabellen. Diese Klassen werden verwendet, um PivotTable-Objekte zu erstellen und einzustellen, die als Grundbausteine eines PivotTable-Objekts dienen:
- PivotField, ein Feld in einem Pivot-Tabellenbericht.
- PivotFields, eine Sammlung aller PivotField-Objekte in einer Pivot-Tabelle.
- PivotTable, ein Pivot-Tabellenbericht auf einem Arbeitsblatt.
- PivotTables, eine Sammlung aller PivotTable-Objekte auf dem Arbeitsblatt.
Vorbereitung der Verwendung von Aspose.Cells for Node.js via C++
- Installieren Sie Aspose.Cells for Node.js via C++ aus NPM, verwenden Sie den Befehl: $ npm install aspose.cells.node.
- Sie können auch die Schritt-für-Schritt-Anleitung zur Installation von “Aspose.Cells for Node.js via C++” in Ihrer Entwicklerumgebung befolgen.
So fügen Sie eine Pivot-Tabelle mit Aspose.Cells for Node.js via C++ hinzu
Um eine Pivot-Tabelle mit Aspose.Cells for Node.js via C++ zu erstellen:
- Fügen Sie einige Daten zu Zellen auf einem Arbeitsblatt hinzu, indem Sie die put_value-Methode eines Cell-Objekts verwenden. Sie verwenden auch eine Vorlagendatei, die bereits mit Daten gefüllt ist. Die Daten werden als Datenquelle der Pivot-Tabelle verwendet.
- Fügen Sie dem Arbeitsblatt eine Pivot-Tabelle hinzu, indem Sie die Methode add der PivotTables-Sammlung aufrufen (die im Arbeitsblatt-Objekt gekapselt ist).
- Greifen Sie auf das neue PivotTable-Objekt aus der PivotTables-Sammlung zu, indem Sie seinen Index übergeben. # Verwenden Sie eines der in dem PivotTable-Objekt gekapselten Pivot-Tabellenobjekte, um die Tabelle zu verwalten.
Codebeispiele finden Sie unten.
const AsposeCells = require("aspose.cells.node"); | |
//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
//The path to the documents directory. | |
var dataDir = RunExamples.GetDataDir(".");; | |
//Instantiating an Workbook object | |
var workbook = new AsposeCells.Workbook(); | |
//Obtaining the reference of the first worksheet | |
var sheet = workbook.getWorksheets().get(0); | |
//Name the sheet | |
sheet.setName("Data");; | |
var cells = sheet.getCells(); | |
//Setting the values to the cells | |
var cell = cells.get("A1"); | |
cell.putValue("Employee"); | |
cell = cells.get("B1"); | |
cell.putValue("Quarter"); | |
cell = cells.get("C1"); | |
cell.putValue("Product"); | |
cell = cells.get("D1"); | |
cell.putValue("Continent"); | |
cell = cells.get("E1"); | |
cell.putValue("Country"); | |
cell = cells.get("F1"); | |
cell.putValue("Sale"); | |
cell = cells.get("A2"); | |
cell.putValue("David"); | |
cell = cells.get("A3"); | |
cell.putValue("David"); | |
cell = cells.get("A4"); | |
cell.putValue("David"); | |
cell = cells.get("A5"); | |
cell.putValue("David"); | |
cell = cells.get("A6"); | |
cell.putValue("James"); | |
cell = cells.get("A7"); | |
cell.putValue("James"); | |
cell = cells.get("A8"); | |
cell.putValue("James"); | |
cell = cells.get("A9"); | |
cell.putValue("James"); | |
cell = cells.get("A10"); | |
cell.putValue("James"); | |
cell = cells.get("A11"); | |
cell.putValue("Miya"); | |
cell = cells.get("A12"); | |
cell.putValue("Miya"); | |
cell = cells.get("A13"); | |
cell.putValue("Miya"); | |
cell = cells.get("A14"); | |
cell.putValue("Miya"); | |
cell = cells.get("A15"); | |
cell.putValue("Miya"); | |
cell = cells.get("A16"); | |
cell.putValue("Miya"); | |
cell = cells.get("A17"); | |
cell.putValue("Miya"); | |
cell = cells.get("A18"); | |
cell.putValue("Elvis"); | |
cell = cells.get("A19"); | |
cell.putValue("Elvis"); | |
cell = cells.get("A20"); | |
cell.putValue("Elvis"); | |
cell = cells.get("A21"); | |
cell.putValue("Elvis"); | |
cell = cells.get("A22"); | |
cell.putValue("Elvis"); | |
cell = cells.get("A23"); | |
cell.putValue("Elvis"); | |
cell = cells.get("A24"); | |
cell.putValue("Elvis"); | |
cell = cells.get("A25"); | |
cell.putValue("Jean"); | |
cell = cells.get("A26"); | |
cell.putValue("Jean"); | |
cell = cells.get("A27"); | |
cell.putValue("Jean"); | |
cell = cells.get("A28"); | |
cell.putValue("Ada"); | |
cell = cells.get("A29"); | |
cell.putValue("Ada"); | |
cell = cells.get("A30"); | |
cell.putValue("Ada"); | |
cell = cells.get("B2"); | |
cell.putValue("1"); | |
cell = cells.get("B3"); | |
cell.putValue("2"); | |
cell = cells.get("B4"); | |
cell.putValue("3"); | |
cell = cells.get("B5"); | |
cell.putValue("4"); | |
cell = cells.get("B6"); | |
cell.putValue("1"); | |
cell = cells.get("B7"); | |
cell.putValue("2"); | |
cell = cells.get("B8"); | |
cell.putValue("3"); | |
cell = cells.get("B9"); | |
cell.putValue("4"); | |
cell = cells.get("B10"); | |
cell.putValue("4"); | |
cell = cells.get("B11"); | |
cell.putValue("1"); | |
cell = cells.get("B12"); | |
cell.putValue("1"); | |
cell = cells.get("B13"); | |
cell.putValue("2"); | |
cell = cells.get("B14"); | |
cell.putValue("2"); | |
cell = cells.get("B15"); | |
cell.putValue("3"); | |
cell = cells.get("B16"); | |
cell.putValue("4"); | |
cell = cells.get("B17"); | |
cell.putValue("4"); | |
cell = cells.get("B18"); | |
cell.putValue("1"); | |
cell = cells.get("B19"); | |
cell.putValue("1"); | |
cell = cells.get("B20"); | |
cell.putValue("2"); | |
cell = cells.get("B21"); | |
cell.putValue("3"); | |
cell = cells.get("B22"); | |
cell.putValue("3"); | |
cell = cells.get("B23"); | |
cell.putValue("4"); | |
cell = cells.get("B24"); | |
cell.putValue("4"); | |
cell = cells.get("B25"); | |
cell.putValue("1"); | |
cell = cells.get("B26"); | |
cell.putValue("2"); | |
cell = cells.get("B27"); | |
cell.putValue("3"); | |
cell = cells.get("B28"); | |
cell.putValue("1"); | |
cell = cells.get("B29"); | |
cell.putValue("2"); | |
cell = cells.get("B30"); | |
cell.putValue("3"); | |
cell = cells.get("C2"); | |
cell.putValue("Maxilaku"); | |
cell = cells.get("C3"); | |
cell.putValue("Maxilaku"); | |
cell = cells.get("C4"); | |
cell.putValue("Chai"); | |
cell = cells.get("C5"); | |
cell.putValue("Maxilaku"); | |
cell = cells.get("C6"); | |
cell.putValue("Chang"); | |
cell = cells.get("C7"); | |
cell.putValue("Chang"); | |
cell = cells.get("C8"); | |
cell.putValue("Chang"); | |
cell = cells.get("C9"); | |
cell.putValue("Chang"); | |
cell = cells.get("C10"); | |
cell.putValue("Chang"); | |
cell = cells.get("C11"); | |
cell.putValue("Geitost"); | |
cell = cells.get("C12"); | |
cell.putValue("Chai"); | |
cell = cells.get("C13"); | |
cell.putValue("Geitost"); | |
cell = cells.get("C14"); | |
cell.putValue("Geitost"); | |
cell = cells.get("C15"); | |
cell.putValue("Maxilaku"); | |
cell = cells.get("C16"); | |
cell.putValue("Geitost"); | |
cell = cells.get("C17"); | |
cell.putValue("Geitost"); | |
cell = cells.get("C18"); | |
cell.putValue("Ikuru"); | |
cell = cells.get("C19"); | |
cell.putValue("Ikuru"); | |
cell = cells.get("C20"); | |
cell.putValue("Ikuru"); | |
cell = cells.get("C21"); | |
cell.putValue("Ikuru"); | |
cell = cells.get("C22"); | |
cell.putValue("Ipoh Coffee"); | |
cell = cells.get("C23"); | |
cell.putValue("Ipoh Coffee"); | |
cell = cells.get("C24"); | |
cell.putValue("Ipoh Coffee"); | |
cell = cells.get("C25"); | |
cell.putValue("Chocolade"); | |
cell = cells.get("C26"); | |
cell.putValue("Chocolade"); | |
cell = cells.get("C27"); | |
cell.putValue("Chocolade"); | |
cell = cells.get("C28"); | |
cell.putValue("Chocolade"); | |
cell = cells.get("C29"); | |
cell.putValue("Chocolade"); | |
cell = cells.get("C30"); | |
cell.putValue("Chocolade"); | |
cell = cells.get("D2"); | |
cell.putValue("Asia"); | |
cell = cells.get("D3"); | |
cell.putValue("Asia"); | |
cell = cells.get("D4"); | |
cell.putValue("Asia"); | |
cell = cells.get("D5"); | |
cell.putValue("Asia"); | |
cell = cells.get("D6"); | |
cell.putValue("Europe"); | |
cell = cells.get("D7"); | |
cell.putValue("Europe"); | |
cell = cells.get("D8"); | |
cell.putValue("Europe"); | |
cell = cells.get("D9"); | |
cell.putValue("Europe"); | |
cell = cells.get("D10"); | |
cell.putValue("Europe"); | |
cell = cells.get("D11"); | |
cell.putValue("America"); | |
cell = cells.get("D12"); | |
cell.putValue("America"); | |
cell = cells.get("D13"); | |
cell.putValue("America"); | |
cell = cells.get("D14"); | |
cell.putValue("America"); | |
cell = cells.get("D15"); | |
cell.putValue("America"); | |
cell = cells.get("D16"); | |
cell.putValue("America"); | |
cell = cells.get("D17"); | |
cell.putValue("America"); | |
cell = cells.get("D18"); | |
cell.putValue("Europe"); | |
cell = cells.get("D19"); | |
cell.putValue("Europe"); | |
cell = cells.get("D20"); | |
cell.putValue("Europe"); | |
cell = cells.get("D21"); | |
cell.putValue("Oceania"); | |
cell = cells.get("D22"); | |
cell.putValue("Oceania"); | |
cell = cells.get("D23"); | |
cell.putValue("Oceania"); | |
cell = cells.get("D24"); | |
cell.putValue("Oceania"); | |
cell = cells.get("D25"); | |
cell.putValue("Africa"); | |
cell = cells.get("D26"); | |
cell.putValue("Africa"); | |
cell = cells.get("D27"); | |
cell.putValue("Africa"); | |
cell = cells.get("D28"); | |
cell.putValue("Africa"); | |
cell = cells.get("D29"); | |
cell.putValue("Africa"); | |
cell = cells.get("D30"); | |
cell.putValue("Africa"); | |
cell = cells.get("E2"); | |
cell.putValue("China"); | |
cell = cells.get("E3"); | |
cell.putValue("India"); | |
cell = cells.get("E4"); | |
cell.putValue("Korea"); | |
cell = cells.get("E5"); | |
cell.putValue("India"); | |
cell = cells.get("E6"); | |
cell.putValue("France"); | |
cell = cells.get("E7"); | |
cell.putValue("France"); | |
cell = cells.get("E8"); | |
cell.putValue("Germany"); | |
cell = cells.get("E9"); | |
cell.putValue("Italy"); | |
cell = cells.get("E10"); | |
cell.putValue("France"); | |
cell = cells.get("E11"); | |
cell.putValue("U.S."); | |
cell = cells.get("E12"); | |
cell.putValue("U.S."); | |
cell = cells.get("E13"); | |
cell.putValue("Brazil"); | |
cell = cells.get("E14"); | |
cell.putValue("U.S."); | |
cell = cells.get("E15"); | |
cell.putValue("U.S."); | |
cell = cells.get("E16"); | |
cell.putValue("Canada"); | |
cell = cells.get("E17"); | |
cell.putValue("U.S."); | |
cell = cells.get("E18"); | |
cell.putValue("Italy"); | |
cell = cells.get("E19"); | |
cell.putValue("France"); | |
cell = cells.get("E20"); | |
cell.putValue("Italy"); | |
cell = cells.get("E21"); | |
cell.putValue("New Zealand"); | |
cell = cells.get("E22"); | |
cell.putValue("Australia"); | |
cell = cells.get("E23"); | |
cell.putValue("Australia"); | |
cell = cells.get("E24"); | |
cell.putValue("New Zealand"); | |
cell = cells.get("E25"); | |
cell.putValue("S.Africa"); | |
cell = cells.get("E26"); | |
cell.putValue("S.Africa"); | |
cell = cells.get("E27"); | |
cell.putValue("S.Africa"); | |
cell = cells.get("E28"); | |
cell.putValue("Egypt"); | |
cell = cells.get("E29"); | |
cell.putValue("Egypt"); | |
cell = cells.get("E30"); | |
cell.putValue("Egypt"); | |
cell = cells.get("F2"); | |
cell.putValue(2000) | |
cell = cells.get("F3"); | |
cell.putValue(500) | |
cell = cells.get("F4"); | |
cell.putValue(1200) | |
cell = cells.get("F5"); | |
cell.putValue(1500) | |
cell = cells.get("F6"); | |
cell.putValue(500) | |
cell = cells.get("F7"); | |
cell.putValue(1500) | |
cell = cells.get("F8"); | |
cell.putValue(800) | |
cell = cells.get("F9"); | |
cell.putValue(900) | |
cell = cells.get("F10"); | |
cell.putValue(500) | |
cell = cells.get("F11"); | |
cell.putValue(1600) | |
cell = cells.get("F12"); | |
cell.putValue(600) | |
cell = cells.get("F13"); | |
cell.putValue(2000) | |
cell = cells.get("F14"); | |
cell.putValue(500) | |
cell = cells.get("F15"); | |
cell.putValue(900) | |
cell = cells.get("F16"); | |
cell.putValue(700) | |
cell = cells.get("F17"); | |
cell.putValue(1400) | |
cell = cells.get("F18"); | |
cell.putValue(1350) | |
cell = cells.get("F19"); | |
cell.putValue(300) | |
cell = cells.get("F20"); | |
cell.putValue(500) | |
cell = cells.get("F21"); | |
cell.putValue(1000) | |
cell = cells.get("F22"); | |
cell.putValue(1500) | |
cell = cells.get("F23"); | |
cell.putValue(1500) | |
cell = cells.get("F24"); | |
cell.putValue(1600) | |
cell = cells.get("F25"); | |
cell.putValue(1000) | |
cell = cells.get("F26"); | |
cell.putValue(1200) | |
cell = cells.get("F27"); | |
cell.putValue(1300) | |
cell = cells.get("F28"); | |
cell.putValue(1500) | |
cell = cells.get("F29"); | |
cell.putValue(1400) | |
cell = cells.get("F30"); | |
cell.putValue(1000) | |
//Adding a new sheet | |
var sheet2 = workbook.getWorksheets().get(workbook.getWorksheets().add()); | |
//Naming the sheet | |
sheet2.setName("PivotTable"); | |
//Getting the pivottables collection in the sheet | |
var pivotTables = sheet2.getPivotTables(); | |
//Adding a PivotTable to the worksheet | |
var index = pivotTables.add("=Data!A1:F30", "B3", "PivotTable1"); | |
//Accessing the instance of the newly added PivotTable | |
var pivotTable = pivotTables.get(index); | |
//Showing the grand totals | |
pivotTable.setRowGrand(true); | |
pivotTable.setColumnGrand(true); | |
//Setting the PivotTable report is automatically formatted | |
pivotTable.setIsAutoFormat(true); | |
//Setting the PivotTable autoformat type. | |
pivotTable.setAutoFormatType(AsposeCells.PivotTableAutoFormatType.Report6); | |
//Draging the first field to the row area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, 0); | |
//Draging the third field to the row area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, 2); | |
//Draging the second field to the row area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, 1); | |
//Draging the fourth field to the column area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, 3); | |
//Draging the fifth field to the data area. | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, 5); | |
//Setting the number format of the first data field | |
pivotTable.getDataFields().get(0).setNumberFormat("$#,##0.00"); | |
//Saving the Excel file | |
workbook.save(dataDir + "pivotTable_test.out.xlsx"); |
Wie man ein Pivot-Diagramm mit der Aspose.Cells for Node.js via C++ Bibliothek hinzufügt
Um ein PivotChart mit Aspose.Cells for Node.js via C++ zu erstellen:
- Fügen Sie ein Diagramm hinzu.
- Setzen Sie den PivotSource des Diagramms so, dass er auf eine vorhandene Pivot-Tabelle in der Tabelle verweist.
- Setzen Sie andere Attribute.
const AsposeCells = require("aspose.cells.node"); | |
//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
//The path to the documents directory. | |
var dataDir = RunExamples.GetDataDir("."); | |
//Instantiating an Workbook object | |
//Opening the excel file | |
var workbook = new AsposeCells.Workbook(dataDir + "pivotTable_test.xlsx"); | |
//Adding a new sheet | |
var sheet3 = workbook.getWorksheets().get(workbook.getWorksheets().add(AsposeCells.SheetType.Chart)); | |
//Naming the sheet | |
sheet3.setName("PivotChart"); | |
//Adding a column chart | |
index = sheet3.getCharts().add(AsposeCells.ChartType.Column, 0, 5, 28, 16); | |
//Setting the pivot chart data source | |
sheet3.getCharts().get(index).setPivotSource("PivotTable!PivotTable1"); | |
sheet3.getCharts().get(index).setHidePivotFieldButtons(false); | |
//Saving the Excel file | |
workbook.save(dataDir + "pivotChart_test_out.xlsx"); |