Playing around with Excel files(.xlsx) in SAP Gateway & SAPUI5

[former_member184739]

Introduction:

This blog introduces a 3 part learning series to look on how we can deal with excel files of format .xlsx. We will cover how to validate the contents of the file before saving in DB /parse the contents of file and extract the data out of them to bind in UI5 tables/download the internal table details into .xlsx file. This technique can be used as workaround in SAPUI5 since there is no native support in HTML5 to parse excel files.

Let's get started.

Pre-requisites:

SAP system should have at least ECC6.0 EHP6 .Basic knowledge of building OData services using media stream, Deep insert is required. Package SFDT_EXCEL_CORE and classes CL_FDT_XL_SPREADSHEET, CX_FDT_EXCEL_CORE are essential to implement this solution. Also this implementation will work only for .xlsx and NOT .xls since they are not compliant to OXML format.

Part 1: In part 1 we will start with uploading an Excel file into SAP Gateway. We will explore how to convert the uploaded file from XSTRING format into dynamic internal table using standard class CL_FDT_XL_SPREADSHEET.



Part 2: In this part we will cover how we can display the excel file contents in UI5 table during upload. To achieve this, SAPUI5 will transform the file into BASE64 string and then upload to SAP Gateway. Later we will decode/parse the file into JSON format and send them back to caller interface. All these processes will happen in a single OData service call.



Part 3: In this part we will look into the use case of converting the internal table into XSTRING format of .xlsx and download them in SAPUI5.



Finally, I hope I was able to provide some helpful information for people who are looking for the similar functionality earlier in SAPUI5/SAP Gateway. I will post follow up blogs to provide a step-by-step example of putting all the pieces together. Many thanks for reading the content.

Cheers
Prabaharan Asokan