SAP HANA, HANA XSA

Excel File Upload SAPUI5 App with SAP HANA XSA

webadmin

In this post, we will create a simple SAPUI5 app to upload an Excel file (.xlsx) into SAP HANA XS Advanced table. We will create the multi-target-application that consists of database, NodeJS and SAPUI5 module.

There are three main modules we need to create in SAP Web IDE SAP HANA XS Advanced: database, NodeJS and web module.

Database Module

  • Create database module db.
  • Inside db folder, create a folder called src.
  • Inside src folder, create two folders data and sequence.
  • Under data, create a file called zxlsfileupload_dummy.hdbtable with this content:
COLUMN TABLE "ZXLSFILEUPLOAD_DUMMY" (
	"SEQ_NO" INTEGER CS_INT NOT NULL COMMENT 'Running Sequence Number',
	"DATE" DATE CS_DAYDATE COMMENT 'Date',
	"COUNTRY_CODE" NVARCHAR(2) COMMENT 'Country Code',
	"COMPANY_CODE" NVARCHAR(10) COMMENT 'Company Code',
	"AMOUNT" SMALLDECIMAL COMMENT 'Amount',
	PRIMARY KEY ("SEQ_NO")) 
	COMMENT 'ZXLSFILEUPLOAD_DUMMY'	 
	UNLOAD PRIORITY 5 AUTO MERGE ​
  • Under sequence, create a file called zxlsfileuploadSeqId.hdbsequence with this content:
SEQUENCE "zxlsfileuploadSeqId" START WITH 100
RESET BY SELECT IFNULL(MAX("SEQ_NO"), 100) + 1 FROM "ZXLSFILEUPLOAD_DUMMY"
  • Now create two files respectively under db folder, called data-model.cds and zxlsfileupload.cds.
    data-model.cds
using from '../db/zxlsfileupload';

entity Base {
  key ID : Integer;
}

zxlsfileupload.cds

namespace zxlsfileupload;

@cds.persistence.exists
entity Dummy {
	key SEQ_NO : Integer not null;
	DATE : Date;
	COUNTRY_CODE : String(2);
	COMPANY_CODE : String(10);
	AMOUNT : Decimal(15,2);
};
  • The final structure of db module would be like this:
  • We are done with db module. You can go ahead to build it by doing a right click on the db folder and select Build.

NodeJS Module

  • Create NodeJS module srv.
  • Inside srv folder, create folders: lib, lib > handlers, router, router > routes as shown below:
  • We will define the oData custom exit on the “create” function for the oData zxlsfileupload_dummy.We’ll get the running number from the zxlsfileuploadSeqId and replace the data.SEQ_NO from UI5 with this running number.
    Go ahead to create index.js Inside lib > handler:
/*eslint no-console: 0, no-unused-vars: 0, no-undef:0, no-process-exit:0, new-cap:0*/
/*eslint-env node, es6 */

"use strict";

const uuid = require("uuid/v4");
const cds = require("@sap/cds");

module.exports = function (entities) {
	const {
		catalog
	} = entities;

	const adm = "ODATASERVICEADMIN";

	this.before("CREATE", "zxlsfileupload_dummy", async(User) => {
		console.log("** Create zxlsfileupload_dummy **");
		const {
			data
		} = User;
		console.log(data);

		const dbClass = require(global.__base + "utils/dbPromises");
		var client = await dbClass.createConnection();
		let db = new dbClass(client);

		const statement = await db.preparePromisified(
			`SELECT \"zxlsfileuploadSeqId\".NEXTVAL AS SEQ_NO
							 FROM DUMMY`);
		const dataResults = await db.statementExecPromisified(statement, []);
		console.log(dataResults[0].SEQ_NO);

		data.SEQ_NO = dataResults[0].SEQ_NO;

		return data;
	});
};
  • These are the helper functions to get the user information: WhoAmI andUserInfo. We are not really using it in the UI5 app, but I will just put it here for future use. You can call it from https://<App_URL>:<App_Port>/node/WhoAmIGo ahead to create myNode.js inside router > routes:
/*eslint no-console: 0, no-unused-vars: 0, no-shadow: 0, newcap:0*/
/*eslint-env node, es6 */
"use strict";
var express = require("express");
var async = require("async");

