Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

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_attributesrefresh 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_catrefresh 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.

1 Comment
Labels in this area