Data Upload to Multiple Z-Tables from EXCEL WorkBook using OLE

[Former Member]

Hi All,

We are going to learn how to do various EXCEL operations using OLE Concept.

Using OLE concept provided by SAP for ABAP, we can do many EXCEL operations in the background(backend) like Selecting rows, cutting cells, pasting data into cells, controlling the font used in individual cells and so on which we normally do in the front end.

I have used this OLE concept to accomplish the following-

Lets say, this is the look and feel of the Selection Screen.

• In the selection screen, there is a select option 'Table Name' where the user can give any number of Z table names.
• The Button Download EXCEL Template downloads the EXCEL template with a list of worksheets, one worksheet for each of the Z tables specified in the Selection Screen.

       

         1.  Note that There are 2 worksheets one for each Z-table specified in the Selection Screen.

          This is achieved using OLE concept of ABAP as below -

CREATE OBJECT lo_application 'Excel.Application'.

  CALL METHOD OF

      lo_application

      'Workbooks'    = lo_workbook.

  DESCRIBE TABLE s_table[] LINES w_tables_cnt.

  CALL METHOD OF

      lo_workbook

      'Add'.

  SET PROPERTY OF lo_application 'Visible' = 0.

  CALL METHOD OF

      lo_application

      'Worksheets'   = lo_worksheet.

  LOOP AT s_table.

    w_sheet_no = sy-tabix.

    CREATE DATA wa_dref TYPE TABLE OF (s_table-low).

    ASSIGN wa_dref->* TO <t_itab>.

    desc_table ?= cl_abap_tabledescr=>describe_by_data( <t_itab> ).

    desc_struc ?= desc_table->get_table_line_type( ).

    IF w_sheet_no = 1.

      CALL METHOD OF

          lo_worksheet

          'Add'.

      CALL METHOD OF

          lo_application

          'Worksheets'   = lo_worksheet

        EXPORTING

          #1             = w_sheet_no.

      CALL METHOD OF

          lo_worksheet

          'Activate'.

      SET PROPERTY OF lo_worksheet 'Name' = s_table-low.

    ELSE.

      GET PROPERTY OF lo_application 'Sheets' = lo_worksheet .

      CALL METHOD OF

          lo_worksheet

          'Add'        = lo_workbook.

      SET PROPERTY OF lo_workbook 'Name' = s_table-low .

      GET PROPERTY OF lo_application 'ACTIVESHEET' = lo_worksheet.

    ENDIF.

    desc_struc1 ?= cl_abap_structdescr=>describe_by_name( s_table-low ).

    t_ddic_info = desc_struc1->get_ddic_field_list( ).

    CLEAR w_column.

    REFRESH lt_data[].

    LOOP AT desc_struc->components ASSIGNING <fs_component>.

      w_column = w_column + 1.

      CLEAR wa_ddic_info.

      READ TABLE t_ddic_info INTO wa_ddic_info WITH KEY fieldname = <fs_component>-name.

      IF wa_ddic_info-keyflag = 'X'.

        PERFORM f300_fill_cell USING 1 w_column 1 <fs_component>-name.

      ELSE.

        PERFORM f300_fill_cell USING 1 w_column 0 <fs_component>-name.

      ENDIF.

      ASSIGN <fs_component>-name TO <field>.

      CONCATENATE lt_data <field> INTO lt_data SEPARATED BY cl_abap_char_utilities=>horizontal_tab.

      SHIFT lt_data BY 1 PLACES LEFT.

      APPEND lt_data.

      CLEAR lt_data.

    ENDLOOP.

    CALL FUNCTION 'CONTROL_FLUSH'

      EXCEPTIONS

        OTHERS = 3.

    CALL FUNCTION 'CLPB_EXPORT'

      TABLES

        data_tab   = lt_data

      EXCEPTIONS

        clpb_error = 1

        OTHERS     = 2.

  ENDLOOP.

  IF w_sheet_no > 1.

    CALL METHOD OF

        lo_workbook

        'SaveAs'

      EXPORTING

        #1          = l_fullpath.

    IF sy-subrc EQ 0.

      MESSAGE 'File downloaded successfully' TYPE 'S'.

    ELSE.

      MESSAGE 'Error downloading the file' TYPE 'E'.

    ENDIF.

  ELSE.

    CALL METHOD OF

        lo_worksheet

        'SaveAs'

      EXPORTING

        #1           = l_fullpath.

    IF sy-subrc EQ 0.

      MESSAGE 'File downloaded successfully' TYPE 'S'.

    ELSE.

      MESSAGE 'Error downloading the file' TYPE 'E'.

    ENDIF.

  ENDIF.

  CALL METHOD OF

      lo_application

      'QUIT'.

   2. Note that the Key fields in the EXCEL are made bold.

        This is accomplished by using OLE concept as below -

