Upload/Read .xlsx file in SAP Web Dynpro

This document explains how to read contents from .xlsx file. It explains reading file contents from .xlsx file by code snippets. Following are the steps explaining the procedure:-

1. Define a Web Dynpro component with name for example ‘ZZDEV_WD_TEST’.
2. Define File Upload UI element and a Button UI element in the main view.
3. Create context node with attributes FILENAME (string), FILETYPE (string), FILECONTENTS (xstring).
4. Bind these to the File Upload UI element. Bind FILECONTENTS to DATA attribute, FILENAME to FILENAME attribute and FILETYPE to MIMETYPE attribute.
5. Browse the .xlsx file and click on upload button.
6. On action of upload button read the context node defined in step 3.
7. Follow below code to read the browsed .xlsx file.

* Internal tables declaration

  DATA:

        lt_worksheets                             TYPE STANDARD TABLE OF string,

        lt_contents                                 TYPE string_table,

        lt_final_contents                          TYPE <target structure table type>.

* Structures declarations

  DATA:

        ls_return                                      TYPE bapiret1,

        ls_contents                                  TYPE <target structure>,

        ls_file_upload                               TYPE wd_this->element_file_upload.

* Local variables declaration

  DATA:

        lv_name                                       TYPE string,

        lv_string                                       TYPE string,

        lv_msg                                         TYPE string,

        lv_flag                                          TYPE boolean,

        lv_message                                  TYPE string.

* 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,

        lref_dref                                        TYPE REF TO data,

        lo_nd_file_upload                          TYPE REF TO if_wd_context_node,

        lo_el_file_upload                           TYPE REF TO if_wd_context_element.

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

* navigate from <CONTEXT> to <FILE_UPLOAD> via lead selection

   lo_nd_file_upload = wd_context->get_child_node( name = wd_this->wdctx_file_upload ).

* get element via lead selection

   lo_el_file_upload = lo_nd_file_upload->get_element( ).

* get all declared attributes

   lo_el_file_upload->get_static_attributes(

     IMPORTING

       static_attributes = ls_file_upload ).

  TRY.

*     Create object of class to read .xlsx file contents

      CREATE OBJECT lref_excel

        EXPORTING

          document_name = ls_file_upload-filename

          xdocument         = ls_file_upload-filecontents.

    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.

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

*   Loop internal table to split records and fill in target internal table

    LOOP AT lt_contents ASSIGNING <fs_output>.

*     Split file contents returned at ‘||’

      SPLIT <fs_output>

        AT ‘||’      

        INTO ls_contents-col1

    ls_contents-col2

    ls_contents-col3……..

*     Append split records in internal table

      APPEND ls_contents TO lt_final_contents.

    ENDLOOP.

   7. Contents will be appended to internal table LT_FINAL_CONTENTS. Define a structure in SE11 with fields corresponding to upload file structure and declare          LS_ CONTENTS of this type. Define a table type in SE11 with this structure and declare LT_FINAL_CONTENTS of this type.

   8. Finally LT_FINAL_CONTENTS will have all the records present in the browsed .xlsx file.

Hope this solves reading file contents from .xlsx file.