Welcome folks today in this blog post we will be using the officegen
library to generate excel
files and adding data
inside cells
and sheets
in node.js and javascript. All the full source code of the application is shown below.
Get Started
In order to get started you need to make a new node.js
project using the below command as shown below
npm init -y
npm i officegen
npm i xlsx
And after that you need to make a new index.js
file and copy paste the following code
index.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
const officegen = require('officegen') const fs = require('fs') // Create an empty Excel object: let xlsx = officegen('xlsx') // Officegen calling this function after finishing to generate the xlsx document: xlsx.on('finalize', function(written) { console.log( 'Finish to create a Microsoft Excel document.' ) }) // Officegen calling this function to report errors: xlsx.on('error', function(err) { console.log(err) }) let sheet = xlsx.makeNewSheet() sheet.name = 'Officegen Excel' |
As you can see we are importing the xlsx
and officegen
library at the top and then we are having the events
for error and finalize. When the file is exported then the finalize
event will call and if any error takes place then error
event takes place. And after that we are making a new excel
sheet using the makeNewSheet()
method and then we are editing the name
of the sheet using the name
attribute.
1 2 3 4 5 6 7 8 9 |
sheet.setCell('A1', "Name") sheet.setCell('B1',"Age") sheet.setCell("C1","Country") sheet.setCell("A2","John") sheet.setCell("B2","25") sheet.setCell("C2","NZ") sheet.setCell("A3","Steve") sheet.setCell("B3","45") sheet.setCell("C3","AUS") |
Now we can edit the data
inside the cells
using the setCell()
method and inside it we are passing two
arguments first is the actual location
which is the coordinates and secondly the actual data
to be inserted.
You can even insert the data
directly using the two dimensional array as shown below using the data
attribute as shown below
1 2 3 4 5 6 |
sheet.data[0][0] = "Name" sheet.data[0][1] = "Age" sheet.data[0][2] = "Country" sheet.data[1][0] = 'John' sheet.data[1][1] = '25' sheet.data[1][2] = 'New Zealand' |
Saving the Excel File
Now we can use the generate()
method to actually save the excel
file inside the local file system as shown below
1 2 3 4 5 6 7 8 |
let out = fs.createWriteStream('example.xlsx') out.on('error', function(err) { console.log(err) }) // Async call to generate the output file: xlsx.generate(out) |