Node.js Express Project to Merge Multiple Excel Files into One in Browser Using xlsx Module in Javascript

Welcome folks today in this blog post we will be merging multiple excel files into one in browser using xlsx module in node.js and express using javascript. All the full source code of the application is shown below.

Get Started

In order to get started you need to initialize a new node.js project using the below command as shown below

npm init -y

npm i express

npm i multer

npm i xlsx

Now after that you will be have the below directory structure of the node.js and express app as shown below

As you can see that we have the uploads folder in which we will be storing all the uploaded excel files by the user in order to merge them into one.

Now we need to make the index.js file which will be the starting point of the application and copy paste the below code

index.js

const express = require("express");

const bodyparser = require("body-parser");

const app = express();

app.use(express.static("uploads"));

app.use(bodyparser.urlencoded({ extended: false }));
app.use(bodyparser.json());

app.get("/", (req, res) => {
  res.sendFile(__dirname + "/index.html");
});


app.listen(5000, () => {
  console.log("App is listening on port 5000");
});

const express = require("express");

const bodyparser = require("body-parser");

const app = express();

app.use(express.static("uploads"));

app.use(bodyparser.urlencoded({ extended: false }));

app.use(bodyparser.json());

app.get("/", (req, res) => {

res.sendFile(__dirname + "/index.html");

});

app.listen(5000, () => {

console.log("App is listening on port 5000");

});

As you can see we are importing the express module at the very top and starting the express app at the port number 5000. And also we are including the bodyparser middleware to the express app which is needed for getting the data from the html5 forms. And also we have added a get reqiuest to the / route where we are loading the index.html template. Now we need to create the index.html template where we will be showing a simple html5 form to the user where we allow them to upload xlsx files to merge them as shown below

index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Merge XLSX Files in Node.js & Express</title>
</head>
<body>
    <form action="/merge" method="post" enctype="multipart/form-data">
        <input type="file" name="files" accept=".xlsx" multiple required>
        <input type="submit" value="Merge Excel Files">
    </form>
</body>
</html>

<!DOCTYPE html>

<head>

<title>Merge XLSX Files in Node.js & Express</title>

</head>

<body>

</form>

</body>

</html>

As you can see that we are submitting the form to /merge endpoint where the method is post where we are merging the xlsx files. Now we need to define this post request inside the index.js file as shown below

index.js

Configuring Multer to Upload Excel Files

Now we will be configuring multer library to allow users to upload excel files as shown below

const multer = require("multer");

var storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, "uploads");
  },
  filename: function (req, file, cb) {
    cb(null, Date.now() + ".xlsx"); //Appending .jpg
  },
});

var upload = multer({ storage: storage });

const multer = require("multer");

var storage = multer.diskStorage({

destination: function (req, file, cb) {

cb(null, "uploads");

filename: function (req, file, cb) {

cb(null, Date.now() + ".xlsx"); //Appending .jpg

});

var upload = multer({ storage: storage });

As you can see we are importing the multer library and after that we have defined the storage engine of the multer where we have declared the filename and destination. And then we are calling the multer constructor passing the storage object as shown above. Now we need to pass this multer middleware to the /merge post request as shown below

app.post("/merge", upload.array("files", 100), (req, res) => {
  try {
    
  } catch (error) {
    
  }
});

app.post("/merge", upload.array("files", 100), (req, res) => {

try {

} catch (error) {

}

});

As you can see we are applying the multer middleware in the middle of the post request here we are uploading multiple files in multer using the array() method. And then we will be writing the merging code inside the try and catch block as shown below

app.post("/merge", upload.array("files", 100), (req, res) => {
  try {
    // Read the contents of the uploaded files into memory
    const workbook1 = xlsx.readFile(req.files[0].path);
    const workbook2 = xlsx.readFile(req.files[1].path);

    // Merge the sheets of the two workbooks into a new workbook
    const mergedWorkbook = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(
      mergedWorkbook,
      workbook1.Sheets[workbook1.SheetNames[0]],
      "Sheet1"
    );
    xlsx.utils.book_append_sheet(
      mergedWorkbook,
      workbook2.Sheets[workbook2.SheetNames[0]],
      "Sheet2"
    );

    // Generate a buffer with the contents of the merged workbook
    const mergedBuffer = xlsx.write(mergedWorkbook, { type: "buffer" });

    // Send the merged file to the client
    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    );
    res.setHeader("Content-Disposition", 'attachment; filename="merged.xlsx"');
    res.send(mergedBuffer);
  } catch (error) {
    console.error(error);
    res.status(500).send("Error merging Excel files");
  }
});

app.post("/merge", upload.array("files", 100), (req, res) => {

try {

// Read the contents of the uploaded files into memory

const workbook1 = xlsx.readFile(req.files[0].path);

const workbook2 = xlsx.readFile(req.files[1].path);

// Merge the sheets of the two workbooks into a new workbook

const mergedWorkbook = xlsx.utils.book_new();

xlsx.utils.book_append_sheet(

mergedWorkbook,

workbook1.Sheets[workbook1.SheetNames[0]],

"Sheet1"

);

xlsx.utils.book_append_sheet(

mergedWorkbook,

workbook2.Sheets[workbook2.SheetNames[0]],

"Sheet2"

);

// Generate a buffer with the contents of the merged workbook

const mergedBuffer = xlsx.write(mergedWorkbook, { type: "buffer" });

// Send the merged file to the client

res.setHeader(

"Content-Type",

"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

);

res.setHeader("Content-Disposition", 'attachment; filename="merged.xlsx"');

res.send(mergedBuffer);

} catch (error) {

console.error(error);

res.status(500).send("Error merging Excel files");

}

});

As you can see we are reading the path of the first and the second excel file which is uploaded inside the uploads folder. And then we are creating the third excel file where we are creating multiple sheets and each sheet is containing the contents of the xlsx file. And then we are creating the buffer of the resultant xlsx file and downloading it as an attachment inside the browser as shown below

Full Source Code

index.js

const express = require("express");
const xlsx = require('xlsx');

const bodyparser = require("body-parser");

const multer = require("multer");

const app = express();

app.use(express.static("uploads"));

var storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, "uploads");
  },
  filename: function (req, file, cb) {
    cb(null, Date.now() + ".xlsx"); //Appending .jpg
  },
});

