cancel
Showing results for 
Search instead for 
Did you mean: 

How to read the data from Excel Sheet using sap/ui/unified/FileUploader

vijay_kumar49
Active Contributor
0 Kudos

Hello Friends,

I need some help from you. How to read the data from the .xlsx file (Examp: “VijayTest. .xlsx) using sap/ui/unified/FileUploader

Using below code  its works for CVS format. When I try to upload excel file its not working. Please help me

In XML View

<mvc:View xmlns:html="http://www.w3.org/1999/xhtml" xmlns:mvc="sap.ui.core.mvc" xmlns="sap.m" xmlns:u="sap.ui.unified"

xmlns:l="sap.ui.layout" controllerName="SimpleUI5.controller.fisrtView">

<App>

<pages>

<Page id="fileId" title="{i18n>title}">

<content>

<l:VerticalLayout id="vlayoutId">

<u:FileUploader id="fileupload" name="File Uploader" useMultipart="false" sendXHR="true"

  change="uploadFileCSV1" sameFilenameAllowed="false"/>

<Button id="btnUpload" press="uploadFileCSVBtn" text="Upload"></Button>

</l:VerticalLayout>

</content>

</Page>

</pages>

</App>

</mvc:View

  1. Controller.js

uploadFileCSV1:function(oEvent){

var that = this;

var file = oEvent.getParameter("files")[0];

if (file && window.FileReader){

  var reader = new FileReader();

  reader.onload = function(evn) {

var strCSV = evn.target.result; //string in CSV

                    that.csvJSON(strCSV);

};

reader.readAsText(file);

 

}

},

csvJSON:function(csv)

{

var lines = csv.split("\n");

        var result = [];

        var headers = lines[0].split(",");

         for (var i = 1; i < lines.length; i++)

         {

          var obj = {};

          var currentline = lines[i].split(",");

for (var j = 0; j < headers.length; j++)

           {

obj[headers[j]] = currentline[j];

}

result.push(obj);

         }

        // var oStringResult = JSON.stringify(result);

         //var oFinalResult = JSON.parse(oStringResult.replace(/\\r/g, ""));

this.oStringResult = JSON.stringify(result);

this.oFinalResult = JSON.parse(this.oStringResult.replace(/\r/g, ""));

//sap.ui.getCore().getModel().setProperty("/", oFinalResult);

         

},

uploadFileCSVBtn : function(){

var oModel = this.oStringResult;

var finalModel = this.oFinalResult;

}

Excel Sheet Format:

Error Message: Getting Wrong data : var strCSV = evn.target.result; //string in CSV

Regards

Vijay K

Accepted Solutions (0)

Answers (6)

Answers (6)

former_member82178
Active Participant
0 Kudos

Yes, in case of excel it might have issue with below line, and for csv case it's valid.


var lines = csv.split("\n");


Hope below link might be some help.

Best Regards,

MS

vijay_kumar49
Active Contributor
0 Kudos

Madhu,

Problem is coming in this line  var strCSV= e.target.result;



Here Data is print Wrong Data as per Error Message: Screen Shot.

Former Member
0 Kudos

Hi Vijay,

I am not sure what is your requirement is.

If your requirement is attachment where you want to upload the excel file or any other file then you can use this link

https://scn.sap.com/thread/3670662

Check this one , I have done this example where we can store any type of attachment in ecc.

Please check the content in above blog or let me know in jayprakash0070@gamil.com

Thanks,

JP

daniel_ruiz2
Active Contributor
0 Kudos

hi Vijay,

I assume you're doing this for 'browser' only correct? - Case this is true, you should use exceljs (npm) - this will probably work out for you.

Cheers,

Dan.

daniel_ruiz2
Active Contributor
0 Kudos

hi Vijay,

Please don't even thing about running this code on a device...

JS Bin - Collaborative JavaScript Debugging

Hope it helps,

Dan.

former_member232384
Participant
0 Kudos

If you want to read XLSX(or XLS) you need a server-side programming.

It can't be read at client-side program(ex. javascript)

vijay_kumar49
Active Contributor
0 Kudos

Yang

Can you pls. Help me on that code. (Server Side program)

junwu
Active Contributor
0 Kudos

you are using csv or xlsx file?

vijay_kumar49
Active Contributor
0 Kudos

Junwu

That code worked for .csv file. It's not working for .xlxs file.

I'm using .xlxs file

junwu
Active Contributor
0 Kudos
vijay_kumar49
Active Contributor
0 Kudos

JunWu,

U have my personal gmail id. please share me that code.

I followed this thread Read contents of Excel file into Sapui5 table | SCN

i downloaded Download xlsx.js and jszip.js from the above URL and added into the my Application. PFA my application.


I created that application in WEBIDE. Please check and help me.


Controller


sap.ui.define([

    "sap/ui/core/mvc/Controller"

], function(Controller) {

    "use strict";

    jQuery.sap.require("UploadExcel.excelFiles.xlsx");

    jQuery.sap.require("UploadExcel.excelFiles.jszip");

    return Controller.extend("UploadExcel.controller.uploadExcel", {

        uploadFileCSV: function(e) {

            this._uploadFileCSVBtn(e.getParameter("files") && e.getParameter("files")[0]);

        },

        _uploadFileCSVBtn: function(file) {

            if (file && window.FileReader) {

                var reader = new FileReader();

                var result = {},

                    data;

                reader.onload = function(e) {

                    data = e.target.result;

                    var wb = XLSX.read(data, {

                        type: 'binary'

                    });

                    wb.SheetNames.forEach(function(sheetName) {

                        var roa = XLSX.utils.sheet_to_row_object_array(wb.Sheets[sheetName]);

                        if (roa.length > 0) {

                            result[sheetName] = roa;

                        }

                    });

                    return result;

                }

                reader.readAsBinaryString(file);

            }

        }

     

});

});

View

<mvc:View controllerName="UploadExcel.controller.uploadExcel" xmlns:html="http://www.w3.org/1999/xhtml" xmlns:mvc="sap.ui.core.mvc"

    xmlns="sap.m" xmlns:u="sap.ui.unified" xmlns:l="sap.ui.layout">

    <App>

        <pages>

            <Page title="{i18n>title}">

                <content>

                    <l:VerticalLayout id="vlayoutId">

                        <u:FileUploader id="fileupload" name="File Uploader" useMultipart="false" sendXHR="true"

                          change="uploadFileCSV" sameFilenameAllowed="false"/>

                         

                        <Button id="btnUpload" press="uploadFileCSV" text="Upload"></Button>

                    </l:VerticalLayout>

                </content>

            </Page>

        </pages>

    </App>

</mvc:View>

Application Structure:



vijay_kumar49
Active Contributor
0 Kudos

Hello

Any help?