Upload a display excel data in webdynpro ALV using ABAP2XLSX

Author | Last Updated| | 4 Comments | F Share

Upload excel sheet in web dynpro for ABAP and displaying the excel data in ALV table using ABAP2XLSX

+ -

Step1:Create a webdynpro component in SE80 Normal Top^

Create a web dynpro component ZSAPN_EXCEL_UPLOAD in SE80, save it in a local package.

Upload excel in webdynpro

Upload excel in webdynpro for ABAP

Step2:Add ALV component a root level Normal Top^

Double click on component name and add standard ALV component SALV_WD_TABLE at root level of the component as used components.

Add standard ALV component

Step3:Create required nodes and attributes Medium Top^

Go to component controller and add required nodes and attributes, the required nodes and attributes for our component are.

Attribute Under Node Type
FILENAME UPLOAD STRING
DATA UPLOAD XSTRING
NAME ALV_DATA CHAR20
CITY ALV_DATA CHAR20
COUNTRY ALV_DATA CHAR20

Upload excel in webdynpro ABAP

Step4:Design view for file upload and ALV table Medium Top^

Go to main view, Drag and drop the context to main view,

Excel upload in webdynpro

Create UI elements as specified below.

File Upload

Button ( name upload)

View Container UI element

Right click on ROOTUIELEMENTCONTAINER , click Insert Element and create file upload element.

ABAP2EXLS webdynpro

Create data binding as below.

Webdynpro excel upload

Right click on ROOTUIELEMENTCONTAINER , click Insert Element and create button element.

Upload excel in webdynpro for ABAP

Create action method for upload button which will trigger when ever we click on upload .

ABAP2XLS in webdynpro

Right click on ROOTUIELEMENTCONTAINER , click Insert Element and create View Container UI element.

ABAP2xlsx in WD

No view will be like below.

Webdynpro XLSX upload

Step5:Write method to upload excel data Important Top^

Step1: Read the upload node to get uploaded file data and get ALV node to set data.

DATA LO_ND_UPLOAD TYPE REF TO IF_WD_CONTEXT_NODE.
DATA LO_EL_UPLOAD TYPE REF TO IF_WD_CONTEXT_ELEMENT.
DATA LS_UPLOAD TYPE WD_THIS->ELEMENT_UPLOAD.
* navigate from  to  via lead selection
LO_ND_UPLOAD = WD_CONTEXT->GET_CHILD_NODE( NAME = WD_THIS->WDCTX_UPLOAD ).
* get element via lead selection
LO_EL_UPLOAD = LO_ND_UPLOAD->GET_ELEMENT( ).
* get all declared attributes
LO_EL_UPLOAD->GET_STATIC_ATTRIBUTES(
    IMPORTING
      STATIC_ATTRIBUTES = LS_UPLOAD ).
  DATA LO_ND_ALV_DATA TYPE REF TO IF_WD_CONTEXT_NODE.

  DATA LT_ALV_DATA TYPE WD_THIS->ELEMENTS_ALV_DATA.
  DATA LS_ALV_DATA LIKE LINE OF LT_ALV_DATA .
* navigate from  to  via lead selection
  LO_ND_ALV_DATA = WD_CONTEXT->GET_CHILD_NODE( NAME = WD_THIS->WDCTX_ALV_DATA ).

Step2: Basic declerations for the class.

DATA: EXCEL             TYPE REF TO ZCL_EXCEL,
        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,
        LV_HIGHEST_COLUMN TYPE STRING,
        LV_HIGHEST_ROW    TYPE STRING.

Step3: Load data into Excel reader and get active worksheet.

CREATE OBJECT READER TYPE ZCL_EXCEL_READER_2007.
EXCEL = READER->LOAD( ls_upload-data  ). "Load data into reader
EXCEL->SET_ACTIVE_SHEET_INDEX( '1').
WORKSHEET = EXCEL->GET_ACTIVE_WORKSHEET( ).
HIGHEST_COLUMN = WORKSHEET->GET_HIGHEST_COLUMN( ). "get heighest column
MOVE HIGHEST_COLUMN TO LV_HIGHEST_COLUMN.
HIGHEST_ROW    = WORKSHEET->GET_HIGHEST_ROW( ). "get heighest row

Step4: Move the uploaded data into a internal table

Now we have heigest row and heighest column, based on this we can able to get each cell value using method GET_CELL of class ZCL_EXCEL_WORKSHEET.

Get data into a internal table using while loop.

ROW = 1.
    WHILE ROW    <= HIGHEST_ROW.
      IF LV_ERROR_FLAG = 'X'.
        EXIT.
      ENDIF.
      WHILE COLUMN <= HIGHEST_COLUMN.
        COL_STR = ZCL_EXCEL_COMMON=>CONVERT_COLUMN2ALPHA( COLUMN ).
*COL_STR = ZCL_EXCEL_COMMON_PLCM=>CONVERT_COLUMN2ALPHA( COLUMN ).
        WORKSHEET->GET_CELL(
        EXPORTING
        IP_COLUMN = COL_STR
        IP_ROW    = ROW
        IMPORTING
        EP_VALUE  = VALUE
        ).

        CASE COLUMN.
          WHEN '1'.
            LS_ALV_DATA-NAME = VALUE .
          WHEN '2'.
            LS_ALV_DATA-CITY = VALUE .
          WHEN '3'.
            LS_ALV_DATA-COUNTRY = VALUE .
        ENDCASE.
        COLUMN  = COLUMN + 1.
      ENDWHILE.
*          IF NOT wa_table IS INITIAL.
*            APPEND wa_table TO it_node_op.
*          ENDIF.
      IF NOT LS_ALV_DATA IS INITIAL .
        APPEND LS_ALV_DATA TO LT_ALV_DATA .
      ENDIF.
*        CLEAR WA_STATUS_BASED .
      COLUMN = 1.
      ROW    = ROW + 1.
    ENDWHILE.

Step5: Set data to node .


LO_ND_ALV_DATA->BIND_TABLE( NEW_ITEMS = LT_ALV_DATA SET_INITIAL_ELEMENTS = ABAP_TRUE ).

Full logic for method is


  DATA LO_ND_UPLOAD TYPE REF TO IF_WD_CONTEXT_NODE.

  DATA LO_EL_UPLOAD TYPE REF TO IF_WD_CONTEXT_ELEMENT.
  DATA LS_UPLOAD TYPE WD_THIS->ELEMENT_UPLOAD.

* navigate from  to  via lead selection
  LO_ND_UPLOAD = WD_CONTEXT->GET_CHILD_NODE( NAME = WD_THIS->WDCTX_UPLOAD ).

* @TODO handle non existant child
* IF lo_nd_upload IS INITIAL.
* ENDIF.

* get element via lead selection
  LO_EL_UPLOAD = LO_ND_UPLOAD->GET_ELEMENT( ).
* @TODO handle not set lead selection
  IF LO_EL_UPLOAD IS INITIAL.
  ENDIF.

* get all declared attributes
  LO_EL_UPLOAD->GET_STATIC_ATTRIBUTES(
    IMPORTING
      STATIC_ATTRIBUTES = LS_UPLOAD ).
  DATA LO_ND_ALV_DATA TYPE REF TO IF_WD_CONTEXT_NODE.

  DATA LT_ALV_DATA TYPE WD_THIS->ELEMENTS_ALV_DATA.
  DATA LS_ALV_DATA LIKE LINE OF LT_ALV_DATA .
* navigate from  to  via lead selection
  LO_ND_ALV_DATA = WD_CONTEXT->GET_CHILD_NODE( NAME = WD_THIS->WDCTX_ALV_DATA ).



  DATA: EXCEL             TYPE REF TO ZCL_EXCEL,
        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,
        LV_HIGHEST_COLUMN TYPE STRING,
        LV_HIGHEST_ROW    TYPE STRING.
  DATA :MSG TYPE        STRING,
            EX  TYPE REF TO ZCX_EXCEL.

  DATA LV_ERROR_FLAG TYPE C.

  CLEAR LV_ERROR_FLAG.
  TRY.
    CREATE OBJECT READER TYPE ZCL_EXCEL_READER_2007.
    EXCEL = READER->LOAD( LS_UPLOAD-DATA  ).
*R&D
    EXCEL->SET_ACTIVE_SHEET_INDEX( '1').
    WORKSHEET = EXCEL->GET_ACTIVE_WORKSHEET( ).
****
    HIGHEST_COLUMN = WORKSHEET->GET_HIGHEST_COLUMN( ).
    MOVE HIGHEST_COLUMN TO LV_HIGHEST_COLUMN.
    HIGHEST_ROW    = WORKSHEET->GET_HIGHEST_ROW( ).
    ROW = 2.
    WHILE ROW    <= HIGHEST_ROW.
      IF LV_ERROR_FLAG = 'X'.
        EXIT.
      ENDIF.
      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
        ).

        CASE COLUMN.
          WHEN '1'.
            LS_ALV_DATA-NAME = VALUE .
          WHEN '2'.
            LS_ALV_DATA-CITY = VALUE .
          WHEN '3'.
            LS_ALV_DATA-COUNTRY = VALUE .
        ENDCASE.
        COLUMN  = COLUMN + 1.
      ENDWHILE.
      IF NOT LS_ALV_DATA IS INITIAL .
        APPEND LS_ALV_DATA TO LT_ALV_DATA .
      ENDIF.
        CLEAR LS_ALV_DATA .
      COLUMN = 1.
      ROW    = ROW + 1.
    ENDWHILE.
    CATCH ZCX_EXCEL INTO EX.    " Exceptions for ABAP2XLSX
      CLEAR MSG.
      MSG = EX->ERROR.
*      IF NOT MSG IS INITIAL.
**Raise exception message
*      ENDIF.
  ENDTRY.

  LO_ND_ALV_DATA->BIND_TABLE( NEW_ITEMS = LT_ALV_DATA SET_INITIAL_ELEMENTS = ABAP_TRUE ).

Step6:Embedd interface view TABLE into ALV view container Important Top^

Here we have use interface view TABLE of SALV_WD_TABLE to display ALV data, for this one we need to embed interface view into our view container .

Go to Window, expand main view, right click to embed interface view.

Upload excel in webdynpro

A pop up will open press F4 and select TABLE, enter.

Upload excel in SAP

Step7:Create external context mapping for ALV Important Top^

Do external context mapping to display data on ALV table.

Expand component Usages->ALV_TABLE->INTERFACECONTROLLER.

Double click on interface controller, create controller usage.

ALV in webdynpro

A pop up will open select Component controller.

Webdynpro ALV edit excel

Map ALV_DATA to DATA.

Upload excel in webdynpro

Create application and test, right click on component name Create->Web Dynpro Application.

Step8:Upload Excel and test the application Normal Top^

Now test the application, upload an excel with the following format

Press Upload button, the out put will be

Upload excel sheet in webdynpro ABAP

Tutorial Comments

Total Comments: Add your Comment
23 Feb 2014

Hi Ashok, Article is very good, i hope its better to place class Source code to understand the entire logic for methods which you called for File read and extracting the data. Regards, Venkat.

09 May 2014

can you explain me abt these declerations DATA: EXCEL TYPE REF TO ZCL_EXCEL, 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, LV_HIGHEST_COLUMN TYPE STRING, LV_HIGHEST_ROW TYPE STRING.

06 Jul 2014

can you explain me about these declerations class z*: DATA: EXCEL TYPE REF TO ZCL_EXCEL, 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

06 Jul 2014

can you explain me about these declerations class z*: DATA: EXCEL TYPE REF TO ZCL_EXCEL, 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

Leave a Comment

Enter Comment Text