module.exports = function () {
	var app = express.Router();
	var userScope = null;
	
	app.get("/WhoAmI", (req, res) => {
		var scope = `${req.authInfo.xsappname}.Create`;
		if (req.authInfo && !req.authInfo.checkScope(scope)) {
			userScope = "usr";
		} else {
			userScope = "adm";
		}
		
		var result = JSON.stringify({
			userScope: userScope
		});
		res.type("application/json").status(200).send(result);
		
	});
	
	app.get("/getSessionInfo", (req, res) => {
		var userContext = req.authInfo;
		var result = JSON.stringify({
			userContext: userContext
		});
		res.type("application/json").status(200).send(result);
	});
	
	app.get("/userinfo", function(req, res) {
		let xssec = require("@sap/xssec");
		let xsenv = require("@sap/xsenv");
		let accessToken;
		let authWriteScope = false;
		let authReadScope = false;
		let controllerAdminScope = true;
		let userInfo = {
			"name": req.user.id,
			"familyName": req.user.name.familyName,
			"emails": req.user.emails,
			"scopes": [],
			"identity-zone": req.authInfo.identityZone
		};
		function getAccessToken(req) {
			var accessToken = null;
			if (req.headers.authorization && req.headers.authorization.split(" ")[0] === "Bearer") {
				accessToken = req.headers.authorization.split(" ")[1];
			}
			return accessToken;
		}
		accessToken = getAccessToken(req);
		let uaa = xsenv.getServices({
			uaa: {
				tag: "xsuaa"
			}
		}).uaa;
		xssec.createSecurityContext(accessToken, uaa, function(error, securityContext) {
			if (error) {
				console.log("Security Context creation failed");
				return;
			}
			console.log("Security Context created successfully");
			userInfo.scopes = securityContext.scopes;
			console.log("Scope checked successfully");
		});
		return res.type("application/json").status(200).json(userInfo);
	});
	
	return app;
};
  • If the path /node is called, then we route to myNode() function that we defined earlier.Inside router, create index.js:
/*eslint-env node, es6 */
"use strict";

module.exports = (app, server) => {
	app.use("/node", require("./routes/myNode")());
};
  • We will define the oData structure. Under srv folder, create my-service.cds:
//tables
using zxlsfileupload.Dummy as dummy from '../db/data-model';

service CatalogService {
  //** App ** //
  entity zxlsfileupload_dummy @(
	title: '{i18n>zxlsfileupload_dummyService}',
	Capabilities: {
		InsertRestrictions: {Insertable: true},
		UpdateRestrictions: {Updatable: true},
		DeleteRestrictions: {Deletable: true}
	}
  ) as projection on dummy;
 }
  • We also need to create a folder called util under srv folder and create file dbPromises.js.
/*eslint no-console: 0, no-unused-vars: 0, no-shadow: 0, new-cap: 0, dot-notation:0, no-use-before-define:0 */
/*eslint-env node, es6 */
"use strict";

module.exports = class {

	static createConnection() {
		return new Promise((resolve, reject) => {
			const xsenv = require("@sap/xsenv");
			let options = xsenv.getServices({
				hana: {
					tag: "hana"
				}
			});
			var hdbext = require("@sap/hdbext");
			options.hana.pooling = true;
			hdbext.createConnection(options.hana, (error, client) => {
				if (error) {
					console.log(error);
					reject(error);
				} else {
					resolve(client);
				}
			});
		});
	}

	constructor(client) {
		this.client = client;
		this.util = require("util");
		this.client.promisePrepare = this.util.promisify(this.client.prepare);
	}

	preparePromisified(query) {
		console.log(query);
		return this.client.promisePrepare(query);
	}

	statementExecPromisified(statement, parameters) {
		statement.promiseExec = this.util.promisify(statement.exec);
		return statement.promiseExec(parameters);
	}

	loadProcedurePromisified(hdbext, schema, procedure) {
		hdbext.promiseLoadProcedure = this.util.promisify(hdbext.loadProcedure);
		return hdbext.promiseLoadProcedure(this.client, schema, procedure);
	}

	callProcedurePromisified(storedProc, inputParams) {
		return new Promise((resolve, reject) => {
			storedProc(inputParams, (error, outputScalar, ...results) => {
				if (error) {
					console.log(error);
					reject(error);
				} else {
					if (results.length < 2) {
						resolve({
							outputScalar: outputScalar,
							results: results[0]
						});
					} else {
						let output = {};
						output.outputScalar = outputScalar;
						for (let i = 0; i < results.length; i++) { 
							output[`results${i}`] = results[i];
						}
						resolve(output);
					}
				}
			});
		});
	}
};
  • Finally update server.js with this content:
/*eslint no-console: 0, no-unused-vars: 0, no-undef:0, no-process-exit:0*/
/*eslint-env node, es6 */
"use strict";
const port = process.env.PORT || 3000;
const server = require("http").createServer();

const cds = require("@sap/cds");
//Initialize Express App for XSA UAA and HDBEXT Middleware
const xsenv = require("@sap/xsenv");
const passport = require("passport");
const xssec = require("@sap/xssec");
const xsHDBConn = require("@sap/hdbext");
const express = require("express");
global.__base = __dirname + "/";

//logging
var logging = require("@sap/logging");
var appContext = logging.createAppContext();

//Initialize Express App for XS UAA and HDBEXT Middleware
var app = express();

//Compression
app.use(require("compression")({
  threshold: "1b"
}));

//Helmet for Security Policy Headers
const helmet = require("helmet");
// ...
app.use(helmet());
app.use(helmet.contentSecurityPolicy({
  directives: {
    defaultSrc: ["'self'"],
    styleSrc: ["'self'", "sapui5.hana.ondemand.com"],
    scriptSrc: ["'self'", "sapui5.hana.ondemand.com"]
  }
}));
// Sets "Referrer-Policy: no-referrer".
app.use(helmet.referrerPolicy({ policy: "no-referrer" }));

passport.use("JWT", new xssec.JWTStrategy(xsenv.getServices({
	uaa: {
		tag: "xsuaa"
	}
}).uaa));
app.use(logging.middleware({
	appContext: appContext,
	logNetwork: true
}));
app.use(passport.initialize());
var hanaOptions = xsenv.getServices({
	hana: {
		tag: "hana"
	}
});
hanaOptions.hana.pooling = true;
app.use(
	passport.authenticate("JWT", {
		session: false
	}),
	xsHDBConn.middleware(hanaOptions.hana)
);

//CDS OData V4 Handler
var options = {
	driver: "hana",
	logLevel: "error"
};
//Use Auto Lookup in CDS 2.10.3 and higher
//Object.assign(options, hanaOptions.hana, {
//	driver: options.driver
//});

cds.connect(options);

var odataURL = "/odata/v4/zxlsfileupload.CatalogService/";
// Main app
cds.serve("gen/csn.json", {
		crashOnError: false
	})
	.at(odataURL)
	.with(require("./lib/handlers"))
	.in(app)
	.catch((err) => {
		console.log(err);
		process.exit(1);
	});

// Redirect any to service root
app.get("/", (req, res) => {
	res.redirect(odataURL);
});
app.get("/node", (req, res) => {
	res.redirect(odataURL);
});

//Setup Additonal Node.js Routes
require("./router")(app, server);

//Start the Server 
server.on("request", app);
server.listen(port, function () {
	console.info(`HTTP Server: ${server.address().port}`);
});

Web Module

To upload an Excel file, I am using the library from SheetJS. And I have done a little modification to meet our requirement.

  • Go ahead to create web module called web.
  • Create the folder structure as shown below:
  • Copy the updated SheetJS library from my Git, xlsx.js and put it under lib folder.
  • Let’s take a look at the function onValidate in ProjectAdm.controller.js. This function is called upon clicking the browse button. The Excel file is read as binary string and fed into function XLXS.read:
var workbook = XLSX.read(strCSV, {
    type: 'binary'
});

We will only get the data from “Sheet1” and populate the array result_final with SEQ_NO, DATE, COUNTRY_CODE, COMPANY_CODE and AMOUNT data. After that, we will create a data binding from table projectList for each record in array result_final. And finally we call the function onSave to submit the records to database via oData batch create request.

