[{ALLOW view All}]
[{ALLOW edit Authenticated}]
{{{
*..............................................................
* Insert Excel data to a table
* First Excel data row is skipped (heading)
*:............................................................:

REPORT  zp_upload_excel_to_table                                    .

TYPE-POOLS: truxs.


SELECTION-SCREEN BEGIN OF BLOCK tabs WITH FRAME.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(25) text-t04.
PARAMETERS: p_file TYPE  rlgrap-filename.
SELECTION-SCREEN END   OF LINE.

SELECTION-SCREEN END OF BLOCK tabs.


SELECTION-SCREEN BEGIN OF BLOCK params WITH FRAME.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(25) text-t03.
PARAMETERS: tabparam(10) TYPE c.
SELECTION-SCREEN END   OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(25) text-t02.
PARAMETERS: simu AS CHECKBOX DEFAULT 'X'.
SELECTION-SCREEN END   OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(25) text-t01.
PARAMETERS: del AS CHECKBOX DEFAULT ' '.
SELECTION-SCREEN END   OF LINE.

SELECTION-SCREEN END OF BLOCK params.


DATA: text1 TYPE string,
      text2 TYPE string,
      text3 TYPE string,
      rows TYPE i,
      cc TYPE i,
      perc TYPE i.

*----- Referencer for Itab and Itab Workarea
DATA ref_itab TYPE REF TO data.
DATA ref_itwa TYPE REF TO data.
*----- Dereferencer for Itab Body
FIELD-SYMBOLS: <fs_itab> TYPE STANDARD TABLE.
*----- Dereferencer for Itab Workarea
FIELD-SYMBOLS: <fs_itwa> TYPE ANY.
*----- Dereferencer for Itab Workarea Components
FIELD-SYMBOLS: <fs_comp> TYPE ANY.

DATA: it_raw TYPE truxs_t_text_data.

* At selection screen
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      field_name = 'P_FILE'
    IMPORTING
      file_name  = p_file.

INITIALIZATION.

  p_file = 'D:\Temp\zums.xls'.
  tabparam = 'YYZUMS'.

***********************************************************************
*START-OF-SELECTION.
START-OF-SELECTION.

  IF sy-uname NE 'EBELM'.
    MESSAGE 'You are not authorized.' TYPE 'I'.
    RETURN.
  ENDIF.


  TRY.

*----- Define Itab for all DB-Records
      CREATE DATA ref_itab TYPE STANDARD TABLE OF (tabparam).

*----- Make Content of Itab accessible
*      Dereference (->*) Pointer ref_itab into a <fs>
      ASSIGN ref_itab->* TO <fs_itab>.

    CATCH cx_root. "DB-Table does not exist
      MESSAGE 'Error' TYPE 'I'.
  ENDTRY.


  " Alternatives: 'KCD_EXCEL_OLE_TO_INT_CONVERT' and 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR        =
      i_line_header            =  'X'
      i_tab_raw_data           =  it_raw       " WORK TABLE
      i_filename               =  p_file
    TABLES
      i_tab_converted_data     = <fs_itab>
   EXCEPTIONS
      conversion_failed        = 1
      OTHERS                   = 2.

  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  DESCRIBE TABLE <fs_itab> LINES rows.


*----- Define Workarea for Itab
  CREATE DATA ref_itwa TYPE (tabparam).
*----- Make content of Itab Workarea accessible
  ASSIGN ref_itwa->* TO <fs_itwa>.

  " delete destination table before
  IF del IS NOT INITIAL.
    DELETE FROM (tabparam).
  ENDIF.

  cc = 1.
  LOOP AT <fs_itab> INTO <fs_itwa>.

    CLEAR text1.
    DO.
*----- Assign via do-loop every component of itab workarea
*      to a component fieldsymbol and access content for write
      ASSIGN COMPONENT sy-index OF STRUCTURE
                       <fs_itwa> TO <fs_comp>.
*----- Abort endless loop after assign of last component
      IF sy-subrc NE 0. EXIT. ENDIF.
      text2 = <fs_comp>.
      CONCATENATE text1 text2 ';' INTO text1.

    ENDDO.
    WRITE: / text1.

    " insert, if simulation is clear
    IF simu IS INITIAL.
      INSERT INTO (tabparam) VALUES <fs_itwa>.
    ENDIF.

    perc = cc MOD 10.
    IF perc EQ 0.
      text1 = cc.
      text2 = rows.
      CONCATENATE 'Reading row' text1 '/' text2 INTO text3 SEPARATED BY ' '.
      CALL FUNCTION 'PROGRESS_INDICATOR'
        EXPORTING
          i_text               = text1
          i_processed          = cc
          i_total              = rows
          i_output_immediately = 'X'.
      COMMIT WORK.
    ENDIF.

  ENDLOOP.
}}}