Up next in 10
Node.js SheetJS Tutorial to Merge Multiple Excel Files into Single Sheet Using xlsx Module
Jun 7, 2025
Support the stream: https://streamlabs.com/freemediatools
Show More Show Less View Video Transcript
0:03
uh hello guys welcome to this uh video
0:06
and this live stream i will show you a
0:09
nodejs tutorial on how to actually merge
0:12
two excel files into a single sheet
0:15
using a library called as
0:17
sheetjs sheetjs is actually a
0:20
open-source library inside uh nodejs if
0:23
you want to work with excel
0:25
files so this is the library that i will
0:31
show you in order to uh basically merge
0:36
multiple excel files into a single sheet
0:39
so i written this nodejs script if you
0:43
see so i've also given the script in the
0:46
description of this video as
0:52
well so i'm just checking that we are
0:55
live or not
0:59
so yeah so if you can see right
1:03
here we have written this nodejs script
1:08
which
1:11
actually merges we have these two excel
1:14
files right here if you see we have the
1:16
first excel
1:17
file which have three columns name age
1:21
country and we have four
1:22
records and this is the second excel
1:25
file if you
1:28
see so we have the same column structure
1:30
name age country and then we have four
1:33
more records so now this script will
1:35
actually do it will merge both these
1:37
excel files into a single excel file so
1:40
if i go to the terminal and just execute
1:42
the script node index js so you will see
1:46
it will merge both these files into a
1:50
single one now it has created this
1:52
output file right here merged.xls excel
1:55
is and if i show you the output file now
1:58
it contains uh eight
2:00
records four from the first one and four
2:04
from the second one so it has merged
2:06
both these excel files into a single
2:08
sheet so that's exactly that we wanted
2:11
so this was the first excel file this
2:13
was the second excel file and what it
2:15
did it it basically merged both the
2:18
excel files into a single excel sheet
2:22
you will see if you're watching it for
2:24
the very first time guys please hit that
2:25
like button subscribe the channel as
2:27
well so now we have got eight records in
2:30
the output excel sheet so now i'll be
2:33
showing you how to do this inside nodejs
2:36
for this you just need to first of all
2:39
install this
2:40
package it's also called as sheetjs so
2:44
just install this nodejs
2:47
package
2:49
so just search here
2:52
xls and uh you just need to install this
2:55
package so npmi
2:58
xls so just install this package i've
3:01
already installed it and after that you
3:04
just need to write this script so just
3:07
create an index.js file and first of all
3:10
you need to require the necessary
3:12
packages so we simply require this
3:15
package xls and then we also require the
3:18
built-in path module as well of
3:23
nodejs so after that we simply read both
3:26
the excel files so we are reading both
3:29
the excel files which is present in the
3:31
same directory so for reading it we
3:34
basically create these two
3:36
variables worksheet one worksheet two so
3:39
for reading it we are using the read
3:41
file method of sheet js and here we are
3:44
providing both the paths of both the
3:46
files
3:47
here after that we
3:51
simply get the first sheet from each so
3:56
for getting the sheet name we simply use
3:59
this
4:01
sheets and we get both the sheets name
4:04
right here like this
4:08
so essentially what we are doing right
4:10
here we are opening the excel file we
4:11
getting each excel file has this sheet
4:14
one so we getting both the sheets one
4:17
using this code so and storing it inside
4:20
these variables after that we now need
4:23
to convert the sheets into json for
4:27
converting that we simply say data 1 and
4:30
here we simply use xls sheetjs module
4:34
once again and it contains this function
4:37
we need to convert this sheet into json
4:40
and for this we need to do this for both
4:43
the
4:46
sheets so data
4:52
to so we essentially we converted both
4:55
the worksheets into json by using this
4:58
function sheet to json after that now we
5:02
just need to merge both the rows of both
5:04
the files if you see we have having four
5:07
rows in this file and four rows in the
5:09
next file now we just need to merge both
5:12
these rows together for merging it we
5:15
now need to write the code for this so
5:18
we simply say
5:20
merged data and here we simply
5:24
say dot dot dot data 1 and dot dot dot
5:28
data 2 this is essentially it is merging
5:31
both these files together into a single
5:34
one and after that we now need to create
5:37
a new sheet so for creating a new sheet
5:40
we create use this function once again
5:44
utils and it contains this function here
5:47
which is
5:49
json to
5:51
sheet and we just need to pass this
5:55
merged data right here that's all so
5:58
after that now we just need to create a
6:01
new
6:02
workbook for creating a new workbook it
6:05
contains another function right here
6:06
which is book new so it will create a
6:11
new uh excel file and inside this we are
6:13
appending the data by using this
6:15
function which is book_append sheet and
6:18
here this will the output sheet name
6:21
which will be merged sheet so after you
6:25
do this we just need to save the excel
6:27
file so now for saving the excel file we
6:29
simply
6:30
use the path so where you need to save
6:35
it just put the file name so output file
6:39
name will be
6:40
merged.xls and then lastly we just need
6:43
to uh save the file so for saving it it
6:46
contains this function which is write
6:49
file and here we simply provide the two
6:53
arguments which will be the workbook and
6:55
the output path where you need to save
6:58
this and then lastly we can simply show
7:01
a notification message that
7:03
your two files of excel file have been
7:06
successfully merged together that's the
7:09
overall script guys if you need the full
7:12
script the link is given in the
7:13
description you can go to my blog post
7:16
now let me run this once again and uh if
7:19
i run the script here you will see it is
7:23
will be xls is not defined so if you are
7:26
getting this error uh i think i
7:28
misspelled it so this needs to be xls so
7:31
three people are watching the stream
7:32
guys please hit that like button
7:34
subscribe the channel as well and here
7:36
we are importing this uh sheetjs package
7:39
right here if i again run this script
7:42
here node index.js it's a coding channel
7:45
guys i upload daily coding related
7:47
videos almost thousands videos are
7:49
available on the channel so if you're
7:51
watching it for the very first time then
7:53
please subscribe this channel and you
7:55
can see it will create the merged.xls
7:58
file and it will actually
8:01
contain all the merged content so now in
8:05
this easy way you can merge two excel
8:08
files together into a single sheet and
8:11
inside nodejs so if you have some
8:14
comments you can comment here and also
8:16
you can check out my website guys as
8:18
well freemediatools.com
8:20
uh which contains thousands of tools
8:23
related to audio video and image
8:25
processing so you can check out this
8:27
website and i basically upload tools on
8:30
a daily basis on this website so these
8:33
tools are pretty much free all the tools
8:36
are free you can use this so the website
8:38
is
8:40
freemediatools.com and
8:42
uh if you still have some comment you
8:45
can ask in the comment section and
8:48
uh three people are watching the stream
8:54
so if you still have some comments you
8:59
can ask
9:04
it and thank you very much guys for
9:08
watching this stream
#Software
#Scripting Languages