Issue in Reading Excel (xlsx) File Data in JavaScript/SAPUI5

Question

I am trying to read a .xlsx file in my SAPUI5 application using FileReader api. The UI5 control that I am using to upload the file is sap.ui.unified.FileUploader My requirement is to read the content of the Excel sheet and display its data in a table in my UI5 application.

For this, I create a FileReader instance and call its .readAsBinaryString or .readAsText method and pass my file to it. When the content is read, inside the onload event, the content of the file is displayed in an unreadable format (refer below)

Am I missing something? Or is there a different way of reading Excel data?

Answer 1

Thanks to mkysoft's and this answer. I was able to read the .xlsx file content.

For this, I had to create two files jszip.js and xlsx.js in my project. I created a separate folder for them and mentioned the two files' locations in sap.ui.define section in my controller.

The two libraries are available at CDNJS - JSZip3 and CDNJS - XLSX4 locations.

sap.ui.define([
    "sap/ui/core/mvc/Controller",
    .... //other libraries
    "projectnamespace/foldername/jszip",
    "projectnamespace/foldername/xlsx"
], function (Controller,...., jszip, xlsx)

And then, to read the .xlsx file I added the following code:

    var oFileUploader = sap.ui.getCore().byId("fileUploader"); // get the sap.ui.unified.FileUploader
    var file = oFileUploader.getFocusDomRef().files[0]; // get the file from the FileUploader control
    if (file && window.FileReader) {
        var reader = new FileReader();
        reader.onload = function (e) {
            var data = e.target.result;
            var excelsheet = XLSX.read(data, {
                type: "binary"
            });
            excelsheet.SheetNames.forEach(function (sheetName) {
                var oExcelRow = XLSX.utils.sheet_to_row_object_array(excelsheet.Sheets[sheetName]); // this is the required data in Object format
                var sJSONData = JSON.stringify(oExcelRow); // this is the required data in String format
            });
        };
        reader.readAsBinaryString(file);
    }

Answer 2

You need to parse Excel file in client side or server side. Excel files are not plain text. Old excel files (xls) can not parse without Excel, it has own binary format. But you can parse xlsx files, these are zipped multiple file which contains raw data, image and style files.

• You can use exsting js library for client parse, example post How to parse Excel file in Javascript/HTML5.
• You can use abap2xlsx library for ABAP backend.