Skip to Content
Technical Articles
Author's profile photo Namasivayam Mani

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.

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

 

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jocelio Pereira
      Jocelio Pereira

      Excellent post!

       

      I had a problem when I am trying to do Deploy

      Can you help me?

       

      error

      error

       

      package%20file

      package file

       

      ui5.yaml%20file

      ui5.yaml file

      Author's profile photo Hendrik Poehlmann
      Hendrik Poehlmann

      Hello,

      bug has been fixed.

       

      https://github.com/ui5-community/ui5-ecosystem-showcase/issues/623

      Author's profile photo Bruno Malla
      Bruno Malla

      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:

      {
       ....
          "dependencies": {},
          "devDependencies": {
              "@ui5/cli": "^2.14.1",
              "@sap/ux-ui5-tooling": "1",
              "@sap/ux-specification": "1.96.14",
              "@sap/ux-ui5-fe-mockserver-middleware": "1",
              "rimraf": "3.0.2",
              "ui5-tooling-modules": "^0.3.0",
              "@types/xslx": "0.0.36",
              "xslx": "1.0.0"
          },
      ....
          "ui5": {
              "dependencies": [
                  "@sap/ux-ui5-tooling",
                  "@sap/ux-ui5-fe-mockserver-middleware",
                  "ui5-tooling-modules",
                  "xslx"
              ]
          },
          "sapux": true,
          "sapuxLayer": "CUSTOMER_BASE"
      }
      

       

      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

      Author's profile photo Amit Goyal
      Amit Goyal

      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