Excel Dynamic Upload ver. 2.0

*---- 엑셀 Structure

______________________________

ZEXCEL_T_HEADER

______________________________

ZEXCEL_S_HEADER

______________________________

ROW Types ZESEQ3     NUMC 3

TEXT Types CHAR255    CHAR 255

______________________________

*---- 엑셀 헤더

FUNCTION zcp_excel_header.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     VALUE(IT_EXCEL) TYPE  STANDARD TABLE
*"     VALUE(IT_FCAT) TYPE  LVC_T_FCAT
*"  CHANGING
*"     REFERENCE(CT_HEADER) TYPE  ZEXCEL_T_HEADER
*"----------------------------------------------------------------------

  DATA: lo_strtdescr   TYPE REF TO cl_abap_structdescr,
        lo_tabldescr   TYPE REF TO cl_abap_tabledescr.
  DATA: lt_comptab     TYPE abap_component_tab WITH HEADER LINE,
        lt_comps       TYPE abap_component_tab WITH HEADER LINE,
        lt_temp        TYPE abap_component_tab WITH HEADER LINE.

  DATA: ls_header      LIKE LINE OF ct_header.

  FIELD-SYMBOLS : <ls_fcat> LIKE LINE OF it_fcat.

  SORT it_fcat BY fieldname.

  lo_tabldescr ?= cl_abap_structdescr=>describe_by_data( it_excel[] ).
  lo_strtdescr ?= lo_tabldescr->get_table_line_type( ).

  lt_comps[] = lo_strtdescr->get_components(  ).

  LOOP AT lt_comps.
    IF lt_comps-as_include IS NOT INITIAL.
      lo_strtdescr ?= lt_comps-type.
      lt_temp[] = lo_strtdescr->get_components( ).
      APPEND LINES OF lt_temp TO lt_comptab.
      CLEAR lt_temp[].
    ELSE.
      APPEND lt_comps TO lt_comptab.
    ENDIF.
  ENDLOOP.

  LOOP AT lt_comptab.
    READ TABLE it_fcat ASSIGNING <ls_fcat>
                       WITH KEY fieldname = lt_comptab-name
                       BINARY SEARCH.

    CHECK sy-subrc IS INITIAL
      AND <ls_fcat> IS ASSIGNED.
    IF <ls_fcat>-coltext IS NOT INITIAL.
      ls_header-text = <ls_fcat>-coltext.
    ELSE.
      ls_header-text = <ls_fcat>-scrtext_l.
    ENDIF.
    ls_header-row = 1.
    APPEND ls_header TO ct_header.
    CLEAR ls_header.
  ENDLOOP.

ENDFUNCTION.

*---- Function Group Global

TYPE-POOLS : abap, kcde.
DATA : gt_xls TYPE kcde_intern WITH HEADER LINE.

*---- 엑셀 업로드

FUNCTION zca_read_excel.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(IV_PATH) TYPE  STRING OPTIONAL
*"     VALUE(IV_BEGIN_COL) TYPE  I DEFAULT 1
*"     VALUE(IV_BEGIN_ROW) TYPE  I DEFAULT 2
*"     VALUE(IT_EXCEL) TYPE  ANY
*"  CHANGING
*"     VALUE(CT_DATA) TYPE  STANDARD TABLE
*"     VALUE(CV_SUBRC) TYPE  SYSUBRC
*"----------------------------------------------------------------------

  DATA : lv_path       TYPE string,
         lv_action     TYPE i,
         lv_return     TYPE i,
         lv_length     TYPE i.

  DATA : lt_file_table TYPE TABLE OF file_table WITH HEADER LINE.

  IF iv_path IS INITIAL.
*--- Get Desktop Path
    CALL METHOD cl_gui_frontend_services=>get_desktop_directory
      CHANGING
        desktop_directory = lv_path.

    CALL METHOD cl_gui_cfw=>flush.

