Skip to Content

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./wp-content/uploads/2013/08/browse_268954.jpg
  3. Create context node with attributes FILENAME (string), FILETYPE (string), FILECONTENTS (xstring)./wp-content/uploads/2013/08/context_268997.jpg
  4. Bind these to the File Upload UI element. Bind FILECONTENTS to DATA attribute, FILENAME to FILENAME attribute and FILETYPE to MIMETYPE attribute./wp-content/uploads/2013/08/binding_268998.jpg
  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.

To report this post you need to login first.

17 Comments

You must be Logged on to comment or reply to a post.

    1. Kevin Dass

      Hi Devesh,

                          The blog is helpful. However when tried the logic in a program the CREATE OBJECT lref_exce throws an exception with text “Invalid document format” with DOCUMENTNAME = D:\USERS\KDASS\DESKTOP\TEST.XLSX

      Kevin

      (0) 
      1. Devesh Singh Post author

        Sorry Kevin for very late reply….I think this has to do with the .XLSX formatting of your excel source file.

        Please try copying the contents to a separate new excel file and use the new file.

        Please let me know if it works.

        (0) 
    2. Venkat Ratnam naidu

      Hi Devesh ,

      Very helpful topic I have tried using the same topic but unable to .facing the error “Invalid document format”

      please help on this .

      Thanks ,

      Venkat Ratnam Naidu

      (0) 
      1. Devesh Singh Post author

        Thanks Venkat.

        Sorry for late reply….I think the problem is the same as faced by Kevin in above comment.

        Please try copying the contents to a separate new excel file and use the new file.

        Please let me know if it works.

        (0) 
  1. Willi Robert

    Hi Devesh,

    I am getting endless loop at

    lref_data = lref_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lv_name).

    can ypu please tell me if any one faced that issue

    (0) 
    1. Devesh Singh Post author

      Hello Willi,

      I faced the same problem with an excel file which had a large amount of data.

      Please let me know if the problem is with small amount of data too.

      (0) 
  2. Tobias KΓΌbler

    Hi, worked fine for me…THX

    but

    this line was wrong

    READ TABLE lt_worksheets INDEX 1 INTO lv_ws_name.

    should be:

    READ TABLE lt_worksheets INDEX 1 INTO lv_name.
    (0) 
        1. Devesh Singh Post author

          Hello Karthikeyan,

          Please help me with your solution for uploading .XLS files.

          Please give me the URL/link to your solution.

          Appreciate your help.

          Thanks and Regards,

          Devesh Singh

          (0) 
  3. Priyaranjan Gupta

    Hi ,

    Is this code specific to certain version of system. I have tried using upload xlsx as per steps given by you but I don’t see certain class refer in this availble in my system. For exp –


    cl_fdt_xl_spreadsheet

    cx_fdt_excel_core

    (0) 

Leave a Reply