How to Import and Export Excel XLSX Using JavaScript
Tags: javascript, webdev, tutorial, devops
Published: 2022-09-16 19:24:03

---
canonical_url: https://developer.mescius.com/blogs/how-to-import-export-excel-xlsx-using-javascript
description: Learn how to import and export Excel XLSX files using JavaScript. See more from SpreadJS today.
---
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](https://developer.mescius.com/spreadjs).

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

1.  [Set Up the JavaScript Spreadsheet Project](#setup)
2.  [Add Excel Import Code](#import)
3.  [Add Data to the Imported Excel File](#data)
4.  [Add a Sparkline](#sparkline)
5.  [Add Excel Export Code](#export)

![Import and Export Excel (.xlsx files) in JavaScript Apps](//cdn.mescius.io/umb/media/r4wnyype/sjs-io-blog-image-1.png)

[Download the sample application to follow along with the blog](https://developer.mescius.com/media/mwicnnx3/spreadjs-javascript-io-excel.zip)

### <a id="setup"></a>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](https://www.npmjs.com/package/@grapecity/spread-sheets), [Spread-Sheets-IO](https://www.npmjs.com/package/@grapecity/spread-sheets-io), [FileSaver](https://www.npmjs.com/package/file-saver) and [jQuery](https://www.npmjs.com/package/jquery):

```
    npm i @grapecity/spread-sheets @grapecity/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/@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-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](https://developer.mescius.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Workbook#constructor) 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>  
```

* * *

### <a id="import"></a>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](https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Workbook#import). 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](https://developer.mescius.com/blogs/how-to-read-excel-xlsx-files-from-a-url-using-javascript).

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](//cdn.mescius.io/umb/media/1wcfr0gt/import-excel.gif)

* * *

### <a id="data"></a>Add Data to the Imported Excel File

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

![](//cdn.mescius.io/umb/media/21cdqbzm/02-js.png?width=500&height=163.0859375)

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](https://developer.mescius.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Worksheet#copyto) and pass in:

*   the origin and destination row and column indices
*   row and column count
*   the [**CopyToOptions**](https://developer.mescius.com/spreadjs/api/enums/GC.Spread.Sheets.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](https://developer.mescius.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Worksheet#setvalue). 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](https://developer.mescius.com/spreadjs/docs/features/formulas/set-formulas#set-formula-in-cells) 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);  
```

* * *

### <a id="sparkline"></a>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](https://developer.mescius.com/spreadjs/docs/features/cells/work-with-cells#get-ranges-of-selected-cells) to get the data from and some settings for the [sparkline](https://developer.mescius.com/spreadjs/docs/features/sparkmain). 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](https://developer.mescius.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Worksheet#setsparkline) 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](//cdn.mescius.io/umb/media/qn2l00gk/add-data-to-the-excel-file.gif)

* * *

### <a id="export"></a>Add Excel Export Code

<span style="font-weight: 400;">Finally, we can add a button to export the file with the added revenue row/s. To handle this, we can invoke the</span> [**export** method](https://developer.mescius.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Workbook#export) 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](//cdn.mescius.io/umb/media/2eonpewx/export-excel-file.gif)

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 sample here](https://developer.mescius.com/media/mwicnnx3/spreadjs-javascript-io-excel.zip)**.

* * *

### Learn More About this JavaScript Spreadsheet Component

This article only scratches the surface of the full capabilities of [**SpreadJS**](https://developer.mescius.com/spreadjs), the JS spreadsheet component. Review the [documentation](https://developer.mescius.com/spreadjs/docs/overview) to see some of the many available features, and check out our<span> </span>[online demos](https://developer.mescius.com/spreadjs/demos)<span> 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 with the v16 release check out our </span>[release blog](https://developer.mescius.com/blogs/spreadjs-v16-is-released)<span> and this video:</span>

{% embed https://youtu.be/jACaSv6JVAc %}

**In another article series, we demonstrate how to import/export Excel (.xlsx) spreadsheets in other ****JavaScript** **frameworks:**

*   [React](https://developer.mescius.com/blogs/how-to-import-export-excel-xlsx-using-react)
*   [Vue](https://developer.mescius.com/blogs/how-to-import-export-excel-xlsx-using-vue)
*   [Angular](https://developer.mescius.com/blogs/how-to-import-export-excel-xlsx-using-angular)