WD ABAP Load Excel Document To Dynamic Context and Dynamic Table

I had the need to give a user the ability to load an excel document into SAP.  I wanted to build a generic load option to allow importing any excel document and building a corresponding dynamic context node and dynamic table.  I created a generic method so this can be used from any ABAP WD application.

Create a reference attribute on the view as follows:

Add a transparent container “TBL_CONTAINER” on your view layout where you wish the table to be displayed.  Then add the following code:

Prepare a test Excel Doc:

Create a File Load:

Test Output:

In the web dynpro onactionupload event, I use this method call:

  data: lv_ref_data type ref to data.

  call method z_cl_any_table=>wd_upload_excel_table
    exporting
      uielement_container              = wd_this->uielement_container    “reference to ui container where table will be created
      wd_context                           = wd_context                              “reference to wd context
      wd_context_fu_node_nm        = wd_this->wdctx_file_upload       “the file upload node name
      worksheet_index                   = 1                                             “which worksheet in the document to read
      first_row_contains_col_txt      = ‘X’                                            “does the excel document include column header texts in first row?
      wd_create_wd_context           = ‘X’                                            “create a dynamic context?
      wd_create_wd_context_nm     = ‘CONTEXT_EXCEL’
      wd_create_wd_table               = ‘X’                                            “create a dynamic table?
      wd_create_wd_table_nm         = ‘TBL_EXCEL’
    importing
      o_tbl_excel_contents_as_data = lv_ref_data.

