How to Add Excel Spreadsheets to an Oracle APEX Application Using JavaScript

Developers can spend hours upon hours creating an interactive grid with amazing reporting using features of Oracle APEX. Unfortunately, we cannot teach an old dog new tricks; no matter how much training or information is provided to users, they will download the data from the application and use it in a separate Microsoft Excel workbook instead of interacting with the APEX grid.

Microsoft Excel has been around since 1982 and users are humans, and humans are creatures of habit. They are comfortable with Excel’s interface, keyboard shortcuts, charts, and formulas. Many industries even depend on Excel workbooks for certain calculations or reports. Having several separate workbooks with data that then needs to be manually entered back into the APEX app causes ineffective workflow, higher chance for human error and a disconnect from having the most up to date data available to your users. Odds are users will even ask _“Can I just import the Excel file back into the APEX app?”... I can feel your stress level rising and I am here to save your sanity, let me introduce you to SpreadJS!

SpreadJS is the World’s #1 selling JavaScript spreadsheet API solution! With no dependencies to Excel, SpreadJS delivers a true Excel-like spreadsheet experiences including similar UI, fast importing and exporting of Excel (.xlsx) files, and a high-speed calculation engine that supports over 500 functions. SpreadJS also includes tables, charts, shapes, sparklines, conditional formatting, filtering, and much more!

It takes 3 steps to add SpreadJS to an APEX application page and implement Excel file importing and exporting capabilities:

1. Add Script and CSS SpreadJS files
2. Set up the APEX page
3. Initialize SpreadJS and the Excel IO module

Step 1: Add Script and CSS SpreadJS files

In an APEX workspace, create a new application if needed, then click the page you want to edit. Add SpreadJS’s scripts and CSS file URLs to the pages JavaScript and CSS properties.

Step 2: Set up APEX page

To set up your APEX application to handle SpreadJS, first create a new Region and set the Static ID to be “ss”. This region will be used as SpreadJS’s host element.

Then, under the regions Appearance tab, select Template Options. When the Template Options dialogue pops up check to Remove Body Padding and set the wanted Body Height. For this example, I have set the height to 480px and hide the header.

Next add buttons, a file browser, and password APEX items for users to interact with and invoke the SpreadJS API to import and export an Excel file:

• File Browser item name UPLOAD_FILE
• Password item named PASSWORD
• Two Button item named IMPORT, with the Static ID set as “import”, and EXPORT, with the Static ID set as “export”.

Note, for this example I have set the Template option to not Display an Icon.

Step 3: Initialize SpreadJS and the Excel IO module

Our final step is to initialize the SpreadJS’s workbook in the host element and the Excel IO module with JavaScript code. This JavaScript code will be added to the page's JavaScript properties section under Execute when Page Loads.

Initialize SpreadJS and the Excel IO module with the following code:

GC.Spread.Sheets.LicenseKey = "<add your SpreadJS distribution key here. Contact us.sales@grapecity to request a temp key>";

window.onload = function () {
    // Initialize SpreadJS and Excel IO
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss").getElementsByClassName("t-Region-body")[0], {calcOnDemand: true});
    var excelIo = new GC.Spread.Excel.IO();
}

Please note: If you do not have a SpreadJS license key set you will observe a license error. Please request an evaluation key from our team: us.sales@grapecity.com

Then using the Excel IO modules open and save methods, add the following code to import and export Excel (.xlsx) files when the previously added buttons are clicked.

GC.Spread.Sheets.LicenseKey = "<add your SpreadJS distribution key here. Contact us.sales@grapecity to request a temp key>";
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss").getElementsByClassName("t-Region-body")[0], {calcOnDemand: true});

    var excelIo = new GC.Spread.Excel.IO();
    // Import Excel file
      document.getElementById('import').onclick = function () {
        var excelFile = document.getElementById("UPLOAD_FILE").files[0];
        var password = document.getElementById('PASSWORD').value;
        // here is excel IO API
        excelIo.open(excelFile, function (json) {
            var workbookObj = json;
                spread.fromJSON(workbookObj);
            }
        , function (e) {
            // process error
            alert(e.errorMessage);
            if (e.errorCode === 2/*noPassword*/ || e.errorCode === 3 /*invalidPassword*/) {
                document.getElementById('password').onselect = null;
            }
        }, {password: password});
    };
// Export Excel file
        document.getElementById('export').onclick = function () {
     var fileName = "export"
        var password = document.getElementById('PASSWORD').value;
        if (fileName.substr(-5, 5) !== '.xlsx') {
            fileName += '.xlsx';
        }

        var json = spread.toJSON();

        // here is excel IO API
        excelIo.save(json, function (blob) {
            saveAs(blob, fileName);
        }, function (e) {
            // process error
            console.log(e);
        }, {password: password});
    };
}

And BOOM! Just like that you can run the APEX application to import and export your Excel files using the SpreadJS JavaScript spreadsheet.

Experience the power of SpreadJS today by checking out our live demos and downloading the latest release here. You can also request a 30-day trial evaluation key by reaching out to our team: us.sales@grapecity.com

Download a trial of SpreadJS to try it out for yourself!