Export Excel (.xlsx extension) file with includes

Question

In my current job, i had a requirement in SAPUI5 to download Table/list/etc control's data into .xlsx excel file extension (not .csv extension).

Please guide me with some good examples or concept.

Thanking you in advance.

Answer 1

I have found one solution to download .xlsx file in sapui5. Please feel free to provide feedback on my work.

First get the data from table/list controls and use below code to generate .xlsx file in sapui5.

some_etc_controller.js

var aExcelData = [
    {
        "ProductId": "1239102",
        "Name": "Power Projector 4713",
        "Category": "Projector",
        "SupplierName": "Titanium",
        "Description": "A very powerful projector with special features for Internet usability, USB",
        "WeightMeasure": 1467,
        "WeightUnit": "g",
        "Price": 856.49,
        "CurrencyCode": "EUR",
        "Status": "Available",
        "Quantity": 3,
        "UoM": "PC",
        "Width": 51,
        "Depth": 42,
        "Height": 18,
        "DimUnit": "cm"
    },
    {
        "ProductId": "2212-121-828",
        "Name": "Gladiator MX",
        "Category": "Graphics Card",
        "SupplierName": "Technocom",
        "Description": "Gladiator MX: DDR2 RoHS 128MB Supporting 512MB Clock rate: 350 MHz Memory Clock: 533 MHz, Bus Type: PCI-Express, Memory Type: DDR2 Memory Bus: 32-bit Highlighted Features: DVI Out, TV Out , HDTV",
        "WeightMeasure": 321,
        "WeightUnit": "g",
        "Price": 81.7,
        "CurrencyCode": "EUR",
        "Status": "Discontinued",
        "Quantity": 10,
        "UoM": "PC",
        "Width": 34,
        "Depth": 14,
        "Height": 2,
        "DimUnit": "cm",
    }
];
// Consider above array of object as a dummy data

    this.fnJSONToXLSXConvertor(aExcelData, <<put_here_xlsx_file_name>>);
}

fnJSONToXLSXConvertor : function(JSONData, ReportTitle) {

    var aData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
    if (aData.length) {
        var aFinalXlsxData,
            aXlsxHeaderData;

        // Array variable to store header data in XLSX file
        aXlsxHeaderData = [];
        aFinalXlsxData = [];

        // Below loop to extract header data
        for ( var iIndex in aData[0]) {
            switch (iIndex) {
            case "ProductId":
                aXlsxHeaderData.push(this.getText("ProductId"));
                break;
            case "Name":
                aXlsxHeaderData.push(this.getText("Name"));
                break;
            ...etc
            <<specify/push your column header from i18n>>
        }
        // Adding column header data in final XLSX data
        aFinalXlsxData.push(aXlsxHeaderData);

        // Below loop to extract data
        for (var i = 0; i < aData.length; i++) {
            // Array variable to store content data in XLSX file
            var aXlsxContentData = [];
            for ( var iIndex in aData[i]) {
                switch (iIndex) {
                case "ProductId":
                case "Name":
                case "Status":
                ...
                <<specify your column name, to extract only particular column data>>
                    aXlsxContentData.push(aData[i][iIndex]);
                    break;
                }
            }
            // Adding content data in final XLSX data
            aFinalXlsxData.push(aXlsxContentData);
        }

        var Workbook = function Workbook() {
            if(!(this instanceof Workbook)) return new Workbook();
            this.SheetNames = [];
            this.Sheets = {};
        }
        var wb = Workbook();
        wb.SheetNames.push(ReportTitle);

        var sheet_from_array_of_arrays = function sheet_from_array_of_arrays(data, opts) {
            var ws = {};
            var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
            for(var R = 0; R != data.length; ++R) {
                for(var C = 0; C != data[R].length; ++C) {
                    if(range.s.r > R) range.s.r = R;
                    if(range.s.c > C) range.s.c = C;
                    if(range.e.r < R) range.e.r = R;
                    if(range.e.c < C) range.e.c = C;
                    var cell = {v: data[R][C] };
                    if(cell.v == null) continue;
                    var cell_ref = XLSX.utils.encode_cell({c:C,r:R});

                    if(typeof cell.v === 'number') cell.t = 'n';
                    else if(typeof cell.v === 'boolean') cell.t = 'b';
                    else if(cell.v instanceof Date) {
                        cell.t = 'n'; cell.z = XLSX.SSF._table[14];
                        cell.v = datenum(cell.v);
                    }
                    else cell.t = 's';

                    ws[cell_ref] = cell;
                }
            }
            if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
            return ws;
        }

        var ws = sheet_from_array_of_arrays(aFinalXlsxData);

        // Setting up Excel column width
        ws['!cols'] = [ 
            {wch:14},
            {wch:12}
            ...     
            <<specify no. of character in cell to set column width if any>>
        ];
        wb.Sheets[ReportTitle] = ws;        // wb.Sheets[ReportTitle] -> To set sheet name

        var wbout = XLSX.write(wb, {bookType:'xlsx', bookSST:true, type: 'binary'});
        var s2ab = function s2ab(s) {
            var buf = new ArrayBuffer(s.length);
            var view = new Uint8Array(buf);
            for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
            return buf;
        };
        saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), ReportTitle + ".xlsx");

    } else {
        MessageBox.error(
            "No data..!",
            {
                styleClass: bCompact? "sapUiSizeCompact" : ""
            }
        );
    }
}

Download and put below specified javascript library file into your sapui5 lib folder.

Blob.js: https://github.com/eligrey/Blob.js/blob/master/Blob.js

xlsx.js:https://github.com/SheetJS/js-xlsx/blob/master/xlsx.js