Skip to Content
Author's profile photo Prabaharan Asokan

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

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Herman Lin
      Herman Lin

      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

      Author's profile photo Mahesh Palavalli
      Mahesh Palavalli

      you have mentioned that you will cover something in each part, but i couldnt find any information except the images?

      Author's profile photo Prabaharan Asokan
      Prabaharan Asokan
      Blog Post Author

      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.

      Author's profile photo Prabaharan Asokan
      Prabaharan Asokan
      Blog Post Author

      Hi Mahesh,
      I will post my follow up blogs soon. Thanks.

      Regards
      Praba

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Hi Prabaharan Asokan

      Can you Share the Documents for this requirement.

       

      Author's profile photo Florian Henninger
      Florian Henninger

      Soon is something stretchy 🙂

      ~Florian

      Author's profile photo Prabaharan Asokan
      Prabaharan Asokan
      Blog Post Author

      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

      Author's profile photo Vigneshwar DHONAPATI
      Vigneshwar DHONAPATI

      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.

      Author's profile photo Prabaharan Asokan
      Prabaharan Asokan
      Blog Post Author

      Hi Vignesh,

      Thanks for sharing the code snippets.

      Regards

      Prabha

      Author's profile photo Nitin Mishra
      Nitin Mishra

      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.

      Author's profile photo Vigneshwar DHONAPATI
      Vigneshwar DHONAPATI

      Prabaharan Asokan

      Do we have any class or FM to upload XLS file from Gateway?

      Thanks in Advance!!

      Author's profile photo Prabaharan Asokan
      Prabaharan Asokan
      Blog Post Author

      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.

       

      Author's profile photo Shalinee Gupta
      Shalinee Gupta

      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.

      Author's profile photo Vigneshwar DHONAPATI
      Vigneshwar DHONAPATI

      Shalinee,

      You can use above code in create stream method to read .XLSX file.

      Author's profile photo nitish chawla
      nitish chawla

      And we are still waiting for 3 blogs to complete 😛

      Hope your access is working. 😉