Easily Upload Data From Excel To Database Table Using RAP Model

[Former Member]

We will be following the RAP ( Restful Application Programming Model ) & SAP Fiori to create this application.

Let's Start 🙂

Step 1 : We will first Create a database table ( Everything Will Be Created In Eclipse )

Right Click on your package and click on New -> Search for Table and create.

change it as you wish.

Step 2 : Create a Data Definition ( CDS View )

Change it as you want but keep ExcelRowNumber same, as it will be used later.

Step 3 : Create A Consumption View For List View Report and Object Page Details.

Step 4 : Create Behaviour Definition for First View ZLA_EXCELCDS

Don't focus on the comments, i changed my code to make it simpler for you to understand and let you know about few keywords used in RAP. I will tell in future blogs how to use late numbering concept, validations and draft handling.

You can look these terms up in google.

( readonly ) - Keep only the fields which you do not want to change when the edit functionality is used.

Step 5: Create Behaviour Definition For Consumption View

All comments are used in draft handling. You can just delete the comments.

Step 6: Global and Local Classes are created to implement Business Logic specified in Behavior Definition.

Since we did not used any methods, we do not have to worry about this atm.

Step 7: Create Service Definition

All these can be created by right click on package and click on specific object, or you can simply search it.

Behaviour Definition And Consumption Views can be created by right clicking on the specific Data Definition also.

Step 8: Create Service Binding

* Create ODATA v2 UI

And publish the service definition. Preview It and check if you are getting the desired output.

********************************************************************************************************************

Now We Are Moving TO Business Application Studio ( BAS ) and you should have knowledge about Fiori Elements.

Step 9 : Create An New Project In BAS

Go to New -> Project From Template -> SAP Fiori Application -> List Report Page -> Connect to an ODATA Service -> then fill the rest of the fields.

Test the app by right click on the project and Preview Application.

Step 10 : Right Click On Project and Open Guided Development. Add a custom action to a page using extensions.

Now Fill All the fields the same as i am providing:

Function Name : openExcelUploadDialog

Now you would have a folder named ext in your webapp with a controller.

Step 11 : Right click on webapp and create a new folder named ext/fragment. This will create a new folder fragment named inside ext folder.

Now Create an file inside fragment -> ExcelUpload.fragment.xml

"CODE"

<core:FragmentDefinition xmlns="sap.m" xmlns:l="sap.ui.layout" xmlns:core="sap.ui.core" xmlns:u="sap.ui.unified" xmlns:upload="sap.m.upload">

    <Dialog id="uploadDialogSet" title="Excel Upload">

        <content>

            <upload:UploadSet uploadEnabled="true" id="uploadSet" items="{path: '/', templateShareable: false}" fileTypes="xlsx, xls" maxFileNameLength="200" beforeUploadStarts="onBeforeUploadStart" uploadCompleted="onUploadSetComplete" afterItemRemoved="onItemRemoved" terminationEnabled="true">

                <upload:UploadSetItem visibleRemove="true" visibleEdit="false" fileName="{name}" url="/upload">

                    <upload:attributes>

                        <ObjectAttribute title="Uploaded by" text="{user}" active="false"/>

                    </upload:attributes>

                </upload:UploadSetItem>

            </upload:UploadSet>

        </content>

        <buttons>        

            <Button text="Template" press="onTempDownload" icon="sap-icon://download-from-cloud" type="Emphasized"/>

            <Button text="Upload" press="onUploadSet" icon="sap-icon://upload-to-cloud" type="Emphasized"/>

            <Button press="onCloseDialog" text="Cancel" icon="sap-icon://cancel"/>

        </buttons>

        <endButton>

            <Button press=".onCloseDialog" text="Ok"/>

        </endButton>    

    </Dialog>

</core:FragmentDefinition>

Step 12: Change ListReportExt.controller.js CODE to

"CODE"

