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);
}}
});