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

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

Excel files allow you to modify data at any stage of visualization or analysis. Microsoft Excel remains the top tool for organizing data and performing complex calculations. In this post we show how to create a Pivot table in Excel using Node.js, and how to generate a Pivot chart from that table. We will use the Excel JavaScript API in a Node.js project.

The guide covers:

Excel JavaScript API installation

Install the library by downloading the package or running the commands below:

npm install java
npm install aspose.cells

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

Follow these steps and the code snippet to create a Pivot table programmatically.

  1. Instantiate a Workbook object.
  2. Retrieve the first worksheet with the get(index) method.
  3. Rename the worksheet using the setName method.
  4. Access the Cells collection via getCells.
  5. Get a specific cell with Get.
  6. Set the cell value using setValue.
  7. Retrieve a range cell with get.
  8. Add a new sheet using add.
  9. Get the new worksheet with get.
  10. Rename the sheet via setName.
  11. Add a Pivot Table to the worksheet with add.
  12. Show row grand totals using setRowGrand.
  13. Show column grand totals using setColumnGrand.
  14. Enable automatic formatting with setAutoFormat.
  15. Choose an auto‑format type via setAutoFormatType.
  16. Drag fields to the row area using addFieldToArea.
  17. Set the number format of the first data field with getDataFields.
  18. Save the workbook using save.

Copy and paste the code below into your main file:

The result looks like this:

Create a Pivot Table in Excel using Node.js

How to make a Pivot chart in Excel programmatically

Now we will create a Pivot chart based on the Pivot table.

  1. Create a Workbook instance.
  2. Add a worksheet with the add method.
  3. Retrieve the worksheet using get.
  4. Rename the sheet via setName.
  5. Add a column chart with add.
  6. Get the chart object using get.
  7. Set the chart’s data source with setPivotSource.
  8. Hide pivot field buttons when appropriate via setHidePivotFieldButtons.
  9. Save the workbook with save.

Insert the following code into your main file:

The output is shown below:

How to make a Pivot chart in Excel programmatically

Get a Free License

You can obtain a free temporary license to try the API without evaluation limits.

Summing up

You have explored the enterprise‑level Excel JavaScript API, learned how to create a Pivot table in Excel using Node.js, and added code to generate a Pivot chart. For more features, visit the documentation.

Also, follow our Getting Started guide.

Stay tuned to conholdate.com for the latest blog posts.

Ask a question

Post your questions on our forum.

FAQs

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

Install the Excel JavaScript API and follow the steps and code snippet linked above.

See Also