SpreadJS can easily load Excel workbooks and render them on webpages. In some cases, these workbooks may need to be combined, like monthly reports. One way to accomplish this would be to use hidden SpreadJS instances to load all the workbooks first before combining them into one.

Follow along with this blog and download the sample.

Try SpreadJS's spreadsheet components

Download the latest version of SpreadJS

Download Now!

Set Up the Project

To load SpreadJS we will need to add the main JS library and CSS files. Since we are also loading Excel files, we need to add the ExcelIO JS 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, you'll also need to add your 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

For this page, we'll add code to let the user load as many workbooks as they want, and click a button to combine them into one and display 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 load:</p>
    <ul id="workbookList"></ul>
    <input type="button" id="loadWorkbooks" value="Load Workbooks" onclick="LoadWorkbooks()" />
</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);
}

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

function LoadWorkbooks() {
    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: the styling of each workbook needs to be added to the visible SpreadJS instance, or it won’t display correctly, shown in the above function as the call to “spread.addNamedStyle()”.

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

Try SpreadJS's spreadsheet components

Download the latest version of SpreadJS

Download Now!