SAPUI5: MS Excel file upload

[vishalkumar1305]

1.   Introduction

This document contains the functionality to upload the excel file in SAP UI5 application through Gateway. For this I have consumed the custom OData service to upload the file. Users can upload the file from the HTML5 browsers (Google Chrome, IE etc…) and see the results in the desired format (List, Table, ComboBox etc…).

2.   Prerequisite

• Eclipse with SAP UI5 plugin (1.28 or higher).


• Upload OData service to consume.


• Excel sheet with relevant data to upload.

3.   SAP UI5 Control

sap.ui.unified.FileUploader is used to perform the file upload operation.

4.   XML View Code

<core:View xmlns:core="sap.ui.core" xmlns:mvc="sap.ui.core.mvc"

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

    controllerName="views.FileUpload" xmlns:html="http://www.w3.org/1999/xhtml">

    <Page title="CSV File Upload">

        <content>

            <l:SimpleForm editable="true">

                <l:content>

                    <Label text="File Name"></Label>

                    <VBox>

                        <u:FileUploader id="idfileUploader" width="50%"

                        sameFilenameAllowed="false" placeholder="Choose a CSV file"

                        style="Emphasized">

                        </u:FileUploader>

                        <Button text="Upload" press="onUpload"></Button>

                    </VBox>

                </l:content>

            </l:SimpleForm>

            <Table id="idTable" items="{/}">

                <items>

                    <ColumnListItem>

                        <cells>

                            <Text text="{prodno}"></Text>

                            <Text text="{product}"></Text>

                            <Text text="{productname}"></Text>

                            <Text text="{uom}"></Text>

                        </cells>

                    </ColumnListItem>

                </items>

                <columns>

                    <Column>

                        <Text text="Product No"></Text>

                    </Column>

                    <Column>

                        <Text text="Product"></Text>

                    </Column>

                    <Column>

                        <Text text="Product Name"></Text>

                    </Column>

                    <Column>

                        <Text text="Unit of Measure"></Text>

                    </Column>

                </columns>

            </Table>

        </content>

    </Page>

</core:View>

5.   JS Controller Code

sap.ui.controller("views.FileUpload", {



onInit : function() {

this._oBusyDialog = new sap.m.BusyDialog();

},



onUpload : function(e) {



var t=this;

var fU = this.getView().byId("idfileUploader");

var domRef = fU.getFocusDomRef();

var file = domRef.files[0];

var dublicateValue = [];

try {

if (file) {

var that = this;

that._oBusyDialog.open();



/****************To Fetch CSRF Token*******************/



var sUrl="/sap/opu/odata/SAP/Z***SRV/";

$.ajax({

url: sUrl,

type: "GET",

beforeSend: function(xhr)

{

xhr.setRequestHeader("X-CSRF-Token", "Fetch");

},

success: function(data, textStatus, XMLHttpRequest) {

oToken =  XMLHttpRequest.getResponseHeader('X-CSRF-Token');

oHeaders = {

"x-csrf-token" : oToken,

};



/****************To Fetch CSRF Token*******************/





/*******************To Upload File************************/



var oURL = "/sap/opu/odata/SAP/Z***SRV/UploadSet";

$.ajax({

type: 'POST',

url: oURL,

headers: oHeaders,

cache: false,

contentType:  ["csv"],

processData: false,

data: file,

success: function(data){                      



var isIE = false || !!document.documentMode;

if(isIE == true){

var ev_result = data.childNodes[0].lastChild.childNodes[1].textContent;

var ev_error = data.childNodes[0].lastChild.childNodes[0].textContent;  

}

else{

var ev_result = data.getElementsByTagName("entry")[0].children[6].children[1].innerHTML;

var ev_error = data.getElementsByTagName("entry")[0].children[6].children[0].innerHTML;

}





/******* Success  *********/

var valSuccessArray = ev_result.split("//");

var itemsArray = [];

for(var i=0;i<valSuccessArray.length;i++){

var splitVal = valSuccessArray[i].split(",");

var items = {

product : splitVal[0],

productname : splitVal[1],

uom : splitVal[2]   



}

itemsArray.push(items);

}



function removeDuplicates(originalArray, prop) {

var newArray = [];

var lookupObject  = {};



for(var i in originalArray) {

lookupObject[originalArray[i][prop]] = originalArray[i];

}



for(i in lookupObject) {

newArray.push(lookupObject[i]);

}

return newArray;

}



var itemsSuccessArray = removeDuplicates(itemsArray, "product");



for(var k=0;k<itemsSuccessArray.length;k++){

var countK = k+1

var prodcnt = "0000000"+ countK;

prodcnt = prodcnt.substring(prodcnt.length-8);                  

itemsSuccessArray[k].prodno = prodcnt;

}



var jsonModel = new sap.ui.model.json.JSONModel(itemsSuccessArray);

sap.ui.getCore().setModel(jsonModel,'successModel');

that.getView().byId("idTable").setModel(jsonModel);



/******* Error  *********/

that._oBusyDialog.close();

},

error:function(data){

sap.m.MessageToast.show("Error");

that._oBusyDialog.close();

}});

}});

}

} catch(oException) {

jQuery.sap.log.error("File upload failed:\n" + oException.message);

}}

});

6.   Result Screens:

Before file upload



Screenshot of excel sheet to be uploaded



After successful file upload

[gregorw]

Hi Vishal,

hope you are aware of the abap2xlsx Project which would allow you to process also real Excel .xlsx files in the ABAP Backend.

Best regards
Gregor

[Former Member]

It is very helpful to me.Thank you.

[former_member484715]

Very Helpfull. Thank you very much !

[Rakesh_Kr]

Nice article ?

[CharanRavikumar]

Hey!
Really helpful post!
Have a question

 Prerequisite

• Upload OData service to consume.?
  
  Could you provide help with this!
  Please, it would be Helpful!

Thank You!
Charan

[former_member591516]

Hi Vishal,

great blog entry.

Could you provide us with an explanation on how to setup the OData service?

Thank you!

[former_member197071]

This is the copy pasted content from

https://blogs.sap.com/2015/07/17/excel-file-upload-in-sapui5master-data-upload-using-sapui5/

[former_member698472]

Hi Vishal,

There are some undefined errors in the coding. Where and how should you define the constant?