Skip to Content

Upload XLSX file in WebDynpro ABAP and view its content

You can see many posts in WebDynpro ABAP Form about reading an Excel file and showing its content in a WebDynpro ABAP Table, however many of them would be closed unanswered since it was not directly possible.

Now it is possible with this interesting and collaborative project ABAP2XLSX!!! Way to go guys for innovative thinking by starting this project!!!

Prerequisites:

  1. Install SAPLINK 1.4 or higher version http://code.google.com/p/saplink/downloads/list
  2. Import and activate needed plug-ins for DDIC , Function Group, Dev Class, Interfaces , WebDynpro ABAP http://code.google.com/p/saplink-plugins/downloads/list or https://cw.sdn.sap.com/cw/docs/DOC-145448
  3. Import most important Plug-in ABAP2XLSX  https://cw.sdn.sap.com/cw/releases/86?container=1016 Installation guide https://cw.sdn.sap.com/cw/docs/DOC-137513
  4. An XLSX excel file with some data.
  5. Join ABAP2XLSX and SAPLINKGroup for more updates on these awsome discoveries https://cw.sdn.sap.com/cw/groups/saplink and https://cw.sdn.sap.com/cw/groups/abap2xlsx

Create a WD Component with File upload UI Element:

  1. Create a WD Component in SE80 with a window and a view
  2. In View controller, create a node” upload_content” and an attribute “file_content” to store Uploaded Excel file data in XSTRING format.
  3. Create a File Upload UI Element and bind its datasource property to XSTRING attribute. WD View.jpg
  4. Create a Button and assign an action upload to it, when an excel file will be uploaded
  5. And here comes the magical code to read the excel 2007 (.xlsx) file.

Method OnActionUpload.

METHOD onactionupload .
DATA lo_nd_upload_content    TYPE REF TO if_wd_context_node.
DATA lo_el_upload_content    TYPE REF TO if_wd_context_element.
DATA ls_upload_content       TYPE wd_this->element_upload_content.
DATA: lo_componentcontroller  TYPE REF TO ig_componentcontroller,
lo_current_controller   TYPE REF TO if_wd_controller,
lo_view_controller      TYPE REF TO if_wd_view_controller,
lo_message_manager      TYPE REF TO if_wd_message_manager .
DATA: excel                   TYPE REF TO zcl_excel,
lo_excel_writer         TYPE REF TO zif_excel_writer,
  reader                  TYPE REF TO zif_excel_reader.
DATA: worksheet               TYPE REF TO zcl_excel_worksheet,
highest_column          TYPE zexcel_cell_column,
highest_row             TYPE int4,
column                  TYPE zexcel_cell_column VALUE 1,
col_str                 TYPE zexcel_cell_column_alpha,
row                     TYPE int4               VALUE 1,
value                   TYPE zexcel_cell_value.
DATA: ex                      TYPE REF TO zcx_excel,
msg                     TYPE string,
lv_highest_row          TYPE string,
lv_highest_column       TYPE string,
lv_rowdata              TYPE string,
lv_rownumber            TYPE string.

lo_current_controller ?= wd_this->wd_get_api( ).
lo_message_manager = lo_current_controller->get_message_manager( ).
lo_view_controller = wd_this->wd_get_api( ).

*   navigate from <CONTEXT> to <UPLOAD_CONTENT> via lead selection
lo_nd_upload_content = wd_context->get_child_node( name = wd_this->wdctx_upload_content ).

*   get element via lead selection
lo_el_upload_content = lo_nd_upload_content->get_element( ).

*   get all declared attributes
  lo_el_upload_content->get_static_attributes(
IMPORTING
static_attributes = ls_upload_content ).

TRY.
CREATE OBJECT reader TYPE zcl_excel_reader_2007.
excel = reader->load( ls_upload_content-file_content ).

