How to Read and Write Excel file in Node.js ?

Read and write excel file in Node is a common task while updating the data from backend. There are many packages available on npm for performing these operations.

Approach

To read and write Excel file in Node js we will use the xlsx package from NPM. We can read the excel file using readFile method and update the content using writeFile method provided by xlsx library.

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.

Steps to Create Node App

Step 1: Initialize Node App

create node app using this command in the project directory

npm init

Step 2: 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.

Step 3: Implementing Read Operation

JavaScript

// Filename: read.js 

// 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:

Step 4: Implementing Write Operation

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

JavaScript

// Filename: write.js

// 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 5: Run the Application