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

In some scenarios, you have 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 me up for the next 5 minutes.

Today we are saving 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":"[e-mail]",
        "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 parameter
a. row number(starts from 1)
b. column number(starts from 1)
This function selects cell with given rowno and columnno

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":"[e-mail]",
    "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('TeacherData.xlsx');

Comments

[DavidJames]

ty