worksheet = excel->get_active_worksheet( ).
highest_column = worksheet->get_highest_column( ).
MOVE highest_column TO lv_highest_column.
highest_row    = worksheet->get_highest_row( ).
MOVE highest_row TO lv_highest_row.
CONCATENATE ‘Highest column: ‘ lv_highest_column  ‘Highest row: ‘  lv_highest_row INTO msg.
CALL METHOD lo_message_manager->report_message
EXPORTING
message_text = msg.

CLEAR lv_rowdata.

WHILE row <= highest_row.
WHILE column <= highest_column.
col_str = zcl_excel_common=>convert_column2alpha( column ).
worksheet->get_cell(
EXPORTING
ip_column = col_str
ip_row    = row
IMPORTING
ep_value = value
).
CONCATENATE lv_rowdata value INTO lv_rowdata SEPARATED BY space.
column       = column + 1.
ENDWHILE.
CLEAR msg.
MOVE row TO lv_rownumber.
CONCATENATE ‘Row# ‘ lv_rownumber ‘ Data = ‘ lv_rowdata INTO msg.
CLEAR lv_rowdata.
CALL METHOD lo_message_manager->report_message
EXPORTING
message_text = msg.
column = 1.
row = row + 1.
ENDWHILE.

CATCHzcx_excel INTO ex.    ” Exceptions for ABAP2XLSX
CLEAR msg.
msg = ex->error.

*  report message
CALL METHOD lo_message_manager->report_message
EXPORTING
message_text = msg.

ENDTRY.
ENDMETHOD.

Sample Excel file Content

Excel File.jpg

Sample WebDynpro ABAP Application accepting xlsx file:

WDA Application.jpg

Output :

WDA Application Output.jpg

Happy programming!!!!

Feel free to share your experience with Excel upload in WDA.

To report this post you need to login first.

