| Quick Start Guide | |
|---|---|
| Tutorial Concept | Learn how to import, modify, and export Excel (.xlsx) files in JavaScript applications. |
| What You Will Need |
NPM Packages: |
| Controls Referenced |
SpreadJS - JavaScript SpreadSheet Component |
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 SpreadJS, a popular client-side JavaScript spreadsheet component.
Add complete JavaScript spreadsheets into your enterprise web apps. Download SpreadJS Now!
How to Import and Export Excel (.XLSX) from a JavaScript Application:
- Set Up the JavaScript Spreadsheet Project
- Add Excel Import Code
- Add Data to the Imported Excel File
- Add a Sparkline
- Add Excel Export Code
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 jquerySpreadJS 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 to a JavaScript App
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 imported and viewed in the JavaScript spreadsheet component like so:
Add Data to the Imported Excel File
For this tutorial we will use the “Profit loss statement” Excel template seen here:
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 to the Imported Excel File
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.
Add Excel Export Code to a JavaScript App
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.
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:
In another article series, we demonstrate how to import/export Excel (.xlsx) spreadsheets in other JavaScript frameworks:
Add complete JavaScript spreadsheets into your enterprise web apps. Download SpreadJS Now!
Tags:
Related Blogs
Try Our JavaScript Spreadsheet Components
SpreadJS Resources
