How to Import a Single Excel Sheet in JavaScript

October 24 2017
0 Comments

When working with Excel workbooks in SpreadJS, sometimes only one sheet from a workbook is relevant to your application and what your users are focusing on. In this case, you can use a hidden workbook to load the entire Excel workbook, and then get one of the sheets out of that workbook for your users to interact with.

In this post, I’ll show you how to accomplish that using the client-side ExcelIO library and two instances of Spread.Sheets on a page:

a hidden one for loading the workbook
the other for displaying the selected sheet

Set up the project

You can follow along and create your own project, or use my sample project: SJS Single Sheet Loading Sample.zip

To start using Spread.Sheets, add the main JS library and CSS files in the head section of your HTML file. In this case we are also loading an Excel file, so we also need to add the JS library for Client-Side ExcelIO:

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

    <link href="http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.10.3.1.css" rel="stylesheet" type="text/css" />
    <script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.10.3.1.min.js"></script>
    <script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/interop/gc.spread.excelio.10.3.1.min.js"></script>

</head>
</body>
</html>

If you are deploying your page, you also need to add your license key to the head section:

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

To finish the setup, add two DIV elements to the body to host the Spread.Sheets instances:

the hidden one (display:none) to hold the workbook
the visible instance to show a sheet

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

Then we can add some code to the script to initialize those two instances as well as the Client-Side ExcelIO:

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

Add ExcelIO Code

In this page, we add code to let the user select the workbook that they want to get a sheet from. To do this we can add some HTML inputs:

<input type="file" name="files[]" id="fileDemo" accept=".xlsx,.xls" />
<input type="button" id="loadExcel" value="Import" onclick="ImportFileStart()" />

For the button to actually start loading the Excel file, we can write a function called ImportFileStart to get the Excel file from the input element and load its JSON into the hidden workbook:

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

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

In this application, we add a drop-down menu that shows up after a user loads a workbook so they can look at a specific sheet. To do this, we add some HTML elements to the page and create a function in the script called CreateSheetSelect.

<div id="sheetSelection" style="display:none;">
    <p style="display:inline">Select sheet to load: </p>
    <select id="sheetSelect" style="display:inline"></select>
</div>

function CreateSheetSelect() {
    var sheetSelectionDiv = document.getElementById('sheetSelection');
    sheetSelectionDiv.style.display = "inline";
    var dropDown = document.getElementById('sheetSelect');
    for (var s = 0; s < hiddenWorkbook.getSheetCount(); s++) {
        dropDown.options[dropDown.options.length] = new Option(hiddenWorkbook.sheets[s].name(), s);
    }     
}

Now we can call the CreateSheetSelect function in the ImportFileStart function, just after the hiddenWorkbook.fromJSON(workbookObj); line.

CreateSheetSelect();

When the user selects a workbook to load, the drop-down menu shows up with a list of sheets they can load in the visible Spread.Sheets instance.

Now we implement a function called CopySheet that fires when the user changes the selection. This function loads the Sheet from the workbook at the specified sheet index. However, we also need to get the styles from the original workbook and add them to the new workbook, since we are only loading one sheet:

function CopySheet() {
    spread.removeSheet(0);
    spread.addSheet();
    var sheetNumber = document.getElementById('sheetSelect').value;

    var sheetJson = JSON.stringify(hiddenWorkbook.getSheet(sheetNumber).toJSON());
    spread.suspendPaint();
    hiddenWorkbook.getNamedStyles().forEach(function (namedStyle) {
        spread.addNamedStyle(namedStyle);
    })
    spread.getSheet(0).fromJSON(JSON.parse(sheetJson));
    spread.resumePaint();
}