Upload a display excel data in webdynpro ALV using ABAP2XLSX

Step1:Create a webdynpro component in SE80 Normal 

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

Step2:Add ALV component a root level Normal 

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

Step3:Create required nodes and attributes Medium 

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

Step4:Design view for file upload and ALV table Medium 

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

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.

Create data binding as below.

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

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

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

No view will be like below.

Step5:Write method to upload excel data Important 

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 

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.

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

Step7:Create external context mapping for ALV Important 

Do external context mapping to display data on ALV table.

Expand component Usages->ALV_TABLE->INTERFACECONTROLLER.

Double click on interface controller, create controller usage.

A pop up will open select Component controller.

Map ALV_DATA to DATA.

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

Step8:Upload Excel and test the application Normal 

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

Press Upload button, the out put will be