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-modulesStep - 2: Installing the XLSX Js module
In the terminal run the command -
npm i xlsxStep - 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 !!
- SAP Managed Tags:
12 Comments
