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

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.