excelsapui5sheetjs

Sheetjs for SAPUI5; exporting of table data to spreadsheet

There was this one requirement for one of my SAPUI5 app table data to be able to be exported to Excel/spreadsheet. The sap.ui.core.util.Export doesn't meet the standard of exporting data especially it only write data in 1 column.

Sample exported data:

enter image description here

Since SAPUI5 1.50, SAP made sap.ui.export.Spreadsheet but according to one of their experts, the said library is not available for Eclipse development tools (https://answers.sap.com/questions/474315/sapuiexport-missing-in-sap-ui5-version-152.html)

It was then suggested to me to use 3rd party JS library that can export data to spreadsheet and upon researching, I came across with SheetJS. I found this article on how to implement and use the code of SheetJS library in SAPUI5: Export Excel (.xlsx extension) file with includes responsive table data in SAPUI5.

My question: How to add external JS libraries on your SAPUI5 project on Eclipse? Please take note that we are running our SAPUI5 applications on NetWeaver server. I need to understand where to put those JS's based on the environment I have (is it fine to put it/them to webContent/ path?) and how to access them if ever.

Solution

  • Already got the answer by combining all the research I got.

    I found a lot of combinations of external libs to use for the sheetjs to work like xlsx.js - jszip.js or xlsx.full.min.js only or there's also xlsx.full.min.js - FileSaver.js combination.

    I first tried using xlsx.full.min.js and FileSaver combination and it worked! I downloaded both of the library first and copied them to my /webContent/controller path (yes, same path level of my controllers).

    Next thing is to define them in your controller using sap.ui.define Defining 3rd party libraries in SAPUI5 You will also notice that I added 2 line of codes above:

    /* global XLSX:true */
/* global saveAs:true */

    With those 2 line of codes, there will be an error upon call of the libraries. These are the global names of the JS if it's to be called outside of the js class.

    Below is the test code I used to access the 2 JS I've mentioned above:

    onExportToExcel: function() {
    console.log(XLSX);
    var wb = XLSX.utils.book_new();
    wb.Props = {
        Title: "Sample SheetJS",
        Subject: "Test",
        Author: "HPC",
        CreatedDate: new Date(2018, 5, 19)
    };
    wb.SheetNames.push("Test Sheet");
    var ws_data = [
        ['HPC', 'TEST', 'call']
    ]; //a row with 2 columns       
    var ws = XLSX.utils.aoa_to_sheet(ws_data);
    wb.Sheets["Test Sheet"] = ws;

    var wbout = XLSX.write(wb, {
        bookType: 'xlsx',
        type: 'binary'
    });

    function s2ab(s) {
        var buf = new ArrayBuffer(s.length); //convert s to arrayBuffer
        var view = new Uint8Array(buf); //create uint8array as viewer
        for (var i = 0; i < s.length; i++) view[i] = s.charCodeAt(i) & 0xFF; //convert to octet
        return buf;
    }
    saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }), 'test.xlsx');
}

    Output: Output export file

    Feel free to provide feedback on this. There might be other better approach to achieve this goal.