Crear tablas dinámicas y gráficos dinámicos

Agregar tablas dinámicas y gráficos usando Aspose.Cells for Node.js via C++

Aspose.Cells for Node.js via C++ proporciona un conjunto especial de clases utilizadas para crear tablas dinámicas. Estas clases sirven para crear y configurar objetos PivotTable, que actúan como los bloques básicos de un objeto PivotTable:

  • PivotField, un campo en un informe de tabla dinámica.
  • PivotFields, una colección de todos los objetos PivotField en una tabla dinámica.
  • PivotTable, un informe de tabla dinámica en una hoja de cálculo.
  • PivotTables, una colección de todos los objetos PivotTable en la hoja de cálculo.

Prepararse para usar Aspose.Cells for Node.js via C++

  1. Instala Aspose.Cells for Node.js via C++ desde NPM, usando el comando: $ npm install aspose.cells.node.
  2. También puedes seguir las instrucciones paso a paso sobre cómo instalar “Aspose.Cells for Node.js via C++” en tu entorno de desarrollo.

Cómo agregar una tabla dinámica usando Aspose.Cells for Node.js via C++

Para crear una tabla dinámica usando Aspose.Cells for Node.js via C++:

  1. Añadir algunos datos a las celdas de una hoja de cálculo utilizando el método put_value de un objeto Celda. También puedes utilizar un archivo de plantilla ya lleno de datos. Los datos se utilizarán como fuente de datos de la tabla dinámica.
  2. Agregue una tabla dinámica a la hoja de cálculo llamando al método add de la colección PivotTables (encapsulada en el objeto Worksheet).
  3. Accede al nuevo objeto PivotTable desde la colección PivotTables pasando su índice. # Usa cualquiera de los objetos de tabla dinámica encapsulados en el objeto PivotTable para gestionar la tabla.

A continuación se muestran ejemplos de código.

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");

Cómo agregar un gráfico dinámico usando la biblioteca Aspose.Cells for Node.js via C++

Para crear un gráfico dinámico usando Aspose.Cells for Node.js via C++:

  1. Agregue un gráfico.
  2. Establezca el PivotSource del gráfico para hacer referencia a una tabla dinámica existente en la hoja de cálculo.
  3. Establezca otros atributos.
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");