How to Import and Export Excel XLSX Using JavaScript

January 22 2024

Quick Start Guide
What You Will Need	NPM Packages: @mescius/spread-sheets @mescius/spread-sheets-io file-saver jquery
Controls Referenced	SpreadJS - JavaScript SpreadSheet Component Documentation \| Demos
Tutorial Concept	Importing, modifying, and exporting Excel (.xlsx) files within JavaScript applications.

Quick Start Guide

What You Will Need

NPM Packages:

Controls Referenced

SpreadJS - JavaScript SpreadSheet Component

Documentation | Demos

Tutorial Concept

Importing, modifying, and exporting Excel (.xlsx) files within JavaScript applications.

Many companies rely on spreadsheets, more specifically Microsoft Excel, to help run their business. Whether these are internal spreadsheets or spreadsheets supplied by their customers, it is crucial that these spreadsheets can still be accessed and/or maintained in your JavaScript applications when moving them to the web.

This blog will introduce how you can easily import Excel files in a familiar spreadsheet UI, programmatically make any changes or allow your user to make edits, then export them back out to an Excel file using the popular SpreadJS client-side JavaScript spreadsheet component.

Add complete JavaScript spreadsheets into your enterprise web apps. Download SpreadJS Now!

In this blog, we will cover how to import/export to Excel in JavaScript following these steps:

Set Up the JavaScript Spreadsheet Project
Add Excel Import Code
Add Data to the Imported Excel File
Add a Sparkline
Add Excel Export Code

Import and Export Excel (.xlsx files) in JavaScript Apps

Download the sample application to follow along with the blog

Set Up the JavaScript Spreadsheet Project

To start, we can use the SpreadJS files hosted on NPM. To do this, we can install using a command line argument. Open a command prompt and navigate to the location of your application. There, you can install the required files with one command.

In this case, we need the base Spread-Sheets library, Spread-Sheets-IO, FileSaver and jQuery:

npm i @mescius/spread-sheets @mescius/spread-sheets-io file-saver jquery

SpreadJS isn’t dependent on jQuery, but in this case, we use it for the easy cross-origin-request support, which we will review later. We will also need the FileSaver library, an external library to allow users to save the files where they want, which we have imported in addition to the SpreadJS and jQuery files.

Once those are installed, we can add references to those script and CSS files in a simple HTML file like so:

<!DOCTYPE html>  
<html>  
<head>  
    <title>SpreadJS Import and Export Xlsx</title>
    <script src="./node_modules/jquery/dist/jquery.min.js" type="text/javascript"></script>
    <script src="./node_modules/file-saver/src/FileSaver.js" type="text/javascript"></script>
    <link href="./node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css" rel="stylesheet" type="text/css" />
    <script type="text/javascript" src="./node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js"></script>
    <script  type="text/javascript" src="./node_modules/@mescius/spread-sheets-io/dist/gc.spread.sheets.io.min.js"></script>
</head>  
<body>  
    <div id="ss" style="height:600px; width :100%; "></div>  
</body>  
</html>

Then we can add a script to the page that initializes the SpreadJS Workbook component and a div element to contain it (since the SpreadJS spreadsheet component utilizes a canvas, this is necessary to initialize the component):

    <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 add an input element and a button to allow users to select their Excel (XLSX) file.

<body>
    <div id="ss" style="height:700px; width:100%;"></div>
    <input type="file" id="selectedFile" name="files[]" accept=".xlsx" />
    <button class="settingButton" id="open">Open</button>
</body>

Then we need to add a function to import a file using spreads import method. In this example, we will import the user selected local file in the click event handler for the open button.

document.getElementById('open').onclick = function () {
     var file = document.querySelector('#selectedFile').files[0];
         if (!file) {
             return;
         }
     workbook.import(file);
};

You can do the same thing with an Excel file on a server by reading it from a URL.

An Excel (.xlsx) file can now be importing and viewed in the JavaScript spreadsheet component like so:

Import an Excel xlsx file in a JS application

Add Data to the Imported Excel File

For this tutorial we will use the “Profit loss statement” Excel template seen here:

“Profit loss statement” 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:

<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

document.getElementById("addRevenue").onclick = function () {  
    var sheet = workbook.getActiveSheet();  
    sheet.addRows(newRowIndex, 1);  
    sheet.copyTo(10, 1, newRowIndex, 1, 1, 29, GC.Spread.Sheets.CopyToOptions.style);  
}

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:

var cellText = "Revenue" + revenueCount++;
sheet.setValue(newRowIndex, 1, cellText);

for (var c = 3; c < 15; c++) {  
    sheet.setValue(newRowIndex, 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:

sheet.setFormula(newRowIndex, 15, "=SUM([@[Jan]:[Dec]])")  
sheet.setValue(newRowIndex, 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:

sheet.copyTo(10, 17, newRowIndex, 17, 1, 13, GC.Spread.Sheets.CopyToOptions.formula);

Add 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

var data = new GC.Spread.Sheets.Range(newRowIndex, 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

sheet.setSparkline(newRowIndex, 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 provide 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:

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.

Programmatically Add Data to an Excel File using JavaScript

Add Excel Export Code

Finally, we can add a button to export the file with the added revenue row/s. To handle this, we can invoke the export method built into Spread.Sheets in the export button on click event handler:

            document.getElementById("export").onclick = function () {
                var fileName = $("#exportFileName").val();
                if (fileName.substr(-5, 5) !== '.xlsx') {
                    fileName += '.xlsx';
                }
                var json = JSON.stringify(workbook.toJSON());
                workbook.export(function (blob) {
                    // save blob to a file
                    saveAs(blob, fileName);
                }, function (e) {
                    console.log(e);
                }, {
                    fileType: GC.Spread.Sheets.FileType.excel
                });
            }

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

<input type="text" id="exportFileName" placeholder="Export file name" value="export.xlsx" />

Then we can add a button that calls this function:

<button id="export">Export File</button>

Once you add a revenue row, you can export the file using the Export File button.

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.

Export Excel (.xlsx) from JavaScript Apps

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

Download the blog sample here.

Learn More About this JavaScript Spreadsheet Component

This article only scratches the surface of the full capabilities of SpreadJS, the JS spreadsheet component. Review the documentation to see some of the many available features, and check out our online demos to see the features in action and interact with the sample code. Integrating a spreadsheet component into your applications allows you to customize your users' experience and provide them with familiar spreadsheet functionality without referring them to an external program. To learn more about SpreadJS and the new features added in the latest release check out our release pages and this video: