将ALV数据快速下载到EXCEL

*&-----------------------------------------------------------------*
*& REPORT ZPS001
*&-----------------------------------------------------------------*
********************************************************************
* 事务代码：                                                       *
* 程序名称：ZDEMO_ALV_DOWN                                         *
* 程序目的：下载到EXCEL文件模板                                    *
* 使用变式：                                                       *
* 设 计 人：                                              *
* 设计时间：2021/11/17                                             *
* 程序类型: ABAP/4 程序 ，报表                                     *
* 输入文件:                                                        *
* 输出文件:                                                        *
* 应用类型:                                              *
* 描    述: 物料主数据                              *
*(修改日志)--------------------------------------------------------*
*                                                                  *
* 日志号   修改人  修改时间       修改说明              请求号码   *
*  ----    ----    ------         -----------
*
********************************************************************
REPORT zdemo_alv_down.

*******************************
*数据类型定义
*******************************
*&-----------------------------------------------------------------*
*&  包含                ZMMR_812_TOP
*&-----------------------------------------------------------------*

TYPE-POOLS: slis,icon.

TABLES:makt.

DATA:BEGIN OF t_fieldnames  OCCURS 0,
       name TYPE char20,
     END OF t_fieldnames.

TYPES: BEGIN OF ty_tab_data,
         " box   TYPE c,
         matnr TYPE makt-matnr,
         maktx TYPE makt-maktx,
       END OF ty_tab_data.

*&-----------------------------------------------------------------
*
*内表、工作区、变量、常量、指针定义
*
*&-----------------------------------------------------------------
DATA: gt_data  TYPE STANDARD TABLE OF ty_tab_data,
      gt_data1 TYPE STANDARD TABLE OF ty_tab_data,
      gs_data  TYPE ty_tab_data,
      gs_data1 TYPE ty_tab_data.

DATA: gs_layout   TYPE lvc_s_layo.
DATA: gt_fieldcat TYPE lvc_t_fcat,
      gs_fieldcat TYPE lvc_s_fcat.
DATA: gt_sort TYPE lvc_t_sort,
      gs_sort TYPE lvc_s_sort.

*&------------------------------------------------*
*&下載模板需要參數
*&------------------------------------------------*
DATA: gv_fullpath TYPE string,
      gv_path     TYPE string,
      gv_name     TYPE string,
      gv_fname    TYPE string.
DATA lv_filename1 TYPE string.
*******************************
*屏幕处理
*******************************
SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS:s_matnr FOR makt-matnr."公司代码
PARAMETERS:p_maktx TYPE makt-maktx."项目（产品）名称
SELECTION-SCREEN END OF BLOCK blk1.

*******************************
*主程序入口
*******************************
*&--------------------------------------------------------------
**初始化处理
*&--------------------------------------------------------------
INITIALIZATION.

*&--------------------------------------------------------------
**选择屏幕控制
*&--------------------------------------------------------------
AT SELECTION-SCREEN OUTPUT.

*&-------------------------------------------------------------*
*& 参数输入检查
*&-------------------------------------------------------------*
AT SELECTION-SCREEN.

*&-------------------------------------------------------------
**程序开始处理
*&-------------------------------------------------------------
START-OF-SELECTION.
********************************
*获取数据
********************************
  PERFORM get_data.
*********************************
**处理数据
*********************************
  PERFORM pro_data.

********************************
*显示数据
********************************

* ALV显示
  PERFORM frm_set_alv.          "设置ALV属性
  PERFORM frm_dis_alv.          "调用ALV

END-OF-SELECTION.

*******************************
*FORM子程序
*******************************
*&---------------------------------------------------------------------*
*&      Form  GET_DATA
*&---------------------------------------------------------------------*
*       获取数据
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM get_data.
  SELECT matnr maktx
    INTO CORRESPONDING FIELDS OF TABLE gt_data1
    FROM makt
    UP TO 30 ROWS
    WHERE matnr IN s_matnr
    .
ENDFORM.         " GET_DATA

*&---------------------------------------------------------------------*
*&      Form  pro_DATA
*&---------------------------------------------------------------------*
*       数据处理
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM pro_data.
  MOVE gt_data1 TO gt_data.
  IF gt_data IS INITIAL.
    MESSAGE '未查询到符合条件的数据！' TYPE 'S' DISPLAY LIKE 'E'.
    LEAVE LIST-PROCESSING.
  ENDIF.
ENDFORM.       " PRO_DATA

*&---------------------------------------------------------------------*
*&      Form  FRM_SET_ALV
*&---------------------------------------------------------------------*
*       alv设置
*---------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM frm_set_alv.
  DATA: l_colpos TYPE lvc_s_fcat-col_pos VALUE 0.

