Excel Upload using RAP: Part -3

Links to other blogs post in this series –

1. Excel Upload using RAP: Part – 1
2. Excel Upload using RAP: Part – 2
3. Excel Upload using RAP: Part – 3

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 –

1. Installing NPM Module – UI5 Tooling Module for using a third party NPM module in our Fiori App.
2. Installing NPM Module – XLSX Js for extracting the data from excel file.
3. 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.
4. 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 */          
        }
    };
});

Run the application in preview mode you can see the XLSX version in the console and the XLSX.js file in the resource tab

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 !!