*--- get file path.
    CALL METHOD cl_gui_frontend_services=>file_open_dialog
      EXPORTING
        window_title      = 'Download Path'
        file_filter       = 'Excel files|*.XLS;*.xlsx'
        initial_directory = lv_path
      CHANGING
        file_table        = lt_file_table[]
        rc                = lv_return
        user_action       = lv_action.

    IF lv_action EQ cl_gui_frontend_services=>action_cancel.
      cv_subrc = 9.
      MESSAGE s050.
      EXIT.
    ENDIF.

    IF lt_file_table[] IS INITIAL.
      MESSAGE s051.
      EXIT.
    ENDIF.

    READ TABLE lt_file_table INDEX 1.
  ELSE.
    lt_file_table-filename = iv_path.
  ENDIF.

  PERFORM set_progess USING ' ' ' ' '엑셀파일을 로드하는 중입니다.'.
  PERFORM read_excel           USING    lt_file_table-filename
                                        iv_begin_col
                                        iv_begin_row
                                        it_excel
                               CHANGING cv_subrc.
  CHECK cv_subrc IS INITIAL.
  PERFORM make_itab_processing USING    it_excel
                               CHANGING ct_data[]
                                        cv_subrc.
ENDFUNCTION.
*&---------------------------------------------------------------------*
*&      Form  read_excel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->PV_PATH       text
*      -->PV_BEGIN_COL  text
*      -->PV_BEGIN_ROW  text
*      -->PV_SUBRC      text
*----------------------------------------------------------------------*
FORM read_excel USING    pv_path
                         pv_begin_col
                         pv_begin_row
                         pt_excel
                CHANGING pv_subrc.

  DATA : lv_type,
         lv_end_row TYPE i,
         lv_file    TYPE rlgrap-filename.

  DATA : lo_table_desc TYPE REF TO cl_abap_tabledescr,
         lo_line_desc  TYPE REF TO cl_abap_structdescr,
         lo_col_desc   TYPE abap_compdescr.

  lv_file = pv_path.

*--- get fields info
  DESCRIBE field pt_excel TYPE lv_type COMPONENTS lv_end_row.

  CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
    EXPORTING
      filename                = lv_file
      i_begin_col             = pv_begin_col  "시작 열
      i_begin_row             = pv_begin_row  "시작 ROW
      i_end_col               = lv_end_row    "종료 열
      i_end_row               = 60000         "종료 ROW
    TABLES
      intern                  = gt_xls
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  pv_subrc = sy-subrc.

  CASE pv_subrc.
    WHEN 0.
    WHEN 1.
      MESSAGE s000 WITH '입력 파라메터를 확인해 주세요.'
              DISPLAY LIKE 'E'.
    WHEN OTHERS.
      MESSAGE s000 WITH 'Excel 파일을 닫은 후 실행해 주세요.'
              DISPLAY LIKE 'E'.
  ENDCASE.

ENDFORM.                    " READ_EXCEL
*&---------------------------------------------------------------------*
*&      Form  MAKE_ITAB_PROCESSING
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->PT_DATA    text
*      -->PT_ALV     text
*----------------------------------------------------------------------*
FORM make_itab_processing USING    pt_data
                          CHANGING pt_alv  TYPE STANDARD TABLE
                                   pv_subrc.

  DATA : lv_tabix    TYPE sy-tabix.
  DATA : lv_type,
         lt_ref      TYPE REF TO data,
         lo_error    TYPE REF TO cx_root.

  DATA : lv_total    TYPE string,
         lv_current  TYPE string,
         lv_msg      TYPE string,
         lv_mask     TYPE char20,
         lv_func     TYPE char100.

  DATA : lv_end_cols TYPE i.

  DATA : lv_desc     TYPE REF TO cl_abap_structdescr,
         lt_comps    TYPE abap_component_tab WITH HEADER LINE.

  FIELD-SYMBOLS : <ls_data>  TYPE ANY,
                  <lv_value> TYPE ANY.
  FIELD-SYMBOLS : <ls_alv>   TYPE ANY,
                  <lv_field> TYPE ANY.

  CREATE DATA lt_ref LIKE pt_data.
  ASSIGN lt_ref->* TO <ls_data>.

  CREATE DATA lt_ref LIKE LINE OF pt_alv.
  ASSIGN lt_ref->* TO <ls_alv>.

  lv_desc ?= cl_abap_typedescr=>describe_by_data( <ls_data> ).
  CALL FUNCTION 'ZGET_COMPONENTS'
    EXPORTING
      it_data         = pt_data
      iv_include_only = 'X'
    IMPORTING
      et_comps        = lt_comps[].

  lv_total = LINES( gt_xls[] ).
  SORT gt_xls  BY row col.

  READ TABLE gt_xls INDEX lv_total.
  lv_total = gt_xls-row.
  SHIFT lv_total LEFT DELETING LEADING '0'.

  LOOP AT gt_xls.
    CLEAR : lv_mask, lv_func.
    CONDENSE gt_xls-value.

    ASSIGN COMPONENT gt_xls-col OF STRUCTURE <ls_data>
        TO <lv_value>.
    <lv_value>  = gt_xls-value.

