DEV Community

Cover image for How to Merge Excel XLSX Workbooks in Your JavaScript Application
Chelsea Devereaux for MESCIUS inc.

Posted on • Updated on • Originally published at grapecity.com

How to Merge Excel XLSX Workbooks in Your JavaScript Application

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.

Download the sample.

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

Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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"));
    }
Enter fullscreen mode Exit fullscreen mode

Load Excel Files in JavaScript

Load XLSX 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>
Enter fullscreen mode Exit fullscreen mode

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();
    }
Enter fullscreen mode Exit fullscreen mode

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);
        });
    }
Enter fullscreen mode Exit fullscreen mode

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);
    }
Enter fullscreen mode Exit fullscreen mode

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();
    }
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)