How to save JSON data in EXCEL file using Node.js
Tags: node, json, excel, npm
Published: 2020-05-13 17:06:27

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](https://www.npmjs.com/package/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

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

####Import excel4node library

   ```javascript
    const xl = require('excel4node');
   ```

####Create a workbook and give some awesome name

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

####Now Let's define columnName 

   ```javascript
    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

   ```javascript
    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)

   ```javascript
    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

   ```javascript
    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');