var upload = multer({ storage: storage });

app.use(bodyparser.urlencoded({ extended: false }));
app.use(bodyparser.json());

app.get("/", (req, res) => {
  res.sendFile(__dirname + "/index.html");
});

app.post("/merge", upload.array("files", 100), (req, res) => {
  try {
    // Read the contents of the uploaded files into memory
    const workbook1 = xlsx.readFile(req.files[0].path);
    const workbook2 = xlsx.readFile(req.files[1].path);

    // Merge the sheets of the two workbooks into a new workbook
    const mergedWorkbook = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(
      mergedWorkbook,
      workbook1.Sheets[workbook1.SheetNames[0]],
      "Sheet1"
    );
    xlsx.utils.book_append_sheet(
      mergedWorkbook,
      workbook2.Sheets[workbook2.SheetNames[0]],
      "Sheet2"
    );

    // Generate a buffer with the contents of the merged workbook
    const mergedBuffer = xlsx.write(mergedWorkbook, { type: "buffer" });

    // Send the merged file to the client
    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    );
    res.setHeader("Content-Disposition", 'attachment; filename="merged.xlsx"');
    res.send(mergedBuffer);
  } catch (error) {
    console.error(error);
    res.status(500).send("Error merging Excel files");
  }
});

app.listen(5000, () => {
  console.log("App is listening on port 5000");
});

const express = require("express");

const xlsx = require('xlsx');

const bodyparser = require("body-parser");

const multer = require("multer");

const app = express();

app.use(express.static("uploads"));

var storage = multer.diskStorage({

destination: function (req, file, cb) {

cb(null, "uploads");

filename: function (req, file, cb) {

cb(null, Date.now() + ".xlsx"); //Appending .jpg

});

var upload = multer({ storage: storage });

app.use(bodyparser.urlencoded({ extended: false }));

app.use(bodyparser.json());

app.get("/", (req, res) => {

res.sendFile(__dirname + "/index.html");

});

app.post("/merge", upload.array("files", 100), (req, res) => {

try {

// Read the contents of the uploaded files into memory

const workbook1 = xlsx.readFile(req.files[0].path);

const workbook2 = xlsx.readFile(req.files[1].path);

// Merge the sheets of the two workbooks into a new workbook

const mergedWorkbook = xlsx.utils.book_new();

xlsx.utils.book_append_sheet(

mergedWorkbook,

workbook1.Sheets[workbook1.SheetNames[0]],

"Sheet1"

);

xlsx.utils.book_append_sheet(

mergedWorkbook,

workbook2.Sheets[workbook2.SheetNames[0]],

"Sheet2"

);

// Generate a buffer with the contents of the merged workbook

const mergedBuffer = xlsx.write(mergedWorkbook, { type: "buffer" });

// Send the merged file to the client

res.setHeader(

"Content-Type",

"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

);

res.setHeader("Content-Disposition", 'attachment; filename="merged.xlsx"');

res.send(mergedBuffer);

} catch (error) {

console.error(error);

res.status(500).send("Error merging Excel files");

}

});

app.listen(5000, () => {

console.log("App is listening on port 5000");

});