How to Import and Export Excel XLSX Using JavaScript

Chelsea Devereaux - Sep 16 '22 - - Dev Community

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.

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

  1. Set Up the JavaScript Spreadsheet Project
  2. Add Excel Import Code
  3. Add Data to the Imported Excel File
  4. Add a Sparkline
  5. 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 @grapecity/spread-sheets @grapecity/spread-sheets-io file-saver jquery
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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);
    };
Enter fullscreen mode Exit fullscreen mode

 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:

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>
Enter fullscreen mode Exit fullscreen mode

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);  
    }
Enter fullscreen mode Exit fullscreen mode

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);  
    }
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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";
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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
                    });
                }
Enter fullscreen mode Exit fullscreen mode

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" />
Enter fullscreen mode Exit fullscreen mode

Then we can add a button that calls this function:

    <button id="export">Export File</button>
Enter fullscreen mode Exit fullscreen mode

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 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 with the v16 release check out our release blog and this video:

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

Image
💥Bitcoin (BTC) Price Prediction for June 29
Image
🤯Toncoin (TON) Price Prediction for June 29
Image
🔥Bitcoin Price Analysis: Does a 30% Fear & Greed Index Signals Bottom?
Image
💥Bitcoin (BTC) New Retail Addresses Hits 352,124, Will Price Breakout?
Image
🤑XRP Price Recovery: Will XRP Rank Under Top 5 in 2024?
Image
🔥ChatGPT Predicts XRP To Hit $4, If It Breaks Out Symmetrical Triangle Pattern
Image
Revolutionizing Real Estate with Room Visualization Apps
Image
Forging Impenetrable AWS Identities: Safeguarding the Root User and IAM Users

cloudcomputing, cloudskills, aws, cloudsecurity
Image
Engaging in the Pick and Pop Versatility for Modern Centers

basketball, coachingtechniques, center, offensivestrategies
Image
Creating a simple referral System in ExpressJS

javascript, node, mongodb, api
Image
Revolutionizing Supply Chain Management with Blockchain Technology

blockchain, supplychain, technology, innovation
Image
Bootstrap vs React JS: Complementary Tools for Frontend Mastery.
Image
React: Best Frontend Framework 2024

webdev, reactjsdevelopment, beginners, programming
Image
Comparing Svelte and Vue.js: A Tale of Two Frontend Technologies
Image
An article on JavaScript and TypeScript
Image
Effective Shot Blocking Timing and Technique

basketball, defense, shotblocking, skills
Image
Exploring React and Angular for Modern Web Development
Image
Higher Order Functions and how it relates to life

techlife, webdev, javascript, beginners
Image
Revolutionizing Industries The Marvels of 3D Printing Technology

3dprinting, technology, innovation, manufacturing
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .