Skip to Content
Technical Articles
Author's profile photo Kanika Malhotra

Upload data from excel in CAP (Node.js)

This example demonstrates how to upload data from excel into HANA cloud tables. This can be beneficial for mass upload of data using excel.

A fiori elements list report is created to display and upload the data into the tables. Upload button is added as an extension to the List Report Application.

The upload entity in this can be re-used for updating data in multiple tables/entities.

The uploaded data is parsed and inserted into respective entity. We can write validations before upload by internally invoking the custom handlers of the respective entity using srv.run.

const query = INSERT.into(entity).entries(data);
let srv = awaitcds.connect.to(‘StudentsSrv’);
const Result = await srv.run(query);

 

CAP DB and SRV Artifacts :

Database :

entity Students : cuid {
 StudentId: String(6);
 FirstName: String;
 LastName: String;
 DOB: Date;
 Address: String;
}

Service:
service StudentsSrv {
@cds.persistence.skip
@odata.singleton
 entity ExcelUpload {
        @Core.MediaType : 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        excel : LargeBinary;
    };

entity Students as projection on db.Students
}

Service.js: ( Custom Handlers)

srv.on('PUT', “ExcelUpload", async (req, next) => {
        if (req.data.excel) {
            var entity = req.headers.slug;
            const stream = new PassThrough();
            var buffers = [];
            req.data.excel.pipe(stream);
            await new Promise((resolve, reject) => {
                stream.on('data', dataChunk => {
                    buffers.push(dataChunk);
                });
                stream.on('end', async () => {
                    var buffer = Buffer.concat(buffers);
                    var workbook = XLSX.read(buffer, { type: "buffer", cellText: true, cellDates: true, dateNF: 'dd"."mm"."yyyy', cellNF: true, rawNumbers: false });
                    let data = []
                    const sheets = workbook.SheetNames
                    for (let i = 0; i < sheets.length; i++) {
                        const temp = XLSX.utils.sheet_to_json(
                            workbook.Sheets[workbook.SheetNames[i]], { cellText: true, cellDates: true, dateNF: 'dd"."mm"."yyyy', rawNumbers: false })
                        temp.forEach((res, index) => {
                            if (index === 0) return;
                            data.push(JSON.parse(JSON.stringify(res)))
                        })
                    }
                    if (data) {
                            const responseCall = await CallEntity(entity, data);
                            if (responseCall == -1)
                                reject(req.error(400, JSON.stringify(data)));
                            else {
                                resolve(req.notify({
                                    message: 'Upload Successful',
                                    status: 200
                                }));   
                        }
                    }
                });
            });
        } else {
            return next();
        }
    });

 srv.before('POST', 'Students', async (req) => {
       //Custom validations can be put, if required before upload
 };
 srv.on('POST', 'Students', async (req) => {
     //return reponse to excel upload entity .
    });

 async function CallEntity(entity, data) {
    if (entity === Students) {
      //If any custom handling required for a particular entity
    }
    const insertQuery = INSERT.into(entity).entries(data); 
    // This calls the service handler of respective entity. It can be used if any custom 
    validations need to be performed. or else custom handlers can be skipped. 
    
    let srv = await cds.connect.to('StudentsSrv');
    const insertResult = await srv.run(insertQuery);
    let query = SELECT.from(entity);
    await srv.run(query);
    return insertResult; //returns response to excel upload entity
   
};

CAP APP Artifacts: Create a list report application using fiori template and extend the application to add custom upload button on the list page.

Manifest.json
     "controlConfiguration": {
                                "@com.sap.vocabularies.UI.v1.LineItem": {
                                    "actions": {
                                        "Upload": {
                                            "id": "UploadButton",
                                            "text": "Upload",
                                            "press": "com.test.ExtensionController.Upload",
                                            "requiresSelection": false
                                        },
ExcelUploadDialog.fragment.xml:
<core:FragmentDefinition xmlns:core="sap.ui.core"
    xmlns:u="sap.ui.unified"
    xmlns="sap.m">
    <Dialog id="_IDDialog"
        title=“Excel Upload"
        class="sapUiResponsiveContentPadding"
        beforeOpen=".onBeforeOpen"
        afterClose=".onAfterClose">
        <content>
            <u:FileUploader id="uploader"
                fileType="xlsx"
                multiple="false"
                uploadUrl=“StudentsSrv/ExcelUpload/excel"
                fileAllowed=".onFileAllowed"
                fileEmpty=".onFileEmpty"
                uploadComplete=".onUploadComplete"
                typeMissmatch=".onTypeMismatch"
                sendXHR="true"
                useMultipart="false"
                placeholder="Choose a xlsx file..."
                httpRequestMethod="Put" />
        </content>
        <beginButton>
            <Button id="ok"
                text="OK"
                press=".onOk"
                type="Emphasized"
                enabled="false" />
        </beginButton>
        <endButton>
            <Button id="cancel"
                text="Cancel"
                press=".onCancel" />
        </endButton>
    </Dialog>
</core:FragmentDefinition>


ExtensionController:
sap.ui.define(["sap/m/MessageBox", "sap/m/MessageToast", "sap/ui/core/UIComponent"],
    function (MessageBox, MessageToast, UIComponent) {
        "use strict";
        function _createUploadController(oExtensionAPI, Entity) {
            var oUploadDialog;

            function setOkButtonEnabled(bOk) {
                oUploadDialog && oUploadDialog.getBeginButton().setEnabled(bOk);
            }

            function setDialogBusy(bBusy) {
                oUploadDialog.setBusy(bBusy)
            }

            function closeDialog() {
                oUploadDialog && oUploadDialog.close()
            }

            function showError(code, target, sMessage) {
                MessageBox.error("Upload failed", {title: "Error"}
            }

            function byId(sId) {
                return sap.ui.core.Fragment.byId("excelUploadDialog", sId);
            }

            return {
                onBeforeOpen: function (oEvent) {
                    oUploadDialog = oEvent.getSource();
                    oExtensionAPI.addDependent(oUploadDialog);
                },

                onAfterClose: function (oEvent) {
                    oExtensionAPI.removeDependent(oUploadDialog);
                    oUploadDialog.destroy();
                    oUploadDialog = undefined;
                },

                onOk: function (oEvent) {
                    setDialogBusy(true)

                    var oFileUploader = byId("uploader");
                    var headPar = new sap.ui.unified.FileUploaderParameter();
                    headPar.setName('slug');
                    headPar.setValue(Entity);
                    oFileUploader.removeHeaderParameter('slug');
                    oFileUploader.addHeaderParameter(headPar);
                    var sUploadUri = oExtensionAPI._controller.extensionAPI._controller._oAppComponent.getManifestObject().resolveUri(“./StudentsSrv/ExcelUpload/excel")
                    oFileUploader.setUploadUrl(sUploadUri);
                    oFileUploader
                        .checkFileReadable()
                        .then(function () {
                            oFileUploader.upload();
                        })
                        .catch(function (error) {
                            showError("The file cannot be read.");
                            setDialogBusy(false)
                        })
                },

                onCancel: function (oEvent) {
                    closeDialog();
                },

                onTypeMismatch: function (oEvent) {
                    var sSupportedFileTypes = oEvent
                        .getSource()
                        .getFileType()
                        .map(function (sFileType) {
                            return "*." + sFileType;
                        })
                        .join(", ");

                    showError(
                        "The file type *." +
                        oEvent.getParameter("fileType") +
                        " is not supported. Choose one of the following types: " +
                        sSupportedFileTypes
                    );
                },

                onFileAllowed: function (oEvent) {
                    setOkButtonEnabled(true)
                },

                onFileEmpty: function (oEvent) {
                    setOkButtonEnabled(false)
                },

                onUploadComplete: function (oEvent) {
                    var iStatus = oEvent.getParameter("status");
                    var oFileUploader = oEvent.getSource()

                    oFileUploader.clear();
                    setOkButtonEnabled(false)
                    setDialogBusy(false)

                    if (iStatus >= 400) {
                        var oRawResponse;
                        try {
                            oRawResponse = JSON.parse(oEvent.getParameter("responseRaw"));
                        } catch (e) {
                            oRawResponse = oEvent.getParameter("responseRaw");
                        }
                        if (oRawResponse && oRawResponse.error && oRawResponse.error.message) {
                            showError(oRawResponse.error.code, oRawResponse.error.target, oRawResponse && oRawResponse.error && oRawResponse.error.message);
                        }
                    } else {
                        MessageToast.show("File uploaded successfully");
                        oExtensionAPI.refresh()
                        closeDialog();
                    }
                }
            };
        };

        return {
            Upload: function (oBindingContext, aSelectedContexts) {
                this.loadFragment({
                    id: “excelUploadDialog",
                    name: "com.test.ExcelUploadDialog",
                    controller: _createUploadController(this, ‘Students’)
                }).then(function (oDialog) {
                    oDialog.open();
                });
            }
        };
    });

Summary:

This blog contains code snippets useful for uploading data into tables from excel. We have used a single upload entity which can be re-used for upload of data into various entities. The Upload custom handler parses the uploaded excel using File Uploader and helps to insert that data into respective entity.

References:

https://github.com/SAP-samples/cloud-cap-samples-java/blob/main/app/admin/webapp/extension/Upload.js

 

Hope this was useful. Feedbacks are appreciated.

 

Thanks

Kanika

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Amit Kumar
      Amit Kumar

      Very nice blog ..Thank you

      Author's profile photo Kanika Malhotra
      Kanika Malhotra
      Blog Post Author

      Thanks Amit.