sap.ui.define(["sap/ui/core/Fragment", "sap/m/MessageToast", "xlsx"],

    function (Fragment, MessageToast, XLSX) {

        "use strict";

        return {

            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: "crudappp.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) {

                if (!this.excelSheetsData.length) {

                    MessageToast.show("Select file to Upload");

                    return;

                }

                var that = this;

                var oSource = oEvent.getSource();

                var fnAddMessage = function () {

                    return new Promise((fnResolve, fnReject) => {

                        that.callOdata(fnResolve, fnReject);

                    });

                };

                var mParameters = {

                    sActionLabel: oSource.getText()

                };

                this.extensionAPI.securedExecution(fnAddMessage, mParameters);

                this.pDialog.close();

            },

            onTempDownload: function (oEvent) {

                var oModel = this.getView().getModel();

                var oBuilding = oModel.getServiceMetadata().dataServices.schema[0].entityType.find(x => x.name === 'Employees');

                var propertyList = ['EmpId', 'EmpAge', 'EmpName', 'EmpPos', 'EmpAddress', 'OfficeLocation'];

                var excelColumnList = [];

                var colList = {};

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

                const ws = XLSX.utils.json_to_sheet(excelColumnList);

                const wb = XLSX.utils.book_new();

                XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

                XLSX.writeFile(wb, 'RAP - Employees.xlsx');

                MessageToast.show("Template File Downloading...");

            },

            onCloseDialog: function (oEvent) {

                this.pDialog.close();

            },

            onBeforeUploadStart: function (oEvent) {

            },

            onUploadSetComplete: function (oEvent) {

                var oFileUploader = Fragment.byId("excel_upload", "uploadSet");

                var oFile = oFileUploader.getItems()[0].getFileObject();

                var reader = new FileReader();

                var that = this;

                reader.onload = (e) => {

                    let xlsx_content = e.currentTarget.result;

                    let workbook = XLSX.read(xlsx_content, { type: 'binary' });

                    var excelData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets["Sheet1"]);

                    workbook.SheetNames.forEach(function (sheetName) {

                        that.excelSheetsData.push(XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]));

                    });

                    console.log("Excel Data", excelData);

                    console.log("Excel Sheets Data", that.excelSheetsData);

                };

                reader.readAsBinaryString(oFile);

                MessageToast.show("Upload Successful");

            },

            onItemRemoved: function (oEvent) {

                this.excelSheetsData = [];

            },

            callOdata: function (fnResolve, fnReject) {

                var oModel = this.getView().getModel();

                oModel.setUseBatch(false);

                var payloadArray = [];

                var successMessages = []; // Array to hold success messages

                this.excelSheetsData[0].forEach((value, index) => {

                    var payload = {

                        "EmpId": value["Id"],

                        "EmpAge": value["Age"],

                        "EmpName": value["Name"],

                        "EmpPos": value["Position"],

                        "EmpAddress": value["Address"],

                        "OfficeLocation": value["Office"]

                    };

                    payload.ExcelRowNumber = (index + 1);

                    payloadArray.push(payload);

                });

                var promises = payloadArray.map(payload => {

                    return new Promise((resolve, reject) => {

                        oModel.create("/Employees", payload, {

                            success: function (result) {

                                console.log(result);

                                successMessages.push(`Employee Created with ID: ${result.EmpId}`);

                                resolve(); // Resolve the promise once success message is pushed

                            },

                            error: function (error) {

                                console.error("Error:", error);

                                reject(error);

                            }

                        });

                    });

                });

                Promise.all(promises)

                    .then(() => {

                        // All OData calls have completed successfully

                        var oMessageManager = sap.ui.getCore().getMessageManager();

                        successMessages.forEach(message => {

                            var oMessage = new sap.ui.core.message.Message({

                                message: message,

                                persistent: true,

                                type: sap.ui.core.MessageType.Success

                            });

                            oMessageManager.addMessages(oMessage);

                        });

                        fnResolve();

                    })

                    .catch(error => {

                        // Handle errors if any of the OData calls fail

                        console.error("Error:", error);

                        fnReject(error);

                    });

            }      

        };

    });

Make Changes According to this Information:

crudappp is my project name.

Employees is my entity set name.

In the var payload, left hand side should contain entity names which are called in odata and Right hand side should contain the excel column names.

The Application Will not work still, we need to add some modules and tooling to enable excel functionality and other stuff.

Step 13: Right Click on project -> open in integrated terminal

Run these commands one by one:

1 - npm i ui5-tooling-modules

2 - npm i xlsx

Step 14: Make Changes in package.json file as follows shown in highlighted code:

MY CODE BELOW:

Step 14: Make Changes in UI5.yaml file and add the highlighted code:

MY CODE BELOW :

->Now Your Application Should Work ( Being Provided the Right Data in excel format )

->If there is no file created in ext folder as xlsx.js. Create it .

->I have six columns in my excel sheet named in var payload on Right Hand Side.

-> There will be an error in package.json (  "@sap/ux-specification": "UI5-1.108" )

I Hope It Works For You And If You Have Any Questions, feel free to ask in comments.

P.S -> I am a Software Trainee in SAP and I'm still learning about new things. I came across a great blog Excel Upload Using RAP and tried to implement this. There were some issues with the code which i have fixed and you can also read the comments on that blog, it has a lot of solutions to issues in the code. It is a great blog and everything is explained briefly. Make sure to Comment if you face any issues in my blog.

Thanks 🙂 , I Will Be Posting More Awesome Solutions.

[Pranjali_Sharma]

Template functionality is not working...there may be some issue in Controller code

[Lokesh_Adhikari]

Ya Pranjali i know i skipped some steps of controller. I Directly gave you the controller code without explaining the steps involved. I Will Be posting this Blog again with Improved Understanding And More Pictures.

🙂 Stay Tuned...

[Lokesh_Adhikari]

This Was My Old Account. Now I Will Be Posting Solutions With This Account.

I Will Reupload This Solution With Better Details        🙂