Playing around with Excel files(.xlsx) in SAP Gateway & SAPUI5
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
Hi Prabaharan,
Your blog is now published and visible in SAP Community already. Please kindly take note that new platform Community does not have a specified sub space for each area but only Tag. So your blod is being tagged with SAP Gateway.
Regards,
Herman
SAP Community Moderator
you have mentioned that you will cover something in each part, but i couldnt find any information except the images?
Hi Mahesh,
Sorry about that. Unfortunately I have lost my SCN access and i was not able to post any content. I have restored my access recently. Thanks.
Hi Mahesh,
I will post my follow up blogs soon. Thanks.
Regards
Praba
Prabaharan, were you able to connect to BPC MS via OData?
I'm interested in extract data from BPC MS using RESTful services.
Thanks
Jesse
Hi Prabaharan Asokan
Can you Share the Documents for this requirement.
Soon is something stretchy 🙂
~Florian
Hi Florian,
Sorry about that. Unfortunately I have lost my SCN access and i was not able to post any content. I have restored my access recently. Thanks.
Regards
Prabha
Hi All,
Please find below code for .XLSX upload form SAP Gateway and UI5.
And use this code create stream method.
DATA im_filename TYPE string.
DATA im_media_resource TYPE xstringval.
DATA im_mime_type TYPE char100.
* * Internal tables declaration
DATA:
lt_worksheets TYPE STANDARD TABLE OF string,
lt_contents TYPE string_table.
* Local variables declaration
DATA:
lv_name TYPE string,
lv_string TYPE string,
* lv_msg TYPE string,
lv_flag TYPE boolean.
* References declarations
DATA:
lref_excel TYPE REF TO cl_fdt_xl_spreadsheet,
lref_excel_core TYPE REF TO cx_fdt_excel_core,
lref_data TYPE REF TO data.
* Field symbols declarations
FIELD-SYMBOLS:
<fs_table> TYPE table,
<fs_data> TYPE any,
<fs_data_str> TYPE any,
<fs_comp> TYPE any,
<fs_output> TYPE string.
DATA lo_tech_read_request_context TYPE REF TO /iwbep/cl_sb_gen_read_aftr_crt.
DATA ls_key TYPE /iwbep/s_mgw_tech_pair.
DATA lt_keys TYPE /iwbep/t_mgw_tech_pairs.
DATA lv_msg_success TYPE string.
DATA lv_msg_error TYPE string.
DATA lv_entityset_name TYPE string.
FIELD-SYMBOLS: <ls_data> TYPE any.
DATA ls_entity TYPE REF TO data.
IF iv_entity_name = ‘ETUpload’.
* Map request input fields to function module parameters
im_mime_type = is_media_resource-mime_type.
im_media_resource = is_media_resource-value.
im_filename = iv_slug.
* im_filename = ‘ew Microsoft Excel Worksheet.xlsx’.
TRY.
* Create object of class to read .xlsx file contents
CREATE OBJECT lref_excel
EXPORTING
document_name = im_filename
xdocument = im_media_resource.
CATCH cx_fdt_excel_core INTO lref_excel_core.
* CLEAR lv_msg.
* Call method to get error message text
CALL METHOD lref_excel_core->if_message~get_text
RECEIVING
result = lv_msg_error.
*<< Display error message returned in lv_msg >>
RETURN.
ENDTRY.
* Call method to get list of worksheets in the .xlsx file
lref_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING
worksheet_names = lt_worksheets ).
* Condition to check whether .xlsx file has any active worksheets
IF lt_worksheets IS NOT INITIAL.
* Read active worksheet
READ TABLE lt_worksheets INDEX 1 INTO lv_name.
ELSE.
*<< Display error message >>
RETURN.
ENDIF.
* Get reference of .xlsx file contents in the active worksheet
lref_data = lref_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lv_name ).
* Fetch all records in the active worksheet
ASSIGN lref_data->* TO <fs_table>.
* Prepare exporting table with .xlsx file contents
IF <fs_table> IS NOT ASSIGNED.
*<< Display error message >>
RETURN.
ENDIF.
* Loop dynamic table to prepare final table contents to pass in exporting parameter
LOOP AT <fs_table> ASSIGNING <fs_data>.
* Initialize flag
lv_flag = abap_true.
WHILE lv_flag = abap_true.
* Read columnwise entries
ASSIGN COMPONENT sy-index OF STRUCTURE <fs_data> TO <fs_comp>.
IF <fs_comp> IS NOT ASSIGNED.
lv_flag = abap_false.
* Exit the loop when a row ends
EXIT.
ELSE.
* Concatenate each cell data in a row into string seperated by ‘||’
CONCATENATE lv_string <fs_comp> INTO lv_string SEPARATED BY ‘||’.
ENDIF.
* Unassign field symbol
UNASSIGN <fs_comp>.
ENDWHILE.
* Shift final string having a row left by 2 places to remove leading ‘||’
SHIFT lv_string LEFT BY 2 PLACES.
* Append prepared row data to exporting parameter
APPEND lv_string TO lt_contents.
* Clear variable having row data
CLEAR lv_string.
ENDLOOP.
DATA:lt_final_contents TYPE STANDARD TABLE OF zvtest1.
DATA:ls_contents TYPE zvtest1.
* Loop internal table to split records and fill in target internal table
LOOP AT lt_contents ASSIGNING <fs_output>.
SPLIT <fs_output>
AT ‘||’
INTO ls_contents-vkey
ls_contents-vdesc
ls_contents-video
ls_contents-test.
ls_contents-mandt = sy-mandt.
* Append split records in internal table
APPEND ls_contents TO lt_final_contents.
ENDLOOP.
IF lt_final_contents IS NOT INITIAL.
delete lt_final_contents INDEX 1.
MODIFY zvtest1 FROM TABLE lt_final_contents.
IF sy-subrc EQ 0.
lv_msg_success = ‘Data updated successfully’.
ELSE.
lv_msg_error = ‘Unable to update Table’.
ENDIF.
ENDIF.
Hi Vignesh,
Thanks for sharing the code snippets.
Regards
Prabha
Hi Vignesh,
I tried to use the same code as explained by but getting exception when trying to create the object of the class to read excel content. I have tried passing the correct mime type but that still does not work. Could you please tell me how are you uploading the file.
Prabaharan Asokan
Do we have any class or FM to upload XLS file from Gateway?
Thanks in Advance!!
Vignesh,
Standard class CL_FDT_SPREADSHEET be used as you mentioned rightly. But I think that should not be used for all the generic purposes since that is specifically built for BRF plus functionalities. Thanks.
Hi Vignesh,
I have used the same code snipped which you mentioned, is it fine to use CL_FDT_SPREADSHEET class in create stream? or any other generic class/FM are there to read Excel data? I couldn't find anything better than this.
Shalinee,
You can use above code in create stream method to read .XLSX file.
And we are still waiting for 3 blogs to complete 😛
Hope your access is working. 😉