*&---------------------------------------------------------------------*
*&      定义宏
*&---------------------------------------------------------------------*
  DEFINE  macro_fill_fcat.
    CLEAR gs_fieldcat.
    &1 = &1 + 1.
    gs_fieldcat-col_pos       = &1.
    gs_fieldcat-fieldname     = &2.
    gs_fieldcat-ref_table     = &3.
    gs_fieldcat-ref_field     = &4.    "内表中数量参照字段
    gs_fieldcat-coltext       = &5.
    APPEND gs_fieldcat TO gt_fieldcat.
  END-OF-DEFINITION.

  FIELD-SYMBOLS: <fs_fieldcat> TYPE lvc_s_fcat.

  CLEAR gt_fieldcat.
  macro_fill_fcat:
                   l_colpos  'MATNR'      ''     ''         '物料编号',"序号
                   l_colpos  'MAKTX'     ''     ''          '物料说明'." 項目(产品)名称
  CLEAR gs_layout.
  gs_layout-zebra = 'X'.
  " gs_layout-box_fname = 'BOX'.
  gs_layout-cwidth_opt = 'X'.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  FRM_DIS_ALV
*&---------------------------------------------------------------------*
*       alv输出
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM frm_dis_alv .

  DATA: lv_grid TYPE lvc_s_glay.

  lv_grid-edt_cll_cb = 'X'.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
    EXPORTING
      i_callback_program       = sy-repid
      i_callback_pf_status_set = 'PF_STATUS_SET'
      i_callback_user_command  = 'FRM_USER_COMMAND'
      i_grid_settings          = lv_grid
      is_layout_lvc            = gs_layout
      it_fieldcat_lvc          = gt_fieldcat
    TABLES
      t_outtab                 = gt_data.

ENDFORM.                "     FRM_ALV

*&---------------------------------------------------------------------*
*&      Form  PF_STATUS_SET
*&---------------------------------------------------------------------*
*       gui状态
*----------------------------------------------------------------------*
*      -->TR_EXTAB   text
*----------------------------------------------------------------------*
FORM pf_status_set USING pr_extab TYPE slis_t_extab.

  SET PF-STATUS 'STANDARD' .

ENDFORM.                 "   PF_STATUS_SET
*&---------------------------------------------------------------------*
*&      Form  frm_user_command
*&---------------------------------------------------------------------*
*       用戶响应事件
*----------------------------------------------------------------------*
*      -->R_UCOMM      text
*      -->RS_SELFIELD  text
*----------------------------------------------------------------------*

FORM frm_user_command USING r_ucomm LIKE sy-ucomm
                           rs_selfield TYPE slis_selfield.
*刷新alv
  DATA:lob_grid TYPE REF TO cl_gui_alv_grid.
  CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
    IMPORTING
      e_grid = lob_grid.
  CALL METHOD lob_grid->check_changed_data.

  CASE r_ucomm.
    WHEN '&TEST'.
      PERFORM frm_down_excel.
  ENDCASE.
  rs_selfield-refresh = 'X'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_DOWN_EXCEL
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_down_excel .
*      PERFORM frm_get_fullpath CHANGING gv_fullpath gv_path gv_name.
** 路徑為空則退出
*      IF gv_fullpath IS INITIAL.
*        MESSAGE TEXT-020 TYPE 'S'."用戶取消操作
*        RETURN.
*      ENDIF.
*      PERFORM frm_down USING gv_fullpath."下載範本
  lv_filename1 = 'C:\Users\Ric\Desktop\11.xls'.
  PERFORM frm_downdata.

ENDFORM.

*&---------------------------------------------------------------------*
*& Form FRM_GET_FULLPATH
*&---------------------------------------------------------------------*
*& 獲取桌面的路徑，並將數據填入
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_get_fullpath CHANGING pv_fullpath TYPE string
                                 pv_path     TYPE string
                                 pv_name     TYPE string.

  DATA: lv_init_path  TYPE string,
        lv_init_fname TYPE string,
        lv_path       TYPE string,
        lv_filename   TYPE string,
        lv_fullpath   TYPE string.
  lv_init_fname = 'Confirmation Excel.xls'."初始名稱(輸出的檔案名稱)

* 獲取桌面路徑
  CALL METHOD cl_gui_frontend_services=>get_desktop_directory
    CHANGING
      desktop_directory    = lv_init_path
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
    EXIT.
  ENDIF.

* 用戶選擇名稱、路徑
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      default_extension    = 'XLS'
      default_file_name    = lv_init_fname
      initial_directory    = lv_init_path
      prompt_on_overwrite  = 'X'
    CHANGING
      filename             = lv_filename "檔案名
      path                 = lv_path
      fullpath             = lv_fullpath
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc = 0.
    pv_fullpath = lv_fullpath.
    pv_path     = lv_path.
  ENDIF.

ENDFORM. " FRM_GET_FULLPATH

