149

Sometimes, you have a 3rd party API which returns data in JSON format and you need that data in excel file. How we can do this? Let’s follow the steps:

We will save our JSON data in the EXCEL file using an excel4node library in Node.js.

Let’s jump to the code and then I will explain code line by line.

Create index.js

Create package.json using

npm init

Install excel4node using

npm install — save excel4node

Define your data you want to be store in excel

const data = [ {
"name":"Shadab Shaikh", "email":"shadab@gmail.com", "mobile":"1234567890" } ]

Import excel4node library

const xl = require('excel4node');

Create a workbook and give some awesome name

const wb = new xl.Workbook();
const ws = wb.addWorksheet('Worksheet Name');

Now Let’s define columnName

const headingColumnNames = [ "Name"
"Email"
"Mobile"
]

Before moving to next let’s explore some functions in excel4node library

1. cell(rownumber,columnnumber) requires 2 parameters a. row number(starts from 1) b. column number(starts from 1) This function selects cell with given row no. and column no. 2. string(data) , number(data) we can store data as string or number just call the above functions and pass data in it.

Now write columnName in Excel file using functions in excel4node

let headingColumnIndex = 1;
headingColumnNames.forEach(heading => {
ws.cell(1, headingColumnIndex++) .string(heading) });

Finally, write our data in excel file

(Don’t forget to start row number from 2)

let rowIndex = 2;
data.forEach( record => {
let columnIndex = 1;
Object.keys(record ).forEach(columnName =>{
ws.cell(rowIndex,columnIndex++) .string(record [columnName]) });
rowIndex++; });

Now Let’s take workbook and save it into the file

wb.write('filename.xlsx');

Here is full code, just copy and paste in your favorite editor to go through demo.

const xl = require('excel4node');
const wb = new xl.Workbook();
const ws = wb.addWorksheet('Worksheet Name');
const data = [ {
"name":"Shadab Shaikh", "email":"shadab@gmail.com", "mobile":"1234567890" } ]
const headingColumnNames = [ "Name", "Email", "Mobile", ] //Write Column Title in Excel file
let headingColumnIndex = 1;
headingColumnNames.forEach(heading => {
ws.cell(1, headingColumnIndex++) .string(heading) }); //Write Data in Excel file
let rowIndex = 2;
data.forEach( record => {
let columnIndex = 1;
Object.keys(record ).forEach(columnName =>{
ws.cell(rowIndex,columnIndex++) .string(record [columnName]) });
rowIndex++; });
wb.write('data.xlsx');

I hope this was helpful… :-)