How to Merge Excel XLSX Workbooks in Your JavaScript Application

February 20 2023

SpreadJS, our JavaScript spreadsheet, makes it easy to load Excel workbooks and render them on webpages in your JavaScript applications. In some cases, you may need to combine data from multiple workbooks, for example, monthly sales reports from different departments, into a single workbook. One way to accomplish this would be to use multiple hidden SpreadJS instances to load all the workbooks and then merge them into one spreadsheet.

This blog will show you how to merge multiple Excel workbooks and present them as a single spreadsheet in your JavaScript applications. You will:

Set Up the Project
Load Excel Files in JavaScript
Merge Excel Files in JavaScript

Download the sample.

Ready to Merge Workbooks in your JavaScript Application? Download SpreadJS Now!

Set Up the Project

To load SpreadJS, we will need to add the main JavaScript library and CSS files. Since we are also loading Excel files, we need to add the ExcelIO JavaScript library. This can be done by navigating to the location of the HTML file and installing the SpreadJS files with NPM:

npm i @grapecity/spread-sheets @grapecity/spread-excelio

And then reference those files in the HTML code:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="utf-8" />
    <title>Multiple Sheet Merging</title>

    <link href="./node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css" rel="stylesheet" type="text/css" />
    <script type="text/javascript" src="./node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js"></script>
    <script type="text/javascript" src="./node_modules/@grapecity/spread-excelio/dist/gc.spread.excelio.min.js"></script>
</head>
</html>

If you're deploying your page to something other than localhost, you'll also need to add your license key (you can contact us.sales@grapecity.com to request a temporary license key):

<script> GC.Spread.Sheets.LicenseKey = "<Your Deployment Key>"; </script>

Then we'll add a DIV element to host the Spread.Sheets instance.

<body>
 <div id="ss" style="width: 800px; height: 700px; border: 1px solid gray"></div>
</body>

We'll want to add some code to initialize that Spread instance, ExcelIO, and an array to hold the hidden Spread instances we'll use to load all of the Excel files before combining:

var hiddenWorkbooks, hiddenSpreadIndex, excelIO, spread;
window.onload = function () {
    hiddenSpreadIndex = -1;
    hiddenWorkbooks = new Array();
    excelIO = new GC.Spread.Excel.IO();
    spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
}

Load Excel Files in JavaScript

For this page, we'll add code to let the user load as many workbooks as they want and click a button to merge them into one and display them in SpreadJS. To do this, we can add the following HTML code:

<input type="file" name="files[]" id="fileDemo" accept=".xlsx,.xls" />
<input type="button" id="addWorkbook" value="Add Workbook" onclick="CreateNewSpreadDiv()" />
<div id="workbookListBlock" style="display:none">
    <p>Workbooks to merge:</p>
    <ul id="workbookList"></ul>
    <input type="button" id="mergeWorkbooks" value="Merge Workbooks" onclick="MergeWorkbooks()" />
</div>

The user clicks on the file input to select a file and then clicks the “Add Workbook” button. This creates a new hidden DIV element to hold the SpreadJS instance that will be used to temporarily load the Excel file, and add them to the list of hidden workbooks:

function CreateNewSpreadDiv() {
    hiddenSpreadIndex++;
    var newDiv = document.createElement("div");
    newDiv.style.cssText = "display:none; width: 800px; height: 600px; border: 1px solid gray";
    newDiv.id = "hiddenWorkbook" + hiddenSpreadIndex;
    document.body.appendChild(newDiv);
    var hiddenWorkbook = new GC.Spread.Sheets.Workbook(document.getElementById(newDiv.id));
    hiddenWorkbooks.push(hiddenWorkbook);
    AddWorkbookToImportList();
}

That function then calls another function to load the Excel file into that hidden Spread instance, using ExcelIO code:

function AddWorkbookToImportList() {
    var excelFile = document.getElementById("fileDemo").files[0];
    excelIO.open(excelFile, function (json) {
        var workbookObj = json;
        hiddenWorkbooks[hiddenSpreadIndex].fromJSON(workbookObj);

        AddWorkbookNameElement(document.getElementById("fileDemo").files[0].name);
        document.getElementById("fileDemo").value = "";

    }, function (e) {
        console.log(e);
    });
}

To give the user feedback, we'll show a list of the files that they're going to be combining, shown here as the “AddWorkbookNameElement” function:

function AddWorkbookNameElement(workbookName) {
    if (document.getElementById("workbookListBlock").style.display == "none") {
        document.getElementById("workbookListBlock").style.display = "block";
    }
    var newDiv = document.createElement("LI");
    var textNode = document.createTextNode(workbookName);
    newDiv.appendChild(textNode);
    document.getElementById("workbookList").appendChild(newDiv);
}

Merge Excel Files in JavaScript

Merge Excel XLSX Files in JavaScript

When the user is ready to finally merge all the workbooks into one, they can click on the “Merge Workbooks” button, which copies each sheet from each workbook to the visible Spread instance on the page:

function MergeWorkbooks() {
    for (var w = 0; w < hiddenWorkbooks.length; w++) {
        if (GC.Spread.Sheets.LicenseKey == null) {
            for (var s = 1; s < hiddenWorkbooks[w].getSheetCount(); s++) {
                CopySheet(w, s);
            }
        } else {
            for (var s = 0; s < hiddenWorkbooks[w].getSheetCount(); s++) {
                CopySheet(w, s);
            }
        }
    }
    spread.removeSheet(0);
}

function CopySheet(workbookIndex, sheetIndex) {
    spread.addSheet();
    var sheetJson = JSON.stringify(hiddenWorkbooks[workbookIndex].getSheet(sheetIndex).toJSON());
    spread.suspendPaint();
    hiddenWorkbooks[workbookIndex].getNamedStyles().forEach(function (namedStyle) {
        spread.addNamedStyle(namedStyle);
    });
    spread.getSheet(spread.getSheetCount()-1).fromJSON(JSON.parse(sheetJson));
    spread.resumePaint();
}

One thing to note: if your workbook(s) is using Named Styles, this styling needs to be added to the visible SpreadJS instance, or it won’t display correctly since we are copying over individual sheets. This is shown in the above function and can be added to “spread.addNamedStyle()”.

After that code is added, you can now load multiple Excel workbooks and merge them into one with SpreadJS.

Ready to Merge Workbooks in your JavaScript Application? Download SpreadJS Now!