*EXCEL 參數
DATA:gs_application TYPE ole2_object.
DATA:gs_borders     TYPE ole2_object.
DATA:gs_workbook    TYPE ole2_object.
DATA:gs_sheet       TYPE ole2_object.
DATA:gs_cell        TYPE ole2_object.
DATA:gs_font        TYPE ole2_object.

  DATA: lv_subrc    TYPE sy-subrc,
        lv_fullpath TYPE string.
*選擇文件。
  DATA: l_name     LIKE  wwwdatatab,
        l_mine     LIKE  w3mime OCCURS 10,
        l_title    TYPE  string,
        l_filename TYPE  string,
        l_path     TYPE  string,
        l_fullpath TYPE  string.

  PERFORM frm_process_excle_data.

  IF p_auart = 'ZHQ1' OR p_auart = 'ZHQ2'.
    CHECK gt_excel_zhq1 IS NOT INITIAL.
  ELSE.
    CHECK gt_excel_other IS NOT INITIAL.
  ENDIF.
  PERFORM frm_check_ole CHANGING lv_subrc.
  IF lv_subrc <> 0.
    MESSAGE i000(oo) WITH '文件下載失敗，請檢查是否安裝了OFFICE EXCEL軟件'.
    RETURN.
  ENDIF.
  " 下載excel模板
  IF p_auart = 'ZHQ1' OR p_auart = 'ZHQ2'.
    PERFORM download_excel_fromserver USING   'ZNYSD00400_1'
                                        CHANGING lv_fullpath.
  ELSE.
    PERFORM download_excel_fromserver USING   'ZNYSD00400_2'
                                        CHANGING lv_fullpath.
  ENDIF.

  " 打開excel填充數據
  PERFORM frm_fill_excel  USING lv_fullpath.

  MESSAGE s000(oo) WITH '下載完畢！'.

FORM frm_process_excle_data .
  CLEAR:gs_excel_zhq1,gs_excel_zhq1,gs_excel_other,gt_excel_other.
  LOOP AT gt_vbak INTO gs_vbak.
    MOVE-CORRESPONDING gs_vbak TO gs_excel_zhq1.
    APPEND gs_excel_zhq1 TO gt_excel_zhq1.

    MOVE-CORRESPONDING gs_vbak TO gs_excel_other.
    APPEND gs_excel_other TO gt_excel_other.

    CLEAR:gs_excel_zhq1,gs_vbak,gs_excel_other.
  ENDLOOP.
ENDFORM.

FORM frm_check_ole  CHANGING pv_subrc TYPE sy-subrc.
  pv_subrc = 0.
  CREATE OBJECT gs_application 'excel.APPLICATION'.
  pv_subrc = sy-subrc.
  FREE OBJECT gs_application.
ENDFORM.

FORM download_excel_fromserver  USING pv_objid       LIKE wwwdatatab-objid
                               CHANGING pv_fullpath    TYPE string.

  DATA:ls_errtxt  TYPE string.
  DATA:lw_object  LIKE wwwdatatab.
  DATA:l_filename TYPE string,
       l_path     TYPE string,
       l_fullpath TYPE string,
       l_title    TYPE string.
  DATA l_destination LIKE rlgrap-filename.

****查詢模板
  SELECT SINGLE *
      FROM wwwdata
      INTO CORRESPONDING FIELDS OF lw_object
      WHERE srtf2 = 0
  AND objid = pv_objid.

  IF sy-subrc <> 0 OR lw_object-objid = space .
    CONCATENATE '模板文件：' pv_objid '不存在，請用TCODE：SMW0進行加載' INTO ls_errtxt.
    MESSAGE e000(oo) WITH ls_errtxt.
  ENDIF.

  l_title = lw_object-text.

  IF pv_objid IS NOT INITIAL.
    CALL METHOD cl_gui_frontend_services=>file_save_dialog
      EXPORTING
        window_title      = l_title
        default_extension = 'XLSX'
        default_file_name = l_title
        file_filter       = 'EXCEL(*.xls)|*.xls*'
      CHANGING
        filename          = l_filename
        path              = l_path
        fullpath          = l_fullpath
      EXCEPTIONS
        cntl_error        = 1
        error_no_gui      = 2
        OTHERS            = 3.
    IF l_fullpath IS NOT INITIAL.
      pv_fullpath   = l_fullpath.

      l_destination = l_fullpath.
      CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
        EXPORTING
          key         = lw_object
          destination = l_destination.
    ENDIF.
  ENDIF.
ENDFORM.

FORM frm_fill_excel  USING  pv_fullpath TYPE string.
  DATA: lt_field_cat TYPE lvc_t_fcat.

  PERFORM open_excel_sheet USING 1 pv_fullpath.

*  PERFORM frm_insert_data.
  IF p_auart = 'ZHQ1' OR p_auart = 'ZHQ2'.
    PERFORM frm_paste_data  TABLES  gt_excel_zhq1
                                  lt_field_cat
                           USING  '3'
                                  '1'.
  ELSE.
    PERFORM frm_paste_data1  TABLES  gt_excel_other
                                  lt_field_cat
                           USING  '3'
                                  '1'.
  ENDIF.


  PERFORM frm_close_excel.
  PERFORM frm_free_object.
ENDFORM.

FORM open_excel_sheet  USING pv_sheet
                               pv_fullpath   TYPE string.
  CREATE OBJECT gs_application 'excel.APPLICATION'.
  SET PROPERTY OF gs_application 'Visible' = 0.
  CALL METHOD OF
    gs_application
      'Workbooks' = gs_workbook.
  CALL METHOD OF
    gs_workbook
    'Open' = gs_workbook
    EXPORTING
    #1 = pv_fullpath.
  CALL METHOD OF
    gs_workbook
    'Worksheets' = gs_sheet
    EXPORTING
    #1 = pv_sheet.
  CALL METHOD OF
    gs_sheet
    'Activate'.
ENDFORM.

FORM frm_paste_data  TABLES pt_output
                           pt_field_cat TYPE lvc_t_fcat
                     USING pv_row        TYPE i
                           pv_col        TYPE i.

  TYPES: BEGIN OF ty_clipboard,
           line(4096) TYPE c,
         END OF ty_clipboard.

  DATA: lt_clipboard    TYPE STANDARD TABLE OF ty_clipboard.

  DATA: lc_tab       TYPE c VALUE cl_abap_char_utilities=>horizontal_tab,
        lv_line      TYPE string,
        lv_str_temp  TYPE string,
        lt_excel     TYPE TABLE OF lvc_s_fcat,
        ls_excel     TYPE lvc_s_fcat,
*        ls_alv1      LIKE LINE OF gt_alv1,
        ls_clipboard TYPE ty_clipboard,
        lv_tabix     TYPE i,
        lv_subrc     TYPE i.
  DATA: lr_dyn_tab TYPE REF TO data,
        lr_dyn_str TYPE REF TO data.

  FIELD-SYMBOLS:
    <fs_field> TYPE any,
    <ft_dyn>   TYPE STANDARD TABLE,
    <fs_dyn>   TYPE any.

  IF pt_field_cat[] IS NOT INITIAL.
    CALL METHOD cl_alv_table_create=>create_dynamic_table
      EXPORTING
        it_fieldcatalog = pt_field_cat[]
      IMPORTING
        ep_table        = lr_dyn_tab.
  ELSE.
    GET REFERENCE OF gt_excel_zhq1 INTO lr_dyn_tab.
  ENDIF.

  ASSIGN lr_dyn_tab->* TO <ft_dyn>.
  CREATE DATA lr_dyn_str LIKE LINE OF <ft_dyn>.
  ASSIGN lr_dyn_str->* TO <fs_dyn>.

  CLEAR: lt_clipboard[].
  LOOP AT gt_excel_zhq1 INTO gs_excel_zhq1.
    MOVE-CORRESPONDING gs_excel_zhq1 TO <fs_dyn>.
    DO 50 TIMES.
      lv_tabix = sy-index.
      ASSIGN COMPONENT lv_tabix OF STRUCTURE <fs_dyn> TO <fs_field>.
      IF sy-subrc = 0 AND <fs_field> IS ASSIGNED.
        lv_str_temp = <fs_field>.
        IF lv_tabix = 1.
          lv_line = lv_str_temp.
        ELSE.
          CONCATENATE lv_line lc_tab lv_str_temp INTO lv_line.
        ENDIF.
      ELSE.
        ls_clipboard-line = lv_line.
        APPEND ls_clipboard TO lt_clipboard.
        CLEAR: ls_clipboard.
        EXIT.
      ENDIF.
    ENDDO.
  ENDLOOP.

  " 列表內容
  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data                 = lt_clipboard
    CHANGING
      rc                   = lv_subrc
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.

  CALL METHOD OF gs_application
    'CELLS' = gs_cell
    EXPORTING
    #1 = pv_row
    #2 = pv_col.
  CALL METHOD OF gs_cell
    'Select'.
  CALL METHOD OF gs_sheet
    'Paste'.
ENDFORM.

FORM frm_close_excel .
  GET PROPERTY OF gs_application 'ActiveSheet' = gs_sheet.
  FREE OBJECT gs_sheet.
  GET PROPERTY OF  gs_application 'ActiveWorkbook' = gs_workbook.
  CALL METHOD OF
    gs_workbook
    'SAVE'.
  CALL METHOD OF
    gs_workbook
    'CLOSE'
    EXPORTING
      #1 = 0.
  CALL METHOD OF
    gs_application
    'QUIT'.
ENDFORM.

FORM frm_free_object .
  FREE OBJECT gs_font.
  FREE OBJECT gs_cell.
  FREE OBJECT gs_sheet.
  FREE OBJECT gs_workbook.
  FREE OBJECT gs_borders.
  FREE OBJECT gs_application.
ENDFORM.