CALL METHOD OF

  lo_worksheet

  'Cells' = lo_cell

  NO

  FLUSH

  EXPORTING

  #1 = i

  #2 = j.

SET PROPERTY OF lo_cell 'VALUE' = val no flush.

GET PROPERTY OF lo_cell 'FONT' = lo_font no flush.

SET PROPERTY OF lo_font 'BOLD' =  bold no flush.

•   The Button 'Upload EXCEL Data' reads data from all the WorkSheets and uploads data into the corresponding tables.

This concept of reading data from multiple sheets from the EXCEL is accomplished using OLE concept as below -

CREATE OBJECT lo_application 'Excel.Application'.

  CALL METHOD OF

      lo_application

      'Workbooks'    = lo_workbook.

  READ TABLE t_filetable INTO wa_filetable INDEX 1.

  w_filename = wa_filetable-filename.

  CALL METHOD OF

      lo_workbook

      'Open'

    EXPORTING

      #1          = w_filename.

  DESCRIBE TABLE s_table LINES w_tabcnt_sel.

  DO w_tabcnt_sel TIMES.

    w_sheet_no = w_sheet_no + 1.

    GET PROPERTY OF  lo_application 'ACTIVESHEET' = lo_worksheet.

    CALL METHOD OF

        lo_application

        'Worksheets'   = lo_worksheet

      EXPORTING

        #1             = w_sheet_no.

    CALL METHOD OF

        lo_worksheet

        'Activate'.

    GET PROPERTY OF lo_application 'ACTIVESHEET' = lo_worksheet.

    GET PROPERTY OF lo_worksheet 'Name' = w_sheet_name.

    IF w_sheet_name IS NOT INITIAL AND w_sheet_name IN s_table[].

    ELSE.

      w_subrc = 4.

      CONTINUE.

    ENDIF.

    CREATE DATA wa_dref TYPE TABLE OF (w_sheet_name).

    ASSIGN wa_dref->* TO <t_itab>.

    CREATE DATA wa_dref LIKE LINE OF <t_itab>.

    ASSIGN wa_dref->* TO <wa_itab>.

    desc_table ?= cl_abap_tabledescr=>describe_by_data( <t_itab> ).

    desc_struc ?= desc_table->get_table_line_type( ).

    DESCRIBE TABLE desc_struc->components LINES w_column_cnt.

    w_row_cnt = p_record.

* mark whole spread sheet

    CALL METHOD OF

        lo_worksheet

        'Cells'      = h_cell

      EXPORTING

        #1           = 2

        #2           = 1.

    CALL METHOD OF

        lo_worksheet

        'Cells'      = h_cell1

      EXPORTING

        #1           = w_row_cnt

        #2           = w_column_cnt.

    CALL METHOD OF

        lo_worksheet

        'RANGE'      = range

      EXPORTING

        #1           = h_cell

        #2           = h_cell1.

    CALL METHOD OF

        range

        'SELECT'.

    CALL METHOD OF

        range

        'COPY'.

    CALL METHOD cl_gui_frontend_services=>clipboard_import

      IMPORTING

        data       = t_excel_tab

      EXCEPTIONS

        cntl_error = 1

        OTHERS     = 4.

    LOOP AT t_excel_tab INTO wa_excel_tab.

      SPLIT wa_excel_tab-line AT w_tab INTO TABLE t_split_data.

      CLEAR w_count.

      LOOP AT t_split_data INTO wa_split_data.

        ADD 1 TO w_count.

        ASSIGN COMPONENT w_count OF STRUCTURE <wa_itab> TO <field>.

        <field> = wa_split_data-line.

      ENDLOOP.

      APPEND <wa_itab> TO <t_itab>.

    ENDLOOP.

    IF <t_itab> IS NOT INITIAL.

      MODIFY (w_sheet_name) FROM TABLE <t_itab>.

      IF sy-subrc = 0.

        wa_tabsuccess-tabname = w_sheet_name.

        APPEND wa_tabsuccess TO t_tabsuccess.

      ELSE.

        wa_tabfailure-tabname = w_sheet_name.

        APPEND wa_tabfailure TO t_tabfailure.

      ENDIF.

    ELSE.

      wa_tabfailure-tabname = w_sheet_name.

      APPEND wa_tabfailure TO t_tabfailure.

    ENDIF.

  ENDDO.

SUMMARY: OLE Concept provided by SAP is a good approach to perform various EXCEL operations in background.