Skip to Content
Author's profile photo Vishal Kumar

SAPUI5: MS Excel file upload

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

Assigned tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Gregor Wolf
      Gregor Wolf

      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

      Author's profile photo Former Member
      Former Member

      It is very helpful to me.Thank you.

      Author's profile photo Arjun Biswas
      Arjun Biswas

      Very Helpfull. Thank you very much !

      Author's profile photo rakesh singh
      rakesh singh

      Nice article ?

      Author's profile photo Charan Ravikumar
      Charan Ravikumar

      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

      Author's profile photo Sonja Deißenböck
      Sonja Deißenböck

      Hi Vishal,

      great blog entry.

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

      Thank you!

      Author's profile photo Ankur Gokhale
      Ankur Gokhale

      This is the copy pasted content from

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

      Author's profile photo Yu Ota
      Yu Ota

      Hi Vishal,

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