How to Edit Excel Sheet in Node.js

How to Edit Excel Sheet in Node.js

MS Excel is an integral component of any business operational structure as it offers a stack of rich features such as data entry, complex calculations, data analysis, report generation, task management, and many more. Users can perform financial analysis and then visualize the data using charts. Moreover, data changes occur so often, and Excel spreadsheets need to be updated to reflect those changes. However, we can save time by automating this whole process. In this article, we will learn how to edit Excel Sheet in Node.js programmatically.

We will cover the following topics in this article:

Node.js library to edit Excel file

Please run the following commands to set up the Node.js Excel library to start editing Excel spreadsheets programmatically.

npm install aspose.cells

npm install java

Please follow this blog post to know about the complete setup info and pre-requisites.

Note: You must have a source XLSX file in the root directory of your project as we have placed the “sample.xlsx” file in this tutorial.

Search and replace in Excel using Node.js

The following are the steps to perform this action programmatically.

  1. Import and create an object of the Cells class.
  2. Instantiates the WorkBook child class with an XLSX file.
  3. Call the replace(placeHolder, newValue) method to search a value and replace it with a new value.
  4. Save the file using save(fileName) method.

The following code sample demonstrates how to search and replace text in an Excel file using Node.js.

Now, start the server and you will see the output as shown below in the image.

How to Edit Excel Sheet in Node.js

How to Edit Excel Sheet in Node.js

How to edit cells in Excel programmatically?

Node.js Excel library also lets you update a value in a specific cell of an Excel sheet. Follow the following steps to achieve this functionality in your Node.js file:

  1. Create an object of the Cells class.
  2. Create an object of the WorkBook child class by instantiating it with an XLSX file.
  3. Access the workbook, get the cells by calling the getCells() method and call putValue(string) method to update a specific cell(i.e. B2) of the Excel sheet.
  4. Call the save(fileName) method to save the file.

The out of this code snippet will be something like shown in the image below.

how to edit spreadsheet

Edit cells in Excel programmatically

Clear data from Excel spreadsheet

In this section, we will learn how we can clear data from an Excel sheet using Node.js programmatically. We will perform the following steps:

  1. Create an object of the Cells class.
  2. Instantiates WorkBook child class with an XLSX file.
  3. Call the clear() method to clear all cell and row objects.
  4. Call the save(fileName) method to save the file.

Start the server and you may see the output shown in the image below.

Nodej.s Excel library

Clear data from Excel sheet using Node.js library

Get a Free License

You may use a free temporary license to use Aspose.Cells for Node.js without evaluation limitations.

Conclusion

This brings us to the end of this blog post. We have learned how to edit Excel Sheets in Node.js programmatically. We have used the Node.js Excel library to edit a specific cell and clear the file data. There are many further methods available here that you may explore and practice yourself. Further, conholdate.com is consistently writing on new topics. Therefore, please stay connected for regular updates.

Ask a question

If you have any questions, please feel free to contact us on the forum.

See Also