Up next in 10
Node.js ExcelJS Tutorial to Create Microsoft Excel File (.XLSX) From JSON File in Terminal
Jan 9, 2025
Buy Premium Scripts and Apps Here:
https://procodestore.com/
Show More Show Less View Video Transcript
0:05
uh Hello friends welcome to this video
0:07
so in this video we will talk about uh
0:09
how we can actually create an Excel file
0:11
Microsoft Excel file from a Json file
0:15
which actually contain some data so
0:18
right here guys we have uh taken a very
0:20
simple example so we have this data.
0:24
Json file and if you just look at this
0:26
file it contains an array of object each
0:29
object object is having three properties
0:31
which is the name age and Country again
0:34
name Age Country again name Age Country
0:36
so these are three records I need to now
0:39
insert them into a Excel file so I
0:41
basically use an Excel JS library in
0:46
nodejs to actually work with the
0:48
Microsoft Excel documents so if I just
0:51
run this application you will see on the
0:53
left hand side Excel file will be
0:55
created so node index.js you will see
0:59
Excel file created and saved as output.
1:02
XLS so if you open this file you will
1:04
basically see these are the columns name
1:07
Age Country and these are the records
1:09
first record Age this is the second
1:13
third if I just show you in Microsoft
1:15
Excel viewer this is actually the name
1:18
Age Country these are three columns and
1:20
these are three rows of data so in this
1:23
way you can actually export a Excel file
1:26
from a Json file and I will show you
1:29
guys in this live stream step by step so
1:32
before that you just need to install
1:34
this package which is Excel JS go to
1:37
npmjs.com just search for this package
1:40
which is Excel JS so it actually allows
1:45
you to read and write Excel files and
1:48
manipulate them and we actually 1
1:51
million weekly downloads are there so
1:53
simple command there npm install Excel
1:56
JS simply
1:59
just go to command line just make a
2:02
nodejs project and just type this
2:04
command npmi Excel JS so I've already
2:07
install this guys so I will now start it
2:10
from
2:10
scratch and show you step by step so the
2:13
very first thing we need to do right
2:15
here we actually need to uh create a
2:18
data. Json file and right here this will
2:22
be a array of object each property in
2:25
double quotes this is actually name name
2:28
Gotham let me put here
2:32
age and then we have the country right
2:36
here so we have one record you will see
2:39
that this is name AG country we have a
2:41
set of curly braet this is one record so
2:45
similarly we can put a comma and this
2:47
will be a second record third record I
2:50
can change the values
3:09
so we have three records out there and
3:11
once again let me again put one other
3:26
record so we have four objects right
3:28
here guys each object is containing
3:30
these three properties now we need to
3:32
insert them into Excel file so what I
3:34
will do I will simply delete this and
3:37
start from scratch so right here I will
3:40
show you step by step of this code right
3:44
here so just if you're watching it for
3:46
the very first time please hit that like
3:48
button subscribe the channel as well so
3:50
now the very first thing guys we need to
3:51
do we need to import this package which
3:54
is Excel JS and we just need to require
3:57
this at the very top Excel Js
4:00
then we need to also import the buil-in
4:02
module which is in available in nodejs
4:05
file system which allows you to read and
4:07
write files we have required this so now
4:10
we need to Simply make a
4:12
function create Excel file from
4:16
Json this will be a simple function guys
4:19
Asing function and here we will be
4:21
expecting the user to give the Json path
4:24
of the file and the Excel path these two
4:27
arguments will be passed as arguments to
4:29
this function now we need to Simply call
4:32
this
4:33
function so we make a simple main
4:36
function right
4:38
here this main function will be a async
4:43
function Arrow function so right here
4:46
this is actually main function we will
4:48
call this function so inside this
4:50
function we will actually provide the
4:52
path of the Json path so it is present
4:54
in the same directory data. Json
4:57
secondly you'll put the Axel path it
4:59
will also be created in the same
5:01
directory so result. XLS and now we will
5:04
be awaiting the response coming from
5:07
this function create Excel from Json we
5:09
will pass the arguments Json path and
5:12
Excel path as two arguments to this
5:14
function now we need to write the
5:16
definition of this function guys right
5:18
here so inside this function it is very
5:21
easy first of all we need to get access
5:25
to to workbook for doing this we need to
5:28
create a brand new workbook so for this
5:32
we will use the Excel
5:33
JS
5:36
and you can see that we imported Excel
5:38
Js from this module and it contains a
5:41
method to actually create a new Excel
5:43
book so here we'll put new Excel JS and
5:48
do workbook it will actually create a
5:51
new Excel workbook guys using this
5:53
Constructor so we have actually created
5:55
a new Excel workbook after that we need
5:58
to add a she sheet
6:00
worksheet so in every Excel file there
6:03
is a sheet number sheet one sheet 2
6:05
sheet three sheet 4 so we need to add
6:07
our very first sheet here so for adding
6:10
the sheet we simply use the workbook
6:12
object it contains a method add work
6:15
sheeet so here we add the worksheet we
6:18
need to give it a name sheet one so I
6:21
basically give it a name here it will be
6:23
sheet one so after this guys we now need
6:27
to load the Json data so the data coming
6:30
from this file data. Json we now need to
6:33
load this data so how we can load this
6:35
data we can create a variable and then
6:38
we can read the data by using json. par
6:42
first of all to actually pass the Json
6:45
and then we'll use the file system
6:46
module file system. read file
6:50
synchronously and we'll provide the Json
6:52
path and the second argument is utf8
6:54
which is the encoding type so first of
6:56
all what it is doing guys it is reading
6:58
the data from from file system module we
7:01
are providing the path of the Json file
7:03
and then we are passing the response of
7:04
Json by using json. pass so now you will
7:08
get your Json data so what we need to
7:10
now do we need to now add the
7:13
columns and
7:15
headers to Json keys for doing this we
7:20
will basically have the
7:22
columns so here we will simply use
7:25
object. keys after this we have Json
7:29
data
7:31
zero and then we will simply map
7:34
this for every key so
7:37
here we need to
7:43
here we need to give it a header right
7:46
here so header will be equal to key do
7:51
karat 0 we need to convert this into
7:55
uppercase for every header there will be
7:57
an uppercase correctors and then we will
8:00
see key. splice this is slightly
8:03
complicated but here we are
8:06
simply writing the header files so these
8:10
are all the columns which are there if I
8:12
explain you correctly what is happening
8:15
right here let me paste the code so here
8:17
guys what we are doing we are getting
8:19
all the columns from if you see the Json
8:21
data coming if I console loog the
8:23
statement this is the Json data and
8:26
these are the columns
8:29
if I try to run this file what will
8:31
happen just see node
8:34
index.js so it will see this is actual
8:37
Json data that is there first of all it
8:40
removes these double codes that are
8:42
there right here it basically converts
8:44
the Json into a JavaScript object so now
8:46
this is actual JavaScript object array
8:48
of JavaScript object it doesn't contain
8:50
this double code which is actually Json
8:53
we convert this using json. parts that
8:55
we use right here it basically removes
8:57
that double quote and after after that
8:59
we get the headers that we want so
9:01
header is equal to name header is age
9:04
header is country and then the key value
9:06
which is name Age Country withd 20 20 20
9:09
this columns that we got right here
9:11
after doing this now we need to Simply
9:14
invoke work
9:15
sheet The Columns here what are the
9:18
columns we need to define the columns
9:20
that we defined right here that's
9:24
all so after you do this we now need to
9:28
add the rows so for adding the rows is
9:30
very easy Json datar for each
9:34
row for each row for each item we need
9:37
to add them as
9:40
worksheet do add row this is actually
9:43
the method right here it will add this
9:46
row as to the Excel file that Sol so in
9:49
inside this for each Loop for each set
9:51
of data that you see right here name Age
9:54
Country this one this one this one so it
9:56
will basically Loop through all this
9:57
data and it will Loop through all this
10:00
data and add this to the Excel file
10:02
using add row function so lastly guys we
10:05
just need to save the file so await
10:07
workbook contains this method XLS do
10:11
write file so right file we will use
10:14
this add that location Excel path and
10:17
then we can simply set a message to the
10:20
user that your file has been
10:21
successfully saved Excel file created
10:25
and saved
10:30
so that's all that guys that is needed
10:32
guys it's very simple and if I just show
10:35
you try to run this application node
10:40
index.js you will see on the left hand
10:42
side result. XLS is created and you will
10:45
see all the data that you put in data.
10:48
Json name age country is the columns
10:51
right here result. XLS goam John Mike
10:54
Smith age
10:56
country so in this handy way guys you
10:58
can create your Excel files add rows
11:01
columns from Json files just make a
11:04
simple Json file it can be coming from
11:06
an API as well let's suppose you're
11:08
fetching data from an web web service
11:12
and this web service is returning data
11:13
in form of Json then you can directly
11:15
make a Excel file using this Library
11:18
Excel JS that I showed you in this live
11:20
tutorial thank you very much guys for
11:22
watching this video and I will be seeing
11:25
you in the next live stream
#Programming