Skip to Content

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

7 Comments
You must be Logged on to comment or reply to a post.
  • 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

  • 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 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.