Créer des tableaux croisés dynamiques et des graphiques croisés dynamiques
Contents
[
Hide
]
Un tableau croisé dynamique est un résumé interactif des enregistrements. Par exemple, vous pouvez avoir des centaines d’entrées de facture dans une liste dans une feuille de calcul. Un tableau croisé dynamique peut totaliser les factures par client, produit ou date. Avec Microsoft Excel, il est possible de réarranger rapidement les informations dans le tableau croisé dynamique en faisant glisser les boutons vers une nouvelle position.
Un graphique croisé dynamique est une représentation graphique interactive des données dans un tableau croisé dynamique. Les graphiques croisés dynamiques ont été introduits dans Excel 2000. L’utilisation d’un graphique croisé dynamique rend encore plus facile la compréhension des données puisque le tableau croisé dynamique crée automatiquement des sous-totaux et des totaux.
Aspose.Cells for Node.js via C++ supporte tableaux croisés dynamiques et graphismes croisés dynamiques.
Ajouter des tableaux croisés dynamiques et des graphiques en utilisant Aspose.Cells for Node.js via C++
Aspose.Cells for Node.js via C++ fournit un ensemble spécial de classes pour créer des tableaux croisés dynamiques. Ces classes servent à créer et définir des objets PivotTable, qui agissent comme les blocs de construction de base d’un tableau croisé dynamique :
- PivotField, un champ dans un rapport de tableau croisé dynamique.
- PivotFields, une collection de tous les objets PivotField dans un tableau croisé dynamique.
- PivotTable, un rapport de tableau croisé dynamique sur une feuille de calcul.
- PivotTables, une collection de tous les objets PivotTable sur la feuille de calcul.
Préparer l’utilisation de Aspose.Cells for Node.js via C++
- Installer Aspose.Cells for Node.js via C++ depuis NPM, utiliser la commande : $ npm install aspose.cells.node.
- Vous pouvez également suivre les instructions étape par étape pour installer “Aspose.Cells for Node.js via C++” dans votre environnement de développement.
Comment ajouter un tableau croisé dynamique en utilisant Aspose.Cells for Node.js via C++
Pour créer un tableau croisé dynamique avec Aspose.Cells for Node.js via C++ :
- Ajoutez des données à des cellules de feuille de calcul en utilisant la méthode put_value d’un objet Cell. Vous pouvez également utiliser un fichier de modèle déjà rempli de données. Les données seront utilisées comme source de données de la table pivotante.
- Ajoutez une table pivotante à la feuille de calcul en appelant la méthode add de la collection PivotTables (encapsulée dans l’objet Feuille de calcul).
- Accédez au nouvel objet PivotTable depuis la collection PivotTables en passant son index. # Utilisez l’un des objets table pivotante encapsulés dans l’objet PivotTable pour gérer la table.
Des exemples de code sont donnés ci-dessous.
This file contains hidden or 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
| 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"); |
Comment ajouter un graphique croisé dynamique en utilisant la bibliothèque Aspose.Cells for Node.js via C++
Pour créer un PivotChart avec Aspose.Cells for Node.js via C++ :
- Ajoutez un graphique.
- Définissez la PivotSource du graphique pour qu’elle fasse référence à une table pivotante existante dans la feuille de calcul.
- Définissez d’autres attributs.
This file contains hidden or 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
| 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"); |