*&---------------------------------------------------------------------*
*&      Form  FRM_DOWN
*&---------------------------------------------------------------------*
*       下載xls範本
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM frm_down USING pv_filename.
  DATA: lv_objdata     LIKE wwwdatatab,
        lv_mime        LIKE w3mime,
        lv_destination LIKE rlgrap-filename,
        lv_objnam      TYPE string,
        lv_rc          LIKE sy-subrc,
        lv_errtxt      TYPE string.
  DATA: lv_filename TYPE string,
        lv_result,
        lv_subrc    TYPE sy-subrc.

  DATA: lv_objid TYPE wwwdatatab-objid .
  lv_objid = 'TEST.XLS'.  "上傳的範本名稱，需要自己定義後再弄。

  "查找檔是否報錯。
  SELECT SINGLE relid objid
    FROM wwwdata
    INTO CORRESPONDING FIELDS OF lv_objdata
  WHERE srtf2    = 0
  AND   relid    = 'MI'
  AND   objid    = lv_objid.

  "判斷範本不存在則報錯
  IF sy-subrc NE 0 OR lv_objdata-objid EQ space.
    CONCATENATE TEXT-024  lv_objid TEXT-025"範本檔不存在，請用TCODE：smwo上傳
    INTO lv_errtxt.
    MESSAGE e000(su) WITH lv_errtxt.
  ENDIF.

  lv_filename = pv_filename.
  lv_filename1 = pv_filename.
  "判斷本地地址是否已經存在此檔。
  CALL METHOD cl_gui_frontend_services=>file_exist
    EXPORTING
      file                 = lv_filename
    RECEIVING
      result               = lv_result
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      wrong_parameter      = 3
      not_supported_by_gui = 4
      OTHERS               = 5.
  IF lv_result EQ 'X'.  "如果存在則刪除檔，並覆蓋
    CALL METHOD cl_gui_frontend_services=>file_delete
      EXPORTING
        filename             = lv_filename
      CHANGING
        rc                   = lv_subrc
      EXCEPTIONS
        file_delete_failed   = 1
        cntl_error           = 2
        error_no_gui         = 3
        file_not_found       = 4
        access_denied        = 5
        unknown_error        = 6
        not_supported_by_gui = 7
        wrong_parameter      = 8
        OTHERS               = 9.
    IF lv_subrc <> 0. "如果刪除失敗，則報錯
      CONCATENATE TEXT-026 TEXT-027"同名EXCEL檔已打開，請關閉該EXCEL後重試
      INTO lv_errtxt.
      MESSAGE e000(su) WITH lv_errtxt.
    ENDIF.
  ENDIF.

  lv_destination   = pv_filename.

  "下載模版。
  CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
    EXPORTING
      key         = lv_objdata
      destination = lv_destination
    IMPORTING
      rc          = lv_rc.
  IF lv_rc NE 0.
    CONCATENATE TEXT-024 TEXT-028 INTO lv_errtxt."範文檔下載失敗
    MESSAGE e000(su) WITH lv_errtxt.
  ENDIF.
  lv_filename1  = lv_destination."用於下載數據的路徑
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_DOWNDATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_downdata .
  LOOP AT gt_fieldcat INTO gs_fieldcat.
    t_fieldnames-name  = gs_fieldcat-coltext .
    APPEND t_fieldnames.
  ENDLOOP.
  " append gt_fieldcat to t_fieldnames.
  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
*     BIN_FILESIZE            =
      filename                = lv_filename1
      filetype                = 'DAT'
      "这里一般用DAT，如果用ASC则1000-不会显示为-1000，而dat会显示为-1000，如果用DBF则不会有缩进，
      "即字符前面的空格会被除去,而且字符的前导0也会输出。
      write_field_separator   = 'X'  "列由制表符分隔  该参数仅对文件类型值ASC、DAT和IBM有意义；对于DAT，它是隐式设置的。
      no_auth_check           = 'X'
      codepage                = '8404'
    TABLES
      data_tab                = gt_data
      fieldnames              = t_fieldnames[]
    EXCEPTIONS
      file_write_error        = 1
      no_batch                = 2
      gui_refuse_filetransfer = 3
      invalid_type            = 4
      no_authority            = 5
      unknown_error           = 6
      header_not_allowed      = 7
      separator_not_allowed   = 8
      filesize_not_allowed    = 9
      header_too_long         = 10
      dp_error_create         = 11
      dp_error_send           = 12
      dp_error_write          = 13
      unknown_dp_error        = 14
      access_denied           = 15
      dp_out_of_memory        = 16
      disk_full               = 17
      dp_timeout              = 18
      file_not_found          = 19
      dataprovider_exception  = 20
      control_flush_error     = 21
      OTHERS                  = 22.
  IF sy-subrc <> 0.
    MESSAGE '导出异常' TYPE 'E'.
  ENDIF.
ENDFORM.