*-- target field type check.
    CLEAR lt_comps.
    READ TABLE lt_comps INDEX gt_xls-col.
    ASSIGN COMPONENT lt_comps-name OF STRUCTURE <ls_alv>
                                      TO <lv_field>.

    IF <lv_field> IS ASSIGNED.

      DESCRIBE FIELD <lv_field> TYPE lv_type EDIT MASK lv_mask.

      CASE lv_type.

*-- Remove comma and quote if field is not char type.
        WHEN 'b' OR 's' OR 'I'
          OR 'P' OR 'a' OR 'e' OR 'F'.
          PERFORM conv_string_remove USING    `'`
                                     CHANGING <lv_value>.
          PERFORM conv_string_remove USING    ','
                                     CHANGING <lv_value>.

          IF <lv_value> EQ '-'.
            <lv_value> = 0.
          ENDIF.
        WHEN 'D'.
          PERFORM conv_string_remove USING    '-'
                                     CHANGING <lv_value>.
          PERFORM conv_string_remove USING    '.'
                                     CHANGING <lv_value>.
        WHEN 'T'.
          PERFORM conv_string_remove USING    ':'
                                     CHANGING <lv_value>.

      ENDCASE.

*-- Conversion Routine
      IF lv_mask IS NOT INITIAL.
        PERFORM conv_string_remove USING    '='
                                   CHANGING lv_mask.
        CONCATENATE 'CONVERSION_EXIT_' lv_mask '_INPUT'
               INTO lv_func.
        CALL FUNCTION lv_func
          EXPORTING
            input  = <lv_value>
          IMPORTING
            output = <lv_value>.
      ENDIF.

      TRY.
          <lv_field> = <lv_value>.
        CATCH cx_root INTO lo_error.
          lv_msg = lo_error->if_message~get_longtext( ).
          pv_subrc = 1.
          MESSAGE lv_msg TYPE 'S' DISPLAY LIKE 'E'.
          EXIT.
      ENDTRY.
      UNASSIGN <lv_field>.
    ENDIF.

    AT END OF row.

      APPEND <ls_alv> TO pt_alv.    CLEAR : <ls_alv>.

      ADD 1 TO lv_current.
      PERFORM set_progess USING lv_current lv_total '업로드중'.
    ENDAT.

  ENDLOOP.

ENDFORM.                    " MAKE_ITAB_PROCESSING
*&---------------------------------------------------------------------*
*&      Form  CONV_STRING_REMOVE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_STR      text
*      -->P_FIELD    text
*----------------------------------------------------------------------*
FORM conv_string_remove USING pv_str  CHANGING pv_field.

  FIND FIRST OCCURRENCE OF pv_str IN pv_field.
  CHECK sy-subrc IS INITIAL.

  REPLACE ALL OCCURRENCES OF pv_str in pv_field
                                    WITH space.

ENDFORM.                    "CONV_STRING_REMOVE
*&---------------------------------------------------------------------*
*&      Form  SET_PROGESS
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->PV_IDX     text
*      -->PV_TOTAL   text
*      -->PV_MSG     text
*----------------------------------------------------------------------*
FORM set_progess  USING    pv_idx
                           pv_total
                           pv_msg.
  DATA : lv_msg     TYPE string.

  IF pv_total IS NOT INITIAL.
    CONCATENATE `( ` pv_idx `/ ` pv_total `)`
                pv_msg `...` INTO lv_msg.
  ELSE.
    lv_msg = pv_msg.
  ENDIF.

  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      text = lv_msg.

ENDFORM.                    "SET_PROGESS