Technical Articles
Excel Upload using RAP: Part -3
Links to other blogs post in this series –
Introduction
In continuous to the previous blog post (Excel Upload using RAP: Part -2 | SAP Blogs) post where I have discussed on adding a custom action & its action handler using the Fiori Guided Development Tool.
This is the final blog post of this series, to develop a solution on uploading data to custom database table using SAP RAP Model using the Fiori.
In this post, we will do the rest of the coding for uploading the excel file using the following higher level steps –
- Installing NPM Module – UI5 Tooling Module for using a third party NPM module in our Fiori App.
- Installing NPM Module – XLSX Js for extracting the data from excel file.
- Calling our OData service after extracting the data from excel file
- For calling OData Service in Fiori Element Application, I will be using the Extension API.
- Displaying the validation message if any. which we have done in RAP OData service using the MessageManager.
- By using the Extension API, the capturing of the messages can be handled by the MessageManager becomes easier. For more example on MessageManager click here.
What is UI5 Tooling Module?
It allows to use the NPM Package names as AMD(Asynchronous Module Definition) for module definition & consumption.
Sample Code for AMD –
sap.ui.define(["sap/m/MessageToast"],
function (MessageToast){
"use strict";
return {
sampleMethod: function(oEvent) {
MessageToast.show("Hello")
}
};
});
Why XLSX.js?
This NPM Module reads the excel file then converts into JS Object and also to generate excel file from JS Objects.
Why Extension API?
It is used to extend the generated Fiori Element Application with new custom features which are not supported by Fiori Element Application. And the method SecuredExecution of Extension API is used to perform the operations.
Note: The node modules – UI5 Tooling Module & XLSX Js are open source projects and not maintained by SAP.
Installing the NPM Modules
Step – 1: Installing the UI5 Tooling
In the terminal run the command –
npm i ui5-tooling-modules
Step – 2: Installing the XLSX Js module
In the terminal run the command –
npm i xlsx
Step – 3: Configuring the UI5 Tooling Module – package.json file, add the highlighted code in the file
Step – 4: Configuring the UI5.yaml file, add the highlighted code in the file
Step – 5: once the above steps have been done, modify the Listreportcontroler.js file’s openExcelUploadDialog with the addition of a line console.log to display the npm module xlsx version as follows –
sap.ui.define(["sap/ui/core/Fragment"],
function (Fragment){
"use strict";
return {
openExcelUploadDialog: function(oEvent) {
var oView = this.getView();
if (!this.pDialog) {
Fragment.load({
id: "excel_upload",
name: "v2.pgms.building.ext.fragment.ExcelUpload",
type: "XML",
controller: this
}).then((oDialog) => {
var oFileUploader = Fragment.byId("excel_upload", "uploadSet");
oFileUploader.removeAllItems();
this.pDialog = oDialog;
this.pDialog.open();
})
.catch(error => alert(error.message));
} else {
var oFileUploader = Fragment.byId("excel_upload", "uploadSet");
oFileUploader.removeAllItems();
this.pDialog.open();
}
},
onUploadSet: function(oEvent) {
console.log("Upload Button Clicked!!!")
/* TODO: Read excel file data */
},
onTempDownload: function (oEvent) {
console.log("Template Download Button Clicked!!!")
/* TODO: Excel file template download */
},
onCloseDialog: function (oEvent) {
this.pDialog.close();
},
onBeforeUploadStart: function (oEvent) {
console.log("File Before Upload Event Fired!!!")
/* TODO: check for file upload count */
},
onUploadSetComplete: function (oEvent) {
console.log("File Uploaded!!!")
/* TODO: Read excel file data*/
},
onItemRemoved:function (oEvent) {
console.log("File Remove/delete Event Fired!!!")
/* TODO: Clear the already read excel file data */
}
};
});
Step – 6: Adding the code for uploading the excel file contents to a JSON object in the file and to download the Excel template which will be used for file upload ListReportExt.controller.js
sap.ui.define(["sap/ui/core/Fragment", "sap/m/MessageToast","xlsx"],
function (Fragment, MessageToast, XLSX){
"use strict";
return {
// this variable will hold the data of excel file
excelSheetsData: [],
openExcelUploadDialog: function(oEvent) {
console.log(XLSX.version)
var oView = this.getView();
if (!this.pDialog) {
Fragment.load({
id: "excel_upload",
name: "v2.pgms.building.ext.fragment.ExcelUpload",
type: "XML",
controller: this
}).then((oDialog) => {
var oFileUploader = Fragment.byId("excel_upload", "uploadSet");
oFileUploader.removeAllItems();
this.pDialog = oDialog;
this.pDialog.open();
})
.catch(error => alert(error.message));
} else {
var oFileUploader = Fragment.byId("excel_upload", "uploadSet");
oFileUploader.removeAllItems();
this.pDialog.open();
}
},
onUploadSet: function(oEvent) {
console.log("Upload Button Clicked!!!")
/* TODO:Call to OData */
},
onTempDownload: function (oEvent) {
// get the odata model binded to this application
var oModel = this.getView().getModel();
// get the property list of the entity for which we need to download the template
var oBuilding = oModel.getServiceMetadata().dataServices.schema[0].entityType.find(x => x.name === 'BuildingsType');
// set the list of entity property, that has to be present in excel file template
var propertyList = ['BuildingId', 'BuildingName', 'NRooms', 'AddressLine',
'City', 'State', 'Country'];
var excelColumnList = [];
var colList = {};
// finding the property description corresponding to the property id
propertyList.forEach((value, index) => {
let property = oBuilding.property.find(x => x.name === value);
colList[property.extensions.find(x => x.name === 'label').value] = '';
});
excelColumnList.push(colList);
// initialising the excel work sheet
const ws = XLSX.utils.json_to_sheet(excelColumnList);
// creating the new excel work book
const wb = XLSX.utils.book_new();
// set the file value
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
// download the created excel file
XLSX.writeFile(wb, 'RAP - Buildings.xlsx');
MessageToast.show("Template File Downloading...");
},
onCloseDialog: function (oEvent) {
this.pDialog.close();
},
onBeforeUploadStart: function (oEvent) {
},
onUploadSetComplete: function (oEvent) {
// getting the UploadSet Control reference
var oFileUploader = Fragment.byId("excel_upload", "uploadSet");
// since we will be uploading only 1 file so reading the first file object
var oFile = oFileUploader.getItems()[0].getFileObject();
var reader = new FileReader();
var that = this;
reader.onload = (e) => {
// getting the binary excel file content
let xlsx_content = e.currentTarget.result;
let workbook = XLSX.read(xlsx_content, { type: 'binary' });
// here reading only the excel file sheet- Sheet1
var excelData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets["Sheet1"]);
workbook.SheetNames.forEach(function (sheetName) {
// appending the excel file data to the global variable
that.excelSheetsData.push(XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]));
});
console.log("Excel Data", excelData);
console.log("Excel Sheets Data", this.excelSheetsData);
};
reader.readAsBinaryString(oFile);
MessageToast.show("Upload Successful");
},
onItemRemoved:function (oEvent) {
}
};
});
Step – 7: Add an helper method callOData for calling the RAP oData Service on the click of button Upload. The success or error message are caputured in the Application using the Message Manager
The below code is the final version of the file ListReportExt.controller.js.
sap.ui.define(["sap/ui/core/Fragment", "sap/m/MessageToast","xlsx"],
function (Fragment, MessageToast, XLSX){
"use strict";
return {
// this variable will hold the data of excel file
excelSheetsData: [],
pDialog: null,
openExcelUploadDialog: function(oEvent) {
console.log(XLSX.version)
this.excelSheetsData = [];
var oView = this.getView();
if (!this.pDialog) {
Fragment.load({
id: "excel_upload",
name: "v2.pgms.building.ext.fragment.ExcelUpload",
type: "XML",
controller: this
}).then((oDialog) => {
var oFileUploader = Fragment.byId("excel_upload", "uploadSet");
oFileUploader.removeAllItems();
this.pDialog = oDialog;
this.pDialog.open();
})
.catch(error => alert(error.message));
} else {
var oFileUploader = Fragment.byId("excel_upload", "uploadSet");
oFileUploader.removeAllItems();
this.pDialog.open();
}
},
onUploadSet: function(oEvent) {
// checking if excel file contains data or not
if (!this.excelSheetsData.length) {
MessageToast.show("Select file to Upload");
return;
}
var that = this;
var oSource = oEvent.getSource();
// creating a promise as the extension api accepts odata call in form of promise only
var fnAddMessage = function () {
return new Promise((fnResolve, fnReject) => {
that.callOdata(fnResolve, fnReject);
});
};
var mParameters = {
sActionLabel: oSource.getText() // or "Your custom text"
};
// calling the oData service using extension api
this.extensionAPI.securedExecution(fnAddMessage, mParameters);
this.pDialog.close();
},
onTempDownload: function (oEvent) {
// get the odata model binded to this application
var oModel = this.getView().getModel();
// get the property list of the entity for which we need to download the template
var oBuilding = oModel.getServiceMetadata().dataServices.schema[0].entityType.find(x => x.name === 'BuildingsType');
// set the list of entity property, that has to be present in excel file template
var propertyList = ['BuildingId', 'BuildingName', 'NRooms', 'AddressLine',
'City', 'State', 'Country'];
var excelColumnList = [];
var colList = {};
// finding the property description corresponding to the property id
propertyList.forEach((value, index) => {
let property = oBuilding.property.find(x => x.name === value);
colList[property.extensions.find(x => x.name === 'label').value] = '';
});
excelColumnList.push(colList);
// initialising the excel work sheet
const ws = XLSX.utils.json_to_sheet(excelColumnList);
// creating the new excel work book
const wb = XLSX.utils.book_new();
// set the file value
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
// download the created excel file
XLSX.writeFile(wb, 'RAP - Buildings.xlsx');
MessageToast.show("Template File Downloading...");
},
onCloseDialog: function (oEvent) {
this.pDialog.close();
},
onBeforeUploadStart: function (oEvent) {
},
onUploadSetComplete: function (oEvent) {
// getting the UploadSet Control reference
var oFileUploader = Fragment.byId("excel_upload", "uploadSet");
// since we will be uploading only 1 file so reading the first file object
var oFile = oFileUploader.getItems()[0].getFileObject();
var reader = new FileReader();
var that = this;
reader.onload = (e) => {
// getting the binary excel file content
let xlsx_content = e.currentTarget.result;
let workbook = XLSX.read(xlsx_content, { type: 'binary' });
// here reading only the excel file sheet- Sheet1
var excelData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets["Sheet1"]);
workbook.SheetNames.forEach(function (sheetName) {
// appending the excel file data to the global variable
that.excelSheetsData.push(XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]));
});
console.log("Excel Data", excelData);
console.log("Excel Sheets Data", this.excelSheetsData);
};
reader.readAsBinaryString(oFile);
MessageToast.show("Upload Successful");
},
onItemRemoved:function (oEvent) {
this.excelSheetsData = [];
},
// helper method to call OData
callOdata: function (fnResolve, fnReject) {
// intializing the message manager for displaying the odata response messages
var oModel = this.getView().getModel();
// creating odata payload object for Building entity
var payload = {};
this.excelSheetsData[0].forEach((value, index) => {
// setting the payload data
payload = {
"BuildingName": value["Building Name"],
"NRooms": value["No of Rooms"],
"AddressLine": value["Address Line"],
"City": value["City"],
"State": value["State"],
"Country": value["Country"]
};
// setting excel file row number for identifying the exact row in case of error or success
payload.ExcelRowNumber = (index + 1);
// calling the odata service
oModel.create("/Buildings", payload, {
success: (result) => {
console.log(result);
var oMessageManager = sap.ui.getCore().getMessageManager();
var oMessage = new sap.ui.core.message.Message({
message: "Building Created with ID: " + result.BuildingId,
persistent: true, // create message as transition message
type: sap.ui.core.MessageType.Success
});
oMessageManager.addMessages(oMessage);
fnResolve();
},
error: fnReject
});
});
}
};
});
Application Preview
Please watch the below video for the demo of the created Fiori Element Application –
Conclusion
And there this the last blog post of this series, hope you have learned something new.
Thanks for reading this post, I would like to read your thoughts in the comments !!
Excellent post!
I had a problem when I am trying to do Deploy
Can you help me?
error
package file
ui5.yaml file
Hello,
bug has been fixed.
https://github.com/ui5-community/ui5-ecosystem-showcase/issues/623
Hi Namasivayam Mani
Nice blogs. However, this is not working for me. I also get the error in the npm run build.
Process Failed With Error - Maximum call stack exceeded.
It looks like a circular reference. I think some information is missing on how to add xslx to the package.json and the ui5.yaml file. The comments from Jocelio Pereira shows the package.json, so i tried that but it is still not working:
Can you please share your package.json and ui5.yaml files? Maybe you can upload the code to Github?
Thanks,
Jay Malla
In your blog, you do the following:
npm i ui5-tooling-modules
npm i xslx
However, you only show the screenshot with ui5-tooling-modules
I carried out all the steps exactly as described in this blog but the I keep getting this error message - I have run the npm command to install xlsx and also updated the the package.json and yaml files
Hi Amit
Have you check if the xlsx.js is loaded in the Source Tab?
B&R,
Rex
Hi how is RAP applicable here .It's more rather all in UI5 than RAP. Do we have any utility by which we can upload/download any file ( .TXT CSV /EXCEL) into SAP and vice versa. I need to send the upload file in AL11 too .Please suggest /help if any.Thanks.
BR,
Somnath.
Hi, this is not working for me when i run the app from BTP but working perfectly fine from BAS on preview mode.
I get xlsx not found issue in network tab, Could you share the config i am missing to make it work from BTP as well.
Same issue here. you found any solution ?
I had the same issue after deployment to BTP.
These steps solved the issue for me:
Hi Namasivayam Mani,
Thank you for your post.
Can we do the same for UPDATE and DELETE with excel?
Hi Mani
Thanks for your great blog, hope to see more.
B&R,
Rex