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 columnno2. 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');
Top comments (1)
ty