How to save JSON data in EXCEL file using Node.js

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… :-)