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++

1. Installieren Sie Aspose.Cells for Node.js via C++ aus NPM, verwenden Sie den Befehl: $ npm install aspose.cells.node.
2. 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:

1. 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.
2. Fügen Sie dem Arbeitsblatt eine Pivot-Tabelle hinzu, indem Sie die Methode add der PivotTables-Sammlung aufrufen (die im Arbeitsblatt-Objekt gekapselt ist).
3. 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:

1. Fügen Sie ein Diagramm hinzu.
2. Setzen Sie den PivotSource des Diagramms so, dass er auf eine vorhandene Pivot-Tabelle in der Tabelle verweist.
3. 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");