Easy Excel Upload in SAPUI5 using OData Service with Base64 format

In this blog, we will explore the process of uploading Excel data into SAPUI5 using OData Service, with Base64 format. The blog covers the necessary configuration steps, coding, and testing, required to successfully upload an Excel file into SAPUI5. With the increasing amount of data being generated and stored in Excel sheets, the need to integrate this data with SAP systems has become crucial. There are several options for uploading the excel using OData service, out of which using Base64 is a better way due to following reasons:

1. In excel upload if one field is missing or has a data type error, upload will not happen. With this method excel upload happens, avoiding the faulty field
2. In normal excel upload a string is passed from frontend, which is to be manually converted to readable format in backend, to parse to table. In case of Base64, the string given to backend can be formatted directly to Json, which can be moved to an internal table

The Working Mechanism

In this section, we will delve into the inner workings of the Excel Upload process in SAPUI5 using OData. The excel file should be of the same format as that of the SAP custom table we have created.

After uploading the file in SAPUI5, data in excel is converted into Json and then to base64 and send the base64 string to the SAP backend.

By receiving the data from the frontend, it is then converted back to Json and stored in an internal table which will be added to the database table.

Backend

1. Create SAP Gateway project in SEGW with suitable entities to handle the stream method.

Entity name CUST_MSTR_UPLOAD

Two properties:

KEY (set as primary key)

VALUE

2. Generate the project

3. In class ZCL_TRANSPORT_MAN_DPC_EXT (from Runtime Artifacts), redefine method CUST_MSTR_UPLOAD_CREATE_ENTITY.

The implementation of Create Entity is as follows:

Steps:

1. Create Excel file for data upload, with the same structure as that of the database table.
2. Data that is passed from UI is to be moved into a Workarea. (‘ls_entity’ in the given code)

• Create an object for utility class CL_HTTP_UTILITY

1. Convert the base64 to Json format
2. Deserialise the Json data to an internal table (json_tab)

Now, we have the data from the excel into our internal table, and can to our database table, in this case it is ZCUST_MSTR.

ZCUST_MSTR:

Excel file to be uploaded:

Register the service and test the service in GW client with POST option. (Transaction /IWFND/MAINT_SERVICE)

Payload:

NOTE: For testing the service, VALUE payload is given manually

By this, the data from Excel sheet is added to the database table.

Frontend:

The below JavaScript function onUpload() converts the excel file into Json; then to base64; pass the base64 to the VALUE entity (with KEY as X) and then send it to the POST method.

#travanleo #sapdevelopment #sapui5 #sapabapdevelopment #sapindia