Take note that we are using oData version 4 as defined in manifest.json.

onValidate: function(e) {
    var fU = this.getView().byId("idfileUploader");
    var domRef = fU.getFocusDomRef();
    var file = domRef.files[0];
    var this_ = this;

    var reader = new FileReader();
    reader.onload = function(e) {
        var strCSV = e.target.result;

        var workbook = XLSX.read(strCSV, {
            type: 'binary'
        });

        var result_final = [];
        var result = [];
        workbook.SheetNames.forEach(function(sheetName) {
            console.log(sheetName);
            if (sheetName === "Sheet1") {
                var csv = XLSX.utils.sheet_to_csv(workbook.Sheets[sheetName]);
                if (csv.length) {
                    result.push(csv);
                }
                result = result.join("[$@~!~@$]")
            }
        });

        var lengthfield = result.split("[$@~!~@$]")[0].split("[#@~!~@#]").length;
        console.log("lengthfield: " + lengthfield);

        var len = 0;
        if (lengthfield === 4) {
            for (var i = 1; i < result.split("[$@~!~@$]").length; i++) {
                if (result.split("[$@~!~@$]")[i].length > 0) {

                    var rec = result.split("[$@~!~@$]")[i].split("[#@~!~@#]");
                    if (rec.length > 0) {
                        len = rec[0].trim().length + rec[1].trim().length + rec[2].trim().length + rec[3].trim().length;
                        if (len > 0) {
                            result_final.push({
                                'SEQ_NO': i,
                                'DATE': rec[0].trim(),
                                'COUNTRY_CODE': rec[1].trim(),
                                'COMPANY_CODE': rec[2].trim(),
                                'AMOUNT': rec[3].trim()
                            });
                        }
                    }
                }
            }

            if (result_final.length === 0) {
                fU.setValue("");
                MessageToast.show(this_._getText("LabelMsg1"));
            } else if (result_final.length >= 101) {
                fU.setValue("");
                MessageToast.show(this_._getText("LabelMsg2"));
            } else {
                var oList = this_.byId("projectList"),
                    oBinding = oList.getBinding("items");

                for (var k = 0; k < result_final.length; k++) {
                    oBinding.create({
                        "SEQ_NO": result_final[k].SEQ_NO,
                        "DATE": result_final[k].DATE,
                        "COUNTRY_CODE": result_final[k].COUNTRY_CODE,
                        "COMPANY_CODE": result_final[k].COMPANY_CODE,
                        "AMOUNT": result_final[k].AMOUNT
                    });
                }

                this_.onSave();
                fU.setValue("");
            }
        } else {
            MessageToast.show(this_._getText("LabelMsg3"));
        }
    };

    if (typeof file !== 'undefined') {
        reader.readAsBinaryString(file);
    } else {
        MessageToast.show(this_._getText("LabelMsg4"));
        return;
    }
},
  • The onSave function will take the model zxflModel and submit via batch with batchGroupId.
onSave: function() {
    var oTable = this.byId("projectList"),
        aItems = oTable.getItems();
    var this_ = this;
    var fnSuccess = function() {
        this._setBusy(false);
        MessageToast.show(this._getText("changesSentMessage"));
        this._setUIChanges(false);
    }.bind(this);

    var fnError = function(oError) {
        this._setBusy(false);
        this._setUIChanges(false);
        MessageBox.error(oError.message);
    }.bind(this);

    this._setBusy(true); // Lock UI until submitBatch is resolved.
    var oModel = this.getOwnerComponent().getModel("zxflModel");

    oModel.submitBatch(batchGroupId).then(fnSuccess, fnError);
    BaseController.technicalerror = false; // If there were technical errors, a new save resets them.
},

Running the App

  • Go ahead now to run the NodeJS and web module from Web IDE.
  • Open the web app URL and try to upload the Excel file with the structure shown below. If there is no error, you will see the message “Records have been uploaded successfully”.
  • Open the HDI container and check the table ZXLSFILEUPLOAD_DUMMY and see if the records get updated.

Leave a Reply

Your email address will not be published. Required fields are marked *