JavaScript is a versatile platform that allows easy customization of client-side scripting tools. In some applications, it is useful to have some sort of spreadsheet interface that is easy to code and maintain. The Spread.Sheets client-side JavaScript spreadsheet component, part of the SpreadJS package, is perfect for this. You can import and export Excel files, and provide users with an interface to interact with those files, all in pure JavaScript. In this tutorial, I will show you how easy it is to add a Spread.Sheets component to an HTML page and import an Excel file into it.

The download for this sample can be found here

How to Import/Export Excel files using JavaScript and Spread.Sheets
The finished page after importing and editing an Excel file.


Set Up the Project



Create a new HTML page and add references to the Spread.Sheets script and the CSS files that are included in your SpreadJS download:

<code class="language-html">
<!DOCTYPE html>
<html>
<head>
    <title>Spread.Sheets ExcelIO</title>

    <script src="http://code.jquery.com/jquery-2.1.3.min.js" type="text/javascript"></script>
    <script src="http://code.jquery.com/ui/1.11.4/jquery-ui.min.js" type="text/javascript"></script>

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

</head>
<body>
    <div id="ss" style="height:600px ; width :100%; "></div>
</body>
</html>


Then add a script to the page that initializes the Spread.Sheets component, and a div element to contain it (since Spread.Sheets utilizes a canvas, this is necessary to initialize the component):

<code class="language-html">
    <script type="text/javascript">
        $(document).ready(function () {
            var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
        });
    </script>
</head>
<body>
    <div id="ss" style="height:600px ; width :100%; "></div>
</body>


Add Excel Import Code



We need to create an instance of the client-side ExcelIO component that we can use to actually open the file:

<code class=”language-javascript”>
var excelIO = new GC.Spread.Excel.IO();


Then we need to add a function to import a file. In this example, we import a local file, but you can do the same thing with a file on a server.

If you’re importing a file from a server, you need to reference the location. The following is an example of an input element where the user can enter the location of the file:

<code class=”language-html”>
<input type="text" id="importUrl" value="http://www.testwebsite.com/files/TestExcel.xlsx" style="width:300px" />


Once you have that, you can directly access that value in script code:

<code class=”language-javascript”>
var excelUrl = $("#importUrl").val();


The following code for the import function just uses a local file for the "excelUrl" variable:

<code class=”language-javascript”>
function ImportFile() {
    var excelUrl = "./test.xlsx";

    var oReq = new XMLHttpRequest();
    oReq.open('get', excelUrl, true);
    oReq.responseType = 'blob';
    oReq.onload = function () {
        var blob = oReq.response;
        excelIO.open(blob, LoadSpread, function (message) {
            console.log(message);
        });
    };
    oReq.send(null);
}
function LoadSpread(json) {
    jsonData = json;
    workbook.fromJSON(json);

    workbook.setActiveSheet("Revenues (Sales)");
}


Regardless of whether you are referencing a file on a server or locally, you will need to add the following to your script inside the $(document).ready function:

<code class=”language-javascript”>
$(document).ready(function () {
    $.support.cors = true;
    workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
    //...
});


Adding Data to the Excel File



In this tutorial, we import a local file that uses the “Profit loss statement” Excel template.

How to Import/Export Excel files using JavaScript and Spread.Sheets
The Excel Template


Now we can use Spread.Sheets script to add another revenue line into this file. Let’s add a button to the page that will do just that:

<code class=”language-html”>
<button id="addRevenue">Add Revenue</button>


We can write a function for the click event handler for that button to add a row and copy the style from the previous row in preparation for adding some data. To copy the style, we will need to use the copyTo function and pass in:


  • the origin and destination row and column indices

  • row and column count

  • the CopyToOptions value for style



<code class=”language-javascript”>
document.getElementById("addRevenue").onclick = function () {
    var sheet = workbook.getActiveSheet();
    sheet.addRows(11, 1);
    sheet.copyTo(10, 1, 11, 1, 1, 29, GC.Spread.Sheets.CopyToOptions.style);
}


All of the following script code for adding data and a Sparkline will be contained within this button click event handler. For most of the data, we can use the setValue function. This allows us to set a value in a sheet in Spread by passing in a row index, column index, and value:

<code class=”language-javascript”>
sheet.setValue(11, 1, "Revenue 8");

