How to Create a Pivot Table in Excel using Node.js

How to Create a Pivot Table in Excel using Node.js

The best thing about Excel files is that you can make modifications at any data visualization or compilation stage. MS Excel is still at the top list for organizing data and performing complex calculations. In this blog post, we will learn how to create a Pivot table in Excel using Node.js programmatically. In addition, we will also go through how to make a Pivot chart in Excel file based on a Pivot table. For this, we will install this Excel JavaScript API in our Node.js project.

The following sections will be covered:

Excel JavaScript API installation

To install this powerful library, you either download the API package or install it by running the following commands:

npm install java
npm install aspose.cells

How to Create a Pivot Table in Excel using Node.js

In this section, we will write the following steps and the code snippet that creates a Pivot table in Excel programmatically.

You may follow the following steps and the code snippet to convert Excel to DataTable in C#:

  1. Instantiate an instance of the Workbook class.
  2. Get the reference of the first worksheet by calling the get(index) method.
  3. Set the name of the worksheet by invoking the setName method.
  4. Call the getCells method to get the Cells collection.
  5. Get the Cell element at the specified cell name.
  6. Call this setValue method to set the value of the range.
  7. Get Cell object in the range by calling the get method.
  8. Adding a new sheet by calling the add method.
  9. get the Worksheet element at the specified index.
  10. Name the sheet by calling the setName method.
  11. Add a Pivot Table to the worksheet by invoking the add method.
  12. Show the grand totals by setting the value of setRowGrand method.
  13. Set the value of the setColumnGrand method that indicates whether the PivotTable report shows grand totals for columns.
  14. Now, set the value of the setAutoFormat method that indicates whether the PivotTable report is automatically formatted.
  15. Set the PivotTable autoformat type by invoking the setAutoFormatType method.
  16. Invoke the addFieldToArea method to drag the first, second, third, fourth, and fifth fields to the row area.
  17. Set the number format of the first data field by calling the getDataFields method.
  18. Saving the Excel file by invoking the save method.

Copy & paste the following code into your main file:

var aspose = aspose || {};
aspose.cells = require("aspose.cells");
// Instantiate an instant of the Workbook class
var workbook = new aspose.cells.Workbook();
// Get the reference of the first worksheet by calling the get(index) method
var sheet = workbook.getWorksheets().get(0);
// set the name of the worksheet by invoking the setName method.
sheet.setName("Data");
// Call the getCells method to get the Cells collection.
var cells = sheet.getCells();
// Get the Cell element at the specified cell name.
var cell = cells.get("A1");
// Call this method to set the value of the range.
cell.setValue("Employee");
// Get Cell object in the range by calling the get method .
cell = cells.get("B1");
cell.setValue("Quarter");
cell = cells.get("C1");
cell.setValue("Product");
cell = cells.get("D1");
cell.setValue("Continent");
cell = cells.get("E1");
cell.setValue("Country");
cell = cells.get("F1");
cell.setValue("Sale");
cell = cells.get("A2");
cell.setValue("David");
cell = cells.get("A3");
cell.setValue("David");
cell = cells.get("A4");
cell.setValue("David");
cell = cells.get("A5");
cell.setValue("David");
cell = cells.get("A6");
cell.setValue("James");
cell = cells.get("A7");
cell.setValue("James");
cell = cells.get("A8");
cell.setValue("James");
cell = cells.get("A9");
cell.setValue("James");
cell = cells.get("A10");
cell.setValue("James");
cell = cells.get("A11");
cell.setValue("Miya");
cell = cells.get("A12");
cell.setValue("Miya");
cell = cells.get("A13");
cell.setValue("Miya");
cell = cells.get("A14");
cell.setValue("Miya");
cell = cells.get("A15");
cell.setValue("Miya");
cell = cells.get("A16");
cell.setValue("Miya");
cell = cells.get("A17");
cell.setValue("Miya");
cell = cells.get("A18");
cell.setValue("Elvis");
cell = cells.get("A19");
cell.setValue("Elvis");
cell = cells.get("A20");
cell.setValue("Elvis");
cell = cells.get("A21");
cell.setValue("Elvis");
cell = cells.get("A22");
cell.setValue("Elvis");
cell = cells.get("A23");
cell.setValue("Elvis");
cell = cells.get("A24");
cell.setValue("Elvis");
cell = cells.get("A25");
cell.setValue("Jean");
cell = cells.get("A26");
cell.setValue("Jean");
cell = cells.get("A27");
cell.setValue("Jean");
cell = cells.get("A28");
cell.setValue("Ada");
cell = cells.get("A29");
cell.setValue("Ada");
cell = cells.get("A30");
cell.setValue("Ada");
cell = cells.get("B2");
cell.setValue("1");
cell = cells.get("B3");
cell.setValue("2");
cell = cells.get("B4");
cell.setValue("3");
cell = cells.get("B5");
cell.setValue("4");
cell = cells.get("B6");
cell.setValue("1");
cell = cells.get("B7");
cell.setValue("2");
cell = cells.get("B8");
cell.setValue("3");
cell = cells.get("B9");
cell.setValue("4");
cell = cells.get("B10");
cell.setValue("4");
cell = cells.get("B11");
cell.setValue("1");
cell = cells.get("B12");
cell.setValue("1");
cell = cells.get("B13");
cell.setValue("2");
cell = cells.get("B14");
cell.setValue("2");
cell = cells.get("B15");
cell.setValue("3");
cell = cells.get("B16");
cell.setValue("4");
cell = cells.get("B17");
cell.setValue("4");
cell = cells.get("B18");
cell.setValue("1");
cell = cells.get("B19");
cell.setValue("1");
cell = cells.get("B20");
cell.setValue("2");
cell = cells.get("B21");
cell.setValue("3");
cell = cells.get("B22");
cell.setValue("3");
cell = cells.get("B23");
cell.setValue("4");
cell = cells.get("B24");
cell.setValue("4");
cell = cells.get("B25");
cell.setValue("1");
cell = cells.get("B26");
cell.setValue("2");
cell = cells.get("B27");
cell.setValue("3");
cell = cells.get("B28");
cell.setValue("1");
cell = cells.get("B29");
cell.setValue("2");
cell = cells.get("B30");
cell.setValue("3");
cell = cells.get("C2");
cell.setValue("Maxilaku");
cell = cells.get("C3");
cell.setValue("Maxilaku");
cell = cells.get("C4");
cell.setValue("Chai");
cell = cells.get("C5");
cell.setValue("Maxilaku");
cell = cells.get("C6");
cell.setValue("Chang");
cell = cells.get("C7");
cell.setValue("Chang");
cell = cells.get("C8");
cell.setValue("Chang");
cell = cells.get("C9");
cell.setValue("Chang");
cell = cells.get("C10");
cell.setValue("Chang");
cell = cells.get("C11");
cell.setValue("Geitost");
cell = cells.get("C12");
cell.setValue("Chai");
cell = cells.get("C13");
cell.setValue("Geitost");
cell = cells.get("C14");
cell.setValue("Geitost");
cell = cells.get("C15");
cell.setValue("Maxilaku");
cell = cells.get("C16");
cell.setValue("Geitost");
cell = cells.get("C17");
cell.setValue("Geitost");
cell = cells.get("C18");
cell.setValue("Ikuru");
cell = cells.get("C19");
cell.setValue("Ikuru");
cell = cells.get("C20");
cell.setValue("Ikuru");
cell = cells.get("C21");
cell.setValue("Ikuru");
cell = cells.get("C22");
cell.setValue("Ipoh Coffee");
cell = cells.get("C23");
cell.setValue("Ipoh Coffee");
cell = cells.get("C24");
cell.setValue("Ipoh Coffee");
cell = cells.get("C25");
cell.setValue("Chocolade");
cell = cells.get("C26");
cell.setValue("Chocolade");
cell = cells.get("C27");
cell.setValue("Chocolade");
cell = cells.get("C28");
cell.setValue("Chocolade");
cell = cells.get("C29");
cell.setValue("Chocolade");
cell = cells.get("C30");
cell.setValue("Chocolade");
cell = cells.get("D2");
cell.setValue("Asia");
cell = cells.get("D3");
cell.setValue("Asia");
cell = cells.get("D4");
cell.setValue("Asia");
cell = cells.get("D5");
cell.setValue("Asia");
cell = cells.get("D6");
cell.setValue("Europe");
cell = cells.get("D7");
cell.setValue("Europe");
cell = cells.get("D8");
cell.setValue("Europe");
cell = cells.get("D9");
cell.setValue("Europe");
cell = cells.get("D10");
cell.setValue("Europe");
cell = cells.get("D11");
cell.setValue("America");
cell = cells.get("D12");
cell.setValue("America");
cell = cells.get("D13");
cell.setValue("America");
cell = cells.get("D14");
cell.setValue("America");
cell = cells.get("D15");
cell.setValue("America");
cell = cells.get("D16");
cell.setValue("America");
cell = cells.get("D17");
cell.setValue("America");
cell = cells.get("D18");
cell.setValue("Europe");
cell = cells.get("D19");
cell.setValue("Europe");
cell = cells.get("D20");
cell.setValue("Europe");
cell = cells.get("D21");
cell.setValue("Oceania");
cell = cells.get("D22");
cell.setValue("Oceania");
cell = cells.get("D23");
cell.setValue("Oceania");
cell = cells.get("D24");
cell.setValue("Oceania");
cell = cells.get("D25");
cell.setValue("Africa");
cell = cells.get("D26");
cell.setValue("Africa");
cell = cells.get("D27");
cell.setValue("Africa");
cell = cells.get("D28");
cell.setValue("Africa");
cell = cells.get("D29");
cell.setValue("Africa");
cell = cells.get("D30");
cell.setValue("Africa");
cell = cells.get("E2");
cell.setValue("China");
cell = cells.get("E3");
cell.setValue("India");
cell = cells.get("E4");
cell.setValue("Korea");
cell = cells.get("E5");
cell.setValue("India");
cell = cells.get("E6");
cell.setValue("France");
cell = cells.get("E7");
cell.setValue("France");
cell = cells.get("E8");
cell.setValue("Germany");
cell = cells.get("E9");
cell.setValue("Italy");
cell = cells.get("E10");
cell.setValue("France");
cell = cells.get("E11");
cell.setValue("U.S.");
cell = cells.get("E12");
cell.setValue("U.S.");
cell = cells.get("E13");
cell.setValue("Brazil");
cell = cells.get("E14");
cell.setValue("U.S.");
cell = cells.get("E15");
cell.setValue("U.S.");
cell = cells.get("E16");
cell.setValue("Canada");
cell = cells.get("E17");
cell.setValue("U.S.");
cell = cells.get("E18");
cell.setValue("Italy");
cell = cells.get("E19");
cell.setValue("France");
cell = cells.get("E20");
cell.setValue("Italy");
cell = cells.get("E21");
cell.setValue("New Zealand");
cell = cells.get("E22");
cell.setValue("Australia");
cell = cells.get("E23");
cell.setValue("Australia");
cell = cells.get("E24");
cell.setValue("New Zealand");
cell = cells.get("E25");
cell.setValue("S.Africa");
cell = cells.get("E26");
cell.setValue("S.Africa");
cell = cells.get("E27");
cell.setValue("S.Africa");
cell = cells.get("E28");
cell.setValue("Egypt");
cell = cells.get("E29");
cell.setValue("Egypt");
cell = cells.get("E30");
cell.setValue("Egypt");
cell = cells.get("F2");
cell.setValue(2000);
cell = cells.get("F3");
cell.setValue(500);
cell = cells.get("F4");
cell.setValue(1200);
cell = cells.get("F5");
cell.setValue(1500);
cell = cells.get("F6");
cell.setValue(500);
cell = cells.get("F7");
cell.setValue(1500);
cell = cells.get("F8");
cell.setValue(800);
cell = cells.get("F9");
cell.setValue(900);
cell = cells.get("F10");
cell.setValue(500);
cell = cells.get("F11");
cell.setValue(1600);
cell = cells.get("F12");
cell.setValue(600);
cell = cells.get("F13");
cell.setValue(2000);
cell = cells.get("F14");
cell.setValue(500);
cell = cells.get("F15");
cell.setValue(900);
cell = cells.get("F16");
cell.setValue(700);
cell = cells.get("F17");
cell.setValue(1400);
cell = cells.get("F18");
cell.setValue(1350);
cell = cells.get("F19");
cell.setValue(300);
cell = cells.get("F20");
cell.setValue(500);
cell = cells.get("F21");
cell.setValue(1000);
cell = cells.get("F22");
cell.setValue(1500);
cell = cells.get("F23");
cell.setValue(1500);
cell = cells.get("F24");
cell.setValue(1600);
cell = cells.get("F25");
cell.setValue(1000);
cell = cells.get("F26");
cell.setValue(1200);
cell = cells.get("F27");
cell.setValue(1300);
cell = cells.get("F28");
cell.setValue(1500);
cell = cells.get("F29");
cell.setValue(1400);
cell = cells.get("F30");
cell.setValue(1000);
// Adding a new sheet by calling the add method
var sheetIndex = workbook.getWorksheets().add();
// get the Worksheet element at the specified index.
var sheet2 = workbook.getWorksheets().get(sheetIndex);
// Name the sheet by calling the setName method
sheet2.setName("PivotTable");
// Getting the pivottables collection in the sheet
var pivotTables = sheet2.getPivotTables();
// Add a PivotTable to the worksheet by invoking the add method
var index = pivotTables.add("=Data!A1:F30", "B3", "PivotTable1");
// get the instance of the newly added PivotTable
var pivotTable = pivotTables.get(index);
// Show the grand totals by setting the value of setRowGrand method
pivotTable.setRowGrand(true);
// Set the value of the setColumnGrand method that indicates whether the PivotTable report shows grand totals for columns.
pivotTable.setColumnGrand(true);
// Set the value of the setAutoFormat method that indicates whether the PivotTable report is automatically formatted.
pivotTable.setAutoFormat(true);
// Set the PivotTable autoformat type by invoking the setAutoFormatType method
pivotTable.setAutoFormatType(aspose.cells.PivotTableAutoFormatType.REPORT_6);
// Invoke the addFieldToArea method to drag the first, second, third, fourth and fifth fields to the row area.
pivotTable.addFieldToArea(aspose.cells.PivotFieldType.ROW, 0);
pivotTable.addFieldToArea(aspose.cells.PivotFieldType.ROW, 2);
pivotTable.addFieldToArea(aspose.cells.PivotFieldType.ROW, 1);
pivotTable.addFieldToArea(aspose.cells.PivotFieldType.COLUMN, 3);
pivotTable.addFieldToArea(aspose.cells.PivotFieldType.DATA, 5);
// Set the number format of the first data field by calling the getDataFields method
pivotTable.getDataFields().get(0).setNumber(7);
// Saving the Excel file by invoking the save method
workbook.save( "pivotTable_test.xls");

You may see the output in the image below:

Create a Pivot Table in Excel using Node.js

How to make a Pivot chart in Excel programmatically

Now, we will implement the functionality to create a Pivot chart based on the generated Pivot table using this Excel JavaScript API.

You may follow the steps and the code snippet mentioned below:

  1. Create an instance of the Workbook class.
  2. Invoke the add method to add a worksheet to the collection.
  3. get the Worksheet element at the specified index.
  4. Name the sheet by invoking the setName method.
  5. Add a column chart by calling the add method.
  6. get the Chart element at the specified index.
  7. Invoke the setPivotSource method to set the pivot chart data source.
  8. Call the setHidePivotFieldButtons method to either hide the pivot chart field buttons only when the chart is PivotChart.
  9. save the Excel file.

Copy & paste the following code into your main file:

var aspose = aspose || {};
aspose.cells = require("aspose.cells");
// Create an instance of the Workbook class
var workbook = new aspose.cells.Workbook("pivotTable_test.xls");
// Invoke the add method to add a worksheet to the collection.
var sheetIndex = workbook.getWorksheets().add(aspose.cells.SheetType.CHART);
// get the Worksheet element at the specified index.
var sheet3 = workbook.getWorksheets().get(sheetIndex);
// Name the sheet by invoking the setName method
sheet3.setName("PivotChart");
// Add a column chart by calling the add method
var chartIndex = sheet3.getCharts().add(aspose.cells.ChartType.COLUMN, 0, 5, 28, 16);
// get the Chart element at the specified index.
var chart = sheet3.getCharts().get(chartIndex);
// Invoke the setPivotSource method to set the pivot chart data source
chart.setPivotSource("PivotTable!PivotTable1");
// Call the setHidePivotFieldButtons method to either hide the pivot chart field buttons only when the chart is PivotChart.
chart.setHidePivotFieldButtons(false);
// Save the Excel file
workbook.save( "pivotChart_test.xls");

The output is shown in the image below:

How to make a Pivot chart in Excel programmatically

Get a Free License

You can avail a free temporary license to try the API without evaluation limitations.

Summing up

As you have gone through this enterprise-level Excel JavaScript API. In addition, you have learned how to create a Pivot table in Excel using Node.js programmatically and also we have implemented the functionality to make a Pivot chart. Moreover, you may visit the documentation to know the other features.

Moreover, we suggest you follow our Getting Started guide.

Finally, conholdate.com is consistently writing new blog posts. Therefore, please stay in touch for the latest updates.

Ask a question

You can let us know about your questions or queries on our forum.

FAQs

How do you create a PivotTable in Excel and edit it?

You can install this Excel JavaScript API to create a PivotTable in Excel programmatically. In addition, you may visit this link to get the steps and the code snippet.

See Also