19 Comments

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

  1. yamuna pillai

    Thanks a lot for sharing this as we were able to implement them.

    Actually above code always reads the active work sheet 3 and we had to set them to read sheet 1 and read the active work sheet and it worked for us.

    EXCEL->SET_ACTIVE_SHEET_INDEX( ‘1’).

    once again Thank you,

    Yamuna

    (0) 
    1. Ashish Shah Post author

      Glad you were able to use this blog.

      Thanks for sharing information about reading the specific worksheet, I will mention this in my blog as well.

      (0) 
      1. Rashmita Parida

        Dear Ashish

        Thanks a lot for your valueable blots which you have share with us. I have also facing same problem when I am uploading a excel file of format .xlsx.So I have used your blog also. But I am getting some error like “zcl_excel is unknown”.   an u kindly tell me what are these : zcl_excel,zif_excel_writer,zif_excel_reader,zcl_excel_worksheet, zexcel_cell_column,

        zexcel_cell_column_alpha,zexcel_cell_value, zcx_excel_reader_2007

        Regards

        Rashmita Parida

        (0) 
          1. HARISH KUMAR SINGH KSHATRI

            Hi ashish,

            can you please explain in details how to import the  Plug-in ABAP2XLSX .

            because i have successfully uploaded the all the above plugings but when i hit the above

            Plug-in ABAP2XLSX .

            I COULD NOT FIND THE LINK TO DOWNLOAD THE  nugg file.

            can you please guide to import the Plug-in ABAP2XLSX .

            THANKS IN ADVANCE,

            HARISH SINGH

            (0) 
          2. HARISH KUMAR SINGH KSHATRI

            Hi,

            Now i have done successfully with the prerequisites section.

            and place the code but i ma getting erro like

            • Call of a method or kernel Method that has not been implemented “

            for the code excel = reader->load( ls_upload_content-file_content ).

            what code i have to place in that load method .

            thanks ,





            (0) 
            1. Ashish Shah Post author

              Hi Harish,

              It seems you have some object from ABAP2XLSX plugins which are not active.

              can you look at your inactive objects list and activate all the inactive objects.

              If still the problem persists then i suggest you reimport the ABAP2XLSX plugin and reactivate all the objects.

              Regards,

              Ashish

              (0) 
              1. HARISH KUMAR SINGH KSHATRI

                Hi Ashish,

                Thanks for the response.

                In the above discussed Prerequisites: in the second point you said to activate the plugins dev class and interfaces from the provided link i could found DDIC , Function Group,

                webDynpro ABAP links but for INTERFACES AND DEV CLASS CAN YOU PLEASE GUIDE ME WHICH ONE I HAVE TO DOWNLOAD IN THE BELOW LINK because we have 11 items in the below link which one i have to download for the interfaces and dev class plugin i could found ddic,function group,webdynpro abap links please guide me


                http://code.google.com/p/saplink-plugins/downloads/list

                (0) 
      2. yamuna pillai

        Hi Ashish,

        I have a question on the installation of Plug-in ABAP2XLSX, actually we installed it in our development box and it has by default went in to $TMP, how are we suppose to implement them in other box ie., testing and production, is that individually we have to install them else move to a package and move them via TR’s if so is that only moving  zcl_excel class would be enough!!

        Thanks,

        Yamuna

        (0) 
        1. Ashish Shah Post author

          Hi Yamuna,

          As mentioned in installation guide : https://cw.sdn.sap.com/cw/docs/DOC-137513

          you can perform this activity either using  STMS or SAPLink using nugg file.

          Using Change Request

          Queue into your STMS abap2xlsx codata and cofile and import the CR to your system.”

          If you need to send these class files to your QA and production environment, you can use STMS transport requests.

          Regards,

          Ashish Shah

          (0) 
  2. Vijay Ramarao

    Hi Ashish,

    System is able to read only XLSX files, but not XLS files. Our requirement is both XLS and XLSX. Do you have any idea how to make it work for XLS also. Please help. Thanks.

    Regards,

    Vijay.

    (0) 
    1. SRINIVASAN ANUMANTHAN

      Hi asah,

      You done Excellent job. I have small issue in the method load

      excel = reader->load( ls_upload_content-file_content ).

      return parameter is still initial. please suggest as soon as possible.

      (0) 
  3. Devesh Singh

    Hello All,

    For this you need to install additional z pluggins through SAPLINK. Instead you can use cl_fdt_xl_spreadsheet standard class for .xlsx file upload. You can refer to my content for the same through the given link.

    http://scn.sap.com/docs/DOC-45719

    Please award points if useful.

    Thanks and Regards,

    Devesh Singh

    (0) 
  4. Prashant Meher

    Hi Ashish,

    Nice article.

    I used it and was able to upload xlsx file. However, the date field in the excel sheet returned  me “41500”.

    How can I solve this issue?

    Regards,

    Prashant

    (0) 
  5. Rita Grace

    Hi,

       I tried the above method to upload excel file in webdynpro. In the given above code, would like to know what is zcl_excel_worksheet and zcl_excel  defined as?? I am getting as unknown.

    Your response is much appreciated.

    (0) 
    1. Devesh Singh

      Hello Rita,

      ZCL_EXCEL_WORKSHEET and ZCL_EXCEL would get installed/loaded into your SAP after you install the plugins from SAPLINK. As Ashish mentioned, follow below steps before writing the logic for upload.

      1. Install SAPLINK 1.4 or higher version http://code.google.com/p/saplink/downloads/list
      2. Import and activate needed plug-ins for DDIC , Function Group, Dev Class, Interfaces , WebDynpro ABAP http://code.google.com/p/saplink-plugins/downloads/list or https://cw.sdn.sap.com/cw/docs/DOC-145448
      3. Import most important Plug-in ABAP2XLSX  https://cw.sdn.sap.com/cw/releases/86?container=1016 Installation guide https://cw.sdn.sap.com/cw/docs/DOC-137513
      4. An XLSX excel file with some data.
      5. Join ABAP2XLSX and SAPLINKGroup for more updates on these awsome discoveries https://cw.sdn.sap.com/cw/groups/saplink and https://cw.sdn.sap.com/cw/groups/abap2xlsx

      Thanks and Regards,

      Devesh Singh

      (0) 

Leave a Reply