for (var c = 3; c < 15; c++) {
    sheet.setValue(11, c, Math.floor(Math.random() * 200) + 10);
}


Set a SUM formula in column P to match the other rows and set a percentage for column Q:

<code class=”language-javascript”>
sheet.setFormula(11, 15, "=SUM([@[Jan]:[Dec]])")
sheet.setValue(11, 16, 0.15);


Lastly, we can copy the formulas from the previous rows to the new row for columns R through AD using the copyTo function again, this time using CopyToOptions.formula:

<code class=”language-javascript”>
sheet.copyTo(10, 17, 11, 17, 1, 13, GC.Spread.Sheets.CopyToOptions.formula);


Adding a Sparkline



Now we can add a sparkline to match the other rows of data. To do this, we need to provide a range of cells to get the data from and some settings for the sparkline. In this case, we can specify:


  • the range of cells we just added data to

  • settings to make the sparkline look like the other sparklines in the same column



<code class=”language-javascript”>
var data = new GC.Spread.Sheets.Range(11, 3, 1, 12);
var setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
setting.options.seriesColor = "Text 2";
setting.options.lineWeight = 1;
setting.options.showLow = true;
setting.options.showHigh = true;
setting.options.lowMarkerColor = "Text 2";
setting.options.highMarkerColor = "Text 1";


After that, we call the setSparkline method and specify:


  • a location for the sparkline

  • the location of the data

  • the orientation of the sparkline

  • the type of sparkline

  • the settings we created



<code class=”language-javascript”>
sheet.setSparkline(11, 2, data, GC.Spread.Sheets.Sparklines.DataOrientation.horizontal, GC.Spread.Sheets.Sparklines.SparklineType.line, setting);


If you were to try running the code now, it might seem a little slow because the workbook is repainting every time data is changed and styles are added. To drastically speed it up and increase performance, Spread.Sheets provides the ability to suspend painting and the calculation service. Let’s add the code to suspend both before adding a row and its data, and then resume both after:

<code class=”language-javascript”>
workbook.suspendPaint();
workbook.suspendCalcService();
//...
workbook.resumeCalcService();
workbook.resumePaint();


Once we add that code, we can open the page in a web browser and see the Excel file load into Spread.Sheets with an added revenue row.

Important: Keep in mind that Chrome doesn’t allow you to open local files for security purposes, so you need to use a web browser like Firefox to successfully run this code. Alternatively, if you load a file from a website URL, it should open fine in any browser.

How to Import/Export Excel files using JavaScript and Spread.Sheets
The Excel Template


How to Import/Export Excel files using JavaScript and Spread.Sheets
The page with a button for adding a revenue row


Adding Excel Export Code



Finally, we can add a button to export the file with the added row. To do this, we can use the client-side ExcelIO code built into Spread.Sheets:

<code class=”language-javascript”>
function ExportFile() {
    var fileName = $("#exportFileName").val();
    if (fileName.substr(-5, 5) !== '.xlsx') {
        fileName += '.xlsx';
    }
    var json = JSON.stringify(workbook.toJSON());

    excelIO.save(json, function (blob) {
        saveAs(blob, fileName);
    }, function (e) {
        if (e.errorCode === 1) {
            alert(e.errorMessage);
        }
    });
}


That code gets the export file name from an exportFileName input element. We can define it and let users name the file like so:

<code class=”language-html”>
<input type="text" id="exportFileName" placeholder="Export file name" value="export.xlsx" />


Then we can add a button that calls this function:

<code class=”language-html”>
<button id="export">Export File</button>


<code class=”language-javascript”>
document.getElementById("export").onclick = function () {
    ExportFile();
}


How to Import/Export Excel files using JavaScript and Spread.Sheets
The finished page after importing and editing an Excel file.


Once you add a revenue row, you can export the file using the Export File button. Make sure to add the FileSaver external library to allow users to save the file where they want:

<code class=”language-html”>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2014-11-29/FileSaver.min.js"></script>


When the file is successfully exported, you can open it in Excel and see that the file looks like it did when it was imported, except there is now an extra revenue line that we added.

How to Import/Export Excel files using JavaScript and Spread.Sheets
The exported file loaded into Excel


This is just one example of how you can use Spread.Sheets to add data to your Excel files and then export them back to Excel with simple JavaScript code.

You can learn more about Spread.Sheets and download your 30-day evaluation here.