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
- Search and replace in Excel using Node.js
- How to edit cells in Excel programmatically?
- Clear data from Excel spreadsheet
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.
- Import and create an object of the Cells class.
- Instantiates the WorkBook child class with an XLSX file.
- Call the replace(placeHolder, newValue) method to search a value and replace it with a new value.
- 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.
// edit a value by search and replace | |
var aspose = aspose || {}; | |
// create an obect of the Cells class. | |
aspose.cells = require("aspose.cells"); | |
// instantiates WorkBook child class with an XLSX file | |
var sampleFile = "sample.xlsx"; | |
var workbook = new aspose.cells.Workbook(sampleFile); | |
// call the replace method to search a value and replace it with a new value | |
workbook.replace("mustafa", 100); | |
// save the data into a new xlsx file | |
workbook.save("result.xlsx"); |
Now, start the server and you will see the output as shown below in the image.
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:
- Create an object of the Cells class.
- Create an object of the WorkBook child class by instantiating it with an XLSX file.
- 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.
- Call the save(fileName) method to save the file.
// edit a specific cell in an Excel sheet | |
var aspose = aspose || {}; | |
// create an object of the Cells class. | |
aspose.cells = require("aspose.cells"); | |
// instantiates WorkBook child class with an XLSX file | |
var sampleFile = "sample.xlsx"; | |
var workbook = new aspose.cells.Workbook(sampleFile); | |
// Access workbook, get the cells by calling getCells() method and call putValue(string) method to update a specefic cell(B2) of Excel sheet | |
workbook.getWorksheets().get(0).getCells().get("B2").putValue("new value"); | |
// save the data into a new xlsx file | |
workbook.save("result.xlsx"); |
The out of this code snippet will be something like shown in the image below.
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:
- Create an object of the Cells class.
- Instantiates WorkBook child class with an XLSX file.
- Call the clear() method to clear all cell and row objects.
- Call the save(fileName) method to save the file.
// clear the content of an SLSX file | |
var aspose = aspose || {}; | |
// create an object of the Cells class. | |
aspose.cells = require("aspose.cells"); | |
// instantiates WorkBook child class with an XLSX file | |
var sampleFile = "sample.xlsx"; | |
var workbook = new aspose.cells.Workbook(sampleFile); | |
// call clear() method to clear all cell and row objects. | |
workbook.getWorksheets().get(0).getCells().clear(); | |
// save the data into a new xlsx file | |
workbook.save("result.xlsx"); |
Start the server and you may see the output shown in the image below.
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.