How to create, read and write excel file in Express.js?
How to create, read and write excel using Express.js?
- In this tutorial, we will learn about reading and writing data in an excel sheet.
- The following lesson 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.
- Install package xlsx to read an excel file.
npm install xlsx
- Create an excel file Tutorial.xlsx
- For reading data from excel in JSON format, do the following code
In read.js
const reader = require('xlsx')
const file = reader.readFile('./Tutorials.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)
})
}
console.log(data)
Run the code using the command:
node read.js
Output:
- For writing data from JSON format to excel, do the following code
In write.js
const reader = require('xlsx')
const file = reader.readFile('./Tutorials.xlsx')
// Sample data set
let student_data = [{
ID:6,
Language:'Ruby on Rails'
},
{
ID:7,
Language:'R'
}]
const ws = reader.utils.json_to_sheet(student_data)
reader.utils.book_append_sheet(file,ws,"Sheet3")
// Writing to our file
reader.writeFile(file,'./Tutorial.xlsx')
Run the code using the command:
node write.js
Output:
