Node.js OfficeGen Example to Generate Excel Files By Adding Data inside Cells & Sheets in Javascript

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

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'

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.

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")

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

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'

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

let out = fs.createWriteStream('example.xlsx')

out.on('error', function(err) {
  console.log(err)
})

// Async call to generate the output file:
xlsx.generate(out)

let out = fs.createWriteStream('example.xlsx')

out.on('error', function(err) {

console.log(err)

})

// Async call to generate the output file:

xlsx.generate(out)