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:

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:

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