Method Code:

  lo_nd_file_upload = wd_context->get_child_node( name = wd_context_fu_node_nm ).
  lo_el_file_upload = lo_nd_file_upload->get_element( ).
  lo_el_file_upload->get_static_attributes( importing static_attributes = lv_file_upload ).

  try.
      create object lv_ref_excel
        exporting
          document_name = lv_file_upload–filename
          xdocument     = lv_file_upload–filecontents.

      lv_ref_excel->if_fdt_doc_spreadsheet~get_worksheet_names( importing worksheet_names = lt_worksheets ).
      read table lt_worksheets index worksheet_index into lv_worksheet_name.
      check not ( lv_worksheet_name is initial ).
      lv_ref_data = lv_ref_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lv_worksheet_name ).
      assign lv_ref_data->* to <fs_excel_ws_table>.
      check ( <fs_excel_ws_table> is assigned ).
      o_tbl_excel_contents_as_data = lv_ref_data.

      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      “BEGIN – DETERMINE ROW/COLUMN COUNT AND BUILD COLUMN HEADER TEXTS
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      lv_row_cnt = 0.
      loop at <fs_excel_ws_table> assigning <fs_excel_ws_table_row>.
        lv_row_cnt = lv_row_cnt + 1.
        lv_column_cnt = 0.
        lv_continue = abap_true.
        while lv_continue = abap_true.
          assign component sy–index of structure <fs_excel_ws_table_row> to <fs_column_component>.

          if <fs_column_component> is not assigned.
            lv_continue = abap_false.
            exit.
          else.
            lv_column_cnt = lv_column_cnt + 1.
            add_column_txt.
          endif.

          unassign <fs_column_component>.
        endwhile.
      endloop.
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      “END – DETERMINE ROW/COLUMN COUNT AND BUILD COLUMN HEADER TEXTS
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””

      root_node_info = wd_context->get_node_info( ).

      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      “BEGIN – CREATE A ‘CONTEXT NODE’ TO HOLD THE EXCEL DATA
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      check ( wd_create_wd_context = abap_true ).
      clear lt_col_attributes.  refresh lt_col_attributes.
      lv_i = 1.
      while lv_i <= lv_column_cnt.
        clear lv_column_nm.
        get_column_txt lv_i lv_column_nm.
        lv_col_attribute–name = lv_column_nm.
        lv_col_attribute–type_name = ‘STRING’.
        insert lv_col_attribute into table lt_col_attributes.
        lv_i = lv_i + 1.
      endwhile.

      root_node_info->add_new_child_node(
        name                         = wd_create_wd_context_nm
        is_mandatory                 = abap_false
        is_mandatory_selection       = abap_false
        is_multiple                  = abap_true
        is_multiple_selection        = abap_true
        is_singleton                 = abap_false
        is_initialize_lead_selection = abap_false
        is_static                    = abap_false
        attributes                   = lt_col_attributes ).
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      “END – CREATE A ‘CONTEXT NODE’ TO HOLD THE EXCEL DATA
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””

      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      “BEGIN – CREATE A DYNAMIC TABLE FOR BINDING TO ‘CONTEXT NODE’
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      clear lt_alv_cat.  refresh lt_alv_cat.
      lv_i = 1.
      while lv_i <= lv_column_cnt.
        clear lv_column_nm.
        get_column_txt lv_i lv_column_nm.
        lv_alv_cat–fieldname = lv_column_nm.
        lv_alv_cat–datatype = ‘STRG’.
        append lv_alv_cat to lt_alv_cat.
        lv_i = lv_i + 1.
      endwhile.

      call method cl_alv_table_create=>create_dynamic_table
        exporting
          it_fieldcatalog = lt_alv_cat
        importing
          ep_table        = lv_ref_dyn_tbl.

      assign lv_ref_dyn_tbl->* to <fs_dyn_tbl>.
      check <fs_dyn_tbl> is assigned.
      create data lv_dyn_tbl_line like line of <fs_dyn_tbl>.
      assign lv_dyn_tbl_line->* to <fs_dyn_tbl_wa>.
      check <fs_dyn_tbl_wa> is assigned.

      lv_row_cnt = 0.
      loop at <fs_excel_ws_table> assigning <fs_excel_ws_table_row>.
        lv_row_cnt = lv_row_cnt + 1.
        if lv_row_cnt = 1.
          if first_row_contains_col_txt = abap_true.
            continue.
          endif.
        endif.
        lv_i = 1.
        lv_continue = abap_true.
        while lv_continue = abap_true.
          assign component sy–index of structure <fs_excel_ws_table_row> to <fs_column_component>.

          if <fs_column_component> is not assigned.
            lv_continue = abap_false.
            exit.
          else.
            get_column_txt lv_i lv_column_nm.

            assign component lv_column_nm of structure <fs_dyn_tbl_wa> to <fs_cell_value> .
            <fs_cell_value> = <fs_column_component>.

            lv_i = lv_i + 1.
          endif.

          unassign <fs_column_component>.
        endwhile.

        append <fs_dyn_tbl_wa> to <fs_dyn_tbl>.
        unassign <fs_cell_value>.
      endloop.

      dyn_node = wd_context->get_child_node( name = wd_create_wd_context_nm ).
      dyn_node->bind_table( <fs_dyn_tbl> ).
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      “END – CREATE A DYNAMIC TABLE FOR BINDING TO ‘CONTEXT NODE’
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””

      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      “BEGIN – CREATE GUI ELEMENT TABLE AND BIND TO ‘CONTEXT NODE’
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      check wd_create_wd_table = abap_true.
      call method cl_wd_table=>new_table
        exporting
          bind_data_source = wd_create_wd_context_nm
          id               = wd_create_wd_table_nm
        receiving
          control          = lv_ui_table.

      lv_column_group = cl_wd_table_column_group=>new_table_column_group( ).

      lv_i = 1.
      while lv_i <= lv_column_cnt.
        clear lv_column_nm.
        get_column_txt lv_i lv_column_nm.
        concatenate wd_create_wd_context_nm ‘.’ lv_column_nm into lv_column_bound_nm.
        condense lv_column_bound_nm no–gaps.

        clear lv_ui_col_input_field.
        clear lv_table_column.
        clear lv_caption.

        call method cl_wd_input_field=>new_input_field
          exporting
            bind_value = lv_column_bound_nm
            id         = lv_column_nm
            read_only  = ‘X’
          receiving
            control    = lv_ui_col_input_field.

        lv_table_column = cl_wd_table_column=>new_table_column( ).
        lv_table_column->set_table_cell_editor( lv_ui_col_input_field ).
        lv_caption = cl_wd_caption=>new_caption( ).
        lv_caption->set_text( lv_column_nm ).
        lv_table_column->set_header( lv_caption ).
        lv_column_group->add_column( lv_table_column ).

        lv_i = lv_i + 1.
      endwhile.

      lv_ui_table->add_grouped_column( lv_column_group ).
      lv_ui_matrix_head_data = cl_wd_matrix_head_data=>new_matrix_head_data( lv_ui_table ).
      lv_ui_table->set_layout_data( lv_ui_matrix_head_data ).
      if not ( uielement_container is initial ).
        call method uielement_container->add_child( exporting the_child = lv_ui_table ).
      endif.
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
      “END – CREATE GUI ELEMENT TABLE AND BIND TO ‘CONTEXT NODE’
      “”””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
    catch cx_fdt_excel_core into ex_excel_core.
      call method ex_excel_core->if_message~get_text( receiving result = lv_errtext ).
    catch cx_root into ex.
      message id sy–msgid type sy–msgty number sy–msgno into lv_errtext with sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4.
  endtry.