Open In App

How to read and write Excel file in Node.js ?

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

Node.js is an open-source and cross-platform JavaScript runtime environment that can also be used to read from a file and write to a file which can be in txt, ods, xlsx, docx, etc format.

The following example covers how an excel file(.xlsx) file is read from an excel file and then converted into JSON and also to write to it. It can be achieved using a package called xlsx to achieve our goal.

Module Installation: You can install xlsx module using the following command:

npm install xlsx

Note: For the following example, text.xlsx is a dummy data file that has been used.

Filename: test.xlsx 

Sheet 1:

Sheet 2:

So the excel file test.xlsx has 2 sheets, one having Student details and another having lecturer details.

Read Operation Filename: read.js 

Javascript




// Requiring the module
const reader = require('xlsx')
  
// Reading our test file
const file = reader.readFile('./test.xlsx')
  
let data = []
  
const sheets = file.SheetNames
  
for(let i = 0; i < sheets.length; i++)
{
   const temp = reader.utils.sheet_to_json(
        file.Sheets[file.SheetNames[i]])
   temp.forEach((res) => {
      data.push(res)
   })
}
  
// Printing data
console.log(data)


Explanation: First, the npm module is included in the read.js file and then the excel file is read into a workbook i.e constant file in the above program.

The number of files in that particular excel file is available in the SheetNames property of the workbook. It can be accessed as follows:

const sheets = file.SheetNames  // Here the value of the sheets will be 2

A for loop is run until the end of the excel file starting from the first page. One of the most important functions used in the code above is the sheet_to_json() function present in the utils module of the xlsx package. It accepts a worksheet object as a parameter and returns an array of JSON objects.

There is a forEach loop which iterates through every JSON object present in the array temp and pushes it into a variable data which would contain all the data in JSON format.

Finally, the data is printed or any other modification can be performed on the array of JSON objects.

Step to run the application:

Run the read.js file using the following command:

node read.js

Output:

Write Operation In the following example, we will convert an array of JSON objects into an excel sheet and append it to the file.

Filename: write.js

Javascript




// Requiring module
const reader = require('xlsx')
  
// Reading our test file
const file = reader.readFile('./test.xlsx')
  
// Sample data set
let student_data = [{
    Student:'Nikhil',
    Age:22,
    Branch:'ISE',
    Marks: 70
},
{
    Student:'Amitha',
    Age:21,
    Branch:'EC',
    Marks:80
}]
  
const ws = reader.utils.json_to_sheet(student_data)
  
reader.utils.book_append_sheet(file,ws,"Sheet3")
  
// Writing to our file
reader.writeFile(file,'./test.xlsx')


Explanation: Here we have an array of JSON objects called student_data. We use two main functions in this program i.e json_to_sheet() which accepts an array of objects and converts them into a worksheet and another function is the book_append_sheet() to append the worksheet into the workbook.

Finally, all the changes are written to the test.xlsx file using writeFile() function which takes a workbook and a excel file as input parameter.

Step to run the application:

Run the read.js file using the following command:

node write.js

Output: The final test.xlsx file would look something like this: 

Sheet 1:

Sheet 2:

Sheet 3: We can see sheet 3 is appended into the test.xlsx as shown below:



Last Updated : 10 Jan, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads