Front-end must-read: How to import and export Excel files with SpreadJS in JavaScript

JavaScript occupies an absolute dominant position in the front-end field. At present, it can be used in almost all application fields from browsers to servers, mobile terminals, and embedded. There is a classic saying in the technical circle that "anything that can be implemented in JavaScript will be implemented in JavaScript in the end".
Excel spreadsheets have been widely used across industries since the 1980s, with over 300 million users today, most of whom are familiar with the Excel spreadsheet experience. Many businesses use Excel spreadsheets for data management in various aspects of their business.

In this blog, we will describe how to implement on-page spreadsheet import/export to Excel in JavaScript by following these steps:
Click here to download the complete demo example.

Set up a JavaScript spreadsheet project
Add Excel import code
Add data to imported Excel file
Add sparklines
Add Excel export code

Set up a JavaScript spreadsheet project

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

In this case, we need the basic Spread-Sheets library, Spread-ExcelIO and jQuery:

 npm i @grapecity/spread-sheets @grapecity/spread-excelio jquery

SpreadJS doesn't depend on jQuery, but in this case we use it to provide simple cross-domain request support, which we'll review later.

Once these are installed, we can add references to these scripts and CSS files in our code:

 <!DOCTYPE html>  
<html>  
<head>  
    <title>SpreadJS ExcelIO</title>
    <script src="./node_modules/jquery/dist/jquery.min.js" type="text/javascript"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2014-11-29/FileSaver.min.js"></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-excelio/dist/gc.spread.excelio.min.js"></script>

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

In addition to the SpreadJS and jQuery files, we also need to import the FileSaver library. In order to facilitate subsequent program processing, SpreadJS provides a complete file stream by default. The FileSaver library can be used to convert the file stream into a file and download it locally.

Then we can add a script to the page to initialize the Spread.Sheets component and a div element to contain it (since the SpreadJS spreadsheet component uses a canvas, which is required 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 create an instance of the client ExcelIO component that we can use to open the file:

 var excelIO = new GC.Spread.Excel.IO();

Then we need to add a function to import the file. In this example, we imported a local file, but you can do the same with the file on the server. If importing files from a server, you need to reference this location. Here is an example of an input element where the user can enter the location of a file:

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

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

 var excelUrl = $("#importUrl").val();

The following code that imports the function uses the local file of the "excelUrl" variable:

 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)");  
}

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

 $(document).ready(function () {  
    $.support.cors = true;  
    workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));  
    //...  
});

In this case, we need to enable Cross-Origin-Request-Support because we might load files from URLs. Hence the $.support.cors = true; line, otherwise trying to load it will result in a CORS error.

Add data to imported Excel file

We imported a local file using the Income Statement Excel template for this tutorial.

Now we can add another income line to this file using the Spread.Sheets script. Let's add a button to the page to do this:
<button id="addRevenue">Add Revenue</button>

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

original and target row and column indices
Number of rows and columns
CopyToOptions value of the 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 Sparkline will be included in this button click event handler. For most data, we can use the setValue function. This allows us to set values in the worksheet in Spread by passing in the row index, column index and value:

 sheet.setValue(newRowIndex, 1, "Revenue 8");  
for (var c = 3; c < 15; c++) {  
    sheet.setValue(newRowIndex, c, Math.floor(Math.random() * 200) + 10);  
}

Finally, we can use the copyTo function again to copy the formula from the previous row to a new row in columns R to AD, this time using CopyToOptions.formula:

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

Add sparklines

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

range of cells, we just add the data to

Settings to make sparklines look like other sparklines in the same column

 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:

location of sparklines
the location of the data
Orientation of Sparklines
Types of Sparklines
The setup we created
```
 sheet.setSparkline(11, 2, data, GC.Spread.Sheets.Sparklines.DataOrientation.horizontal, GC.Spread.Sheets.Sparklines.SparklineType.line, setting);
```
If you try to run the code now, it might seem a little slow because the workbook repaints every time you change data and add styles. To significantly speed up and improve performance, Spread.Sheets provides the ability to pause painting and computing services. Let's add code to pause both before adding a row and its data, and resume both after:
```
 workbook.suspendPaint();  
workbook.suspendCalcService();  
//...  
workbook.resumeCalcService();  
workbook.resumePaint();
```
After adding this code, we can open the page in a web browser and see the Excel file loaded into Spread.Sheets and the income row added. Important: Remember that for security reasons Chrome does not allow you to open local files, so you will need a web browser such as Firefox to run this code successfully. Alternatively, loading a file from a website URL should open just fine in any browser.

Add Excel export code

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

 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);  
        }  
    });  
}

The code gets the export file name from the exportFileName input element. We can define it and let the user name the file like this:

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

Then we can add a button to call this function:

 <button id="export">Export File</button>  
document.getElementById("export").onclick = function () {  
    ExportFile();  
}

After adding revenue lines, you can export the file using the "Export File" button. Make sure to add the FileSaver external library to allow users to save files where they want:

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

Once the file is successfully exported, you can open it in Excel and see the file look the same as it was imported, only now we've added an extra income row.

This is just an example of how to use the SpreadJS JavaScript spreadsheet to add data to an Excel file and then export them back to Excel using simple JavaScript code.

In another series of articles, we demonstrate how to import/export Excel spreadsheets in other Javascript frameworks:

React
vue
Angular

The sample download address of this article:
https://gcdn.grapecity.com.cn/forum.php?mod=attachment&aid=MjMzMzU3fGU0NTJlZWEyfDE2NjM3MjYyNTl8NjI2NzZ8OTk3MTg%3D

More pure front-end form online demo examples: https://demo.grapecity.com.cn/spreadjs/gc-sjs-samples/index.html
Pure front-end table application scenario: https://www.grapecity.com.cn/developer/spreadjs#scenarios
Mobile sample (scan code experience): http://demo.grapecity.com.cn/spreadjs/mobilesample/

Front-end must-read: How to import and export Excel files with SpreadJS in JavaScript

Set up a JavaScript spreadsheet project

Add Excel import code

Add data to imported Excel file

Add sparklines

Add Excel export code

葡萄城技术团队

引用和评论

浅谈 IoT 如何助力制造业企业实现数字化落地

前端开发工具

开发插件集合

2024最全ECharts 实战大全（速记版+资源）

Vue.js-方法与事件

Vue使用mixins

startViewTransition的简单示例