DEV Community

Cover image for How to save JSON data in EXCEL file using Node.js
Shadab Majid Shaikh
Shadab Majid Shaikh

Posted on • Edited on

6 4

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":"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 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":"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('TeacherData.xlsx');

Image of Stellar post

Check out Episode 1: How a Hackathon Project Became a Web3 Startup 🚀

Ever wondered what it takes to build a web3 startup from scratch? In the Stellar Dev Diaries series, we follow the journey of a team of developers building on the Stellar Network as they go from hackathon win to getting funded and launching on mainnet.

Read more

Top comments (1)

Collapse
 
davidjames profile image
DavidJames

ty

Jetbrains image

Build Secure, Ship Fast

Discover best practices to secure CI/CD without slowing down your pipeline.

Read more

👋 Kindness is contagious

Engage with a wealth of insights in this thoughtful article, valued within the supportive DEV Community. Coders of every background are welcome to join in and add to our collective wisdom.

A sincere "thank you" often brightens someone’s day. Share your gratitude in the comments below!

On DEV, the act of sharing knowledge eases our journey and fortifies our community ties. Found value in this? A quick thank you to the author can make a significant impact.

Okay