ABAP WebDynpro – Upload Excel File using Class CL_FDT_XL_SPREADSHEET

By | November 25, 2013 | WebDynpro ABAP | 8,699 | 4

Lets see how we can upload the excel file using the class CL_FDT_XL_SPREADSHEET in ABAP WebDynpro application.

Preface:
Data can be copied into the Excel from various sources. These sources can copy the data using Unicode vs non-Unicode. Sometimes, all you get is the data in different format. Like:

  • "Change". – Copied from PDF
  • “Change”. – Copied from Text file or Entering it directly

As clearly visible from two pointers difference in characters, this creates problem while upload, so a manual cleanse was required, cumbersome!!. What is the big fuss about loading data? It’s been done many time using legendary FMs. No, ABAP WebDynpro application doesn’t take this way of first taking content into ‘XSTRING’ and read the contents.

Initially I tried this solution:

”OnActionUpload”

 
* Data Declaration part,
   DATA:  LV_FILECONTENT TYPE XSTRING,
                        CONVT TYPE REF TO CL_ABAP_CONV_IN_CE,
                         WA_STRING TYPE STRING,
                       S_TABLE TYPE STRING_TABLE.
 
  CONVT = CL_ABAP_CONV_IN_CE=>CREATE (   INPUT = LV_FILECONTENT
                                         ENCODING = '6102').
  CONVT->READ( IMPORTING DATA = S_CONTENT ).
  SPLIT S_CONTENT AT CL_ABAP_CHAR_UTILITIES=>CR_LF INTO TABLE S_TABLE.
.... 
  LOOP AT S_TABLE INTO WA_STRING. 
    IF WA_STRING CO CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB OR SY-TABIX EQ 1.
      CONTINUE.
    ENDIF. "Skip the heading line
    SPLIT WA_STRING AT CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB 
        INTO L_Variabl-field1 L_variable-fiedld2.
...
  ENDLOOP.
 

However, aforementioned logic may end in the short dumps when there is a different encoding.

Alternative Solutions

So, I looked for more answers. Like these:

Class CL_FDT_XL_SPREADSHEET

Above three references helped me to build solution, last one fits in perfectly. But there is always a catch – this class CL_FDT_XL_SPREADSHEET doesn’t exist in ECC6.0 EHP4 and but it is available in ECC6.0 EHP6.

Quick look at TCode SPAM, ECC6.0 EHP4

and Quick Look T-Code SPAM, ECC6.0 EHP6.0

Package SFDT_EXCEL_CORE in ECC6.0 EHP4

Package SFDT_EXCEL_CORE in ECC6.0 EHP6 has the class CL_FDT_XL_SPREADSHEET

Demo Application

Step-by-Step Demonstration of the ABAP WebDynpro application

Create a Webdynpro Application
Use Tcode SE80, Webdynpro Comp./Intf->Create. Create a node (File_Upload) with three attributes File_name,filetype and filecontents.

  • File_name type string
  • Filetype type string
  • FileContents type Xtring

View
Create a View. Also add the field to select the file location an a button to trigger the upload

Assign the View fields to the Context. The Assigned Fields would look like this:

Assign the event to the Button

Write the code in the Event handler method ONACTIONUPLOAD

”OnActionUpload”

 
Method ONACTIONUPLOAD.
* * Internal tables declaration
  DATA:
        lt_worksheets                             TYPE STANDARD TABLE OF string,
        lt_contents                                 TYPE string_table,
        lt_final_contents                          TYPE TABLE OF ZTEST_JUNKCHAR."Ztable to be appended
* Structures declarations
  DATA:
        ls_return                                      TYPE bapiret1,
        ls_contents                                  TYPE ZTEST_JUNKCHAR,"Ztable to be appended
        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-file_name
          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-TEXT
         LS_CONTENTS-SPECIAL_CHARS.
*     Append split records in internal table
  APPEND LS_CONTENTS TO LT_FINAL_CONTENTS.
  ENDLOOP.
  IF lt_final_contents IS NOT INITIAL.
    MODIFY ZTEST_JUNKCHAR FROM TABLE lt_final_contents.
  ENDIF.
endmethod.
 

Excel File
Sample excel file. Make sure it has the same format as the Table.

Output

Execute application and upload to table

Data was saved in the table as it can be seen here.

Like It? Share!!

Don't miss an Update

Get notified of the new post, right into your inbox

Mohinder Chouhan{2 articles}

I have been working in SAP-ABAP for last 7 years, Infosys LTd. My main interest areas are Forms(SmartForm, Adobe), ALVs( Container based, SALV). Many times, I Debug Standard transactions to understand their framework and to learn from them. Recently, I have started exploring to Webdynpro ABAP.
Follow :

Explore all of his 2 articles.

Load comments

4 Comments

  • steve oldner

    Excellent article! Nice graphics, I like the angles. Looking forward to the next one!

  • vishnu

    Nice article. Comparison to ECC 04 vs 06 was good,well explained.

  • sriram

    Wonderful article and good explaination

  • Titus Thomas

    Excellent article…
    After a lot of head wrecking, this is a clean… crisp.. sure-shot way to read XLSX file from within the web dynpro environment and convert it into a readable internal table format..
    Pure joy :)

    Thanks much for this great help…. :)

Comments on this Post are now closed. If you have something important to share, you can always contact me.

You seem to be new here. Subscribe to stay connected.