SAP 批量下载表数据到EXCEL中

导语：最近有个需求，是需要一段时间把自定义表的数据拉出来，核对一下，涉及的表呢比较多，所以写了一个通用的批量程序，代码放在了最后，直接粘贴到系统就可以使用，拿走的小伙伴点个赞哈，后面会写一个改进版可以添加查询条件的。

一、文件夹名称

  系统会先根据文件夹名称创建一个文件夹在本地，然后将EXCEL写入文件夹

二、表名称的输入

  支持多个表名称，用“/”分割，最长200个字符

三、使用展示

四、代码

*&---------------------------------------------------------------------*
*& Report ZUPLOADTABLE
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zuploadtable_csdn.
*----------------------------------------------------------------------*
* Type-pools/定义类型池
*----------------------------------------------------------------------*
TYPE-POOLS:slis.

*----------------------------------------------------------------------*
* Tables/声明数据库表
*----------------------------------------------------------------------*
TABLES:sscrfields.

DATA : BEGIN OF gs_table,
         tabname TYPE tabname,
       END OF gs_table.
DATA : gt_table LIKE TABLE OF gs_table.


DATA :BEGIN OF gw_alv,  "结果显示
        deng    TYPE char4,
        tabname TYPE tabname,
        message TYPE char200,
      END OF gw_alv.
DATA : gt_alv LIKE TABLE OF gw_alv.

DATA : gv_name TYPE char20.
DATA : gv_error.


*--Other Define.
DATA:ok_code LIKE sy-ucomm,
     g_file  TYPE sapb-sappfad.


CONSTANTS:c_green  LIKE dv70a-statusicon VALUE '@08@',
          c_yellow LIKE dv70a-statusicon VALUE '@09@',
          c_red    LIKE dv70a-statusicon VALUE '@0A@'.

SELECTION-SCREEN BEGIN OF BLOCK blc_002 WITH FRAME TITLE TEXT-001.
  PARAMETERS p_1 TYPE char20 OBLIGATORY DEFAULT '导出文件'.
  PARAMETERS p_2 TYPE char200 OBLIGATORY.
  PARAMETERS p_3 TYPE char6 DEFAULT '100000' OBLIGATORY.
  SELECTION-SCREEN SKIP 1.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT (50) lv_text.
  SELECTION-SCREEN END OF LINE.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT (50) lv_text2.
  SELECTION-SCREEN END OF LINE.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT (50) lv_text3.
  SELECTION-SCREEN END OF LINE.
  SELECTION-SCREEN BEGIN OF LINE.
    SELECTION-SCREEN COMMENT (79) lv_text4.
  SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK blc_002.


*---------------------------------------------------------------------*
*  INITIALIZATION                                                     *
*---------------------------------------------------------------------*
INITIALIZATION.

  lv_text  = '1、新建文件夹名称在本地不能存在'.
  lv_text2 = '2、表名之间用“/”分割，最长可输入200位'.
  lv_text3 = '3、如果表中数据超过最大下载条数将不会被下载'.
  lv_text4 = '说明：本程序会把每张表下载成一个EXCEL，并直接在本地生成文件夹，将每个EXCEL写入文件夹中'.

AT SELECTION-SCREEN.

START-OF-SELECTION.

  PERFORM frm_get_data."获取并判断数据

  CHECK gv_error IS INITIAL.

  PERFORM frm_get_file."获取文件路径

  PERFORM frm_create_folder. "创建文件夹并修改文件路径

  CHECK g_file IS NOT INITIAL.

  PERFORM frm_download_table.


*&---------------------------------------------------------------------*
*& Form frm_button
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_button .

  CASE sscrfields-ucomm.
    WHEN 'FC01'.
      CALL FUNCTION 'VIEW_MAINTENANCE_CALL'
        EXPORTING
          action                       = 'U'
          view_name                    = 'ZTDOWNLOAD'
        EXCEPTIONS
          client_reference             = 1
          foreign_lock                 = 2
          invalid_action               = 3
          no_clientindependent_auth    = 4
          no_database_function         = 5
          no_editor_function           = 6
          no_show_auth                 = 7
          no_tvdir_entry               = 8
          no_upd_auth                  = 9
          only_show_allowed            = 10
          system_failure               = 11
          unknown_field_in_dba_sellist = 12
          view_not_found               = 13
          maintenance_prohibited       = 14
          OTHERS                       = 15.
  ENDCASE.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_get_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_get_data .

  SPLIT p_2 AT '/' INTO TABLE gt_table.

  LOOP AT gt_table INTO gs_table.
    SELECT COUNT(*) FROM dd02l WHERE tabname = gs_table-tabname AND ( tabclass = 'TRANSP' OR tabclass = 'VIEW' ).
    IF sy-subrc <> 0.
      gw_alv-deng = c_red.
      gw_alv-tabname = gs_table-tabname .
      gw_alv-message = gs_table-tabname && '不是结构或视图，无法进行导出！'.
      WRITE: / gw_alv-deng,gw_alv-message.
      gv_error = 'X'.
    ENDIF.
  ENDLOOP.


ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_dwonload_table
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_download_table .

  FIELD-SYMBOLS: <dyn_table>        TYPE STANDARD TABLE,
                 <dyn_table_header> TYPE STANDARD TABLE,
                 <dyn_wa>,
                 <dyn_wa_header>.

  DATA: dy_table        TYPE REF TO data.
  DATA: dy_line         TYPE REF TO data.
  DATA: dy_table_header TYPE REF TO data.
  DATA: dy_line_header  TYPE REF TO data.

  DATA: lt_fields  TYPE TABLE OF dd03p.
  DATA: lw_fields  TYPE dd03p.
  DATA: xfc        TYPE lvc_s_fcat.
  DATA: ifc        TYPE lvc_t_fcat.
  DATA: xfc_header TYPE lvc_s_fcat.
  DATA: ifc_header TYPE lvc_t_fcat.

  DATA: lv_lines TYPE i.
  DATA: gv_file  TYPE rlgrap-filename.
  DATA: lv_str   TYPE string.
  DATA: lv_text  TYPE char50.

  LOOP AT gt_table INTO gs_table.
    CLEAR: lv_lines,gv_file.
    SELECT COUNT(*) INTO lv_lines FROM (gs_table-tabname).
    IF lv_lines > p_3."如果表数据大于6w不进行导出操作，在最后的显示上报错
      gw_alv-deng = c_red.
      gw_alv-tabname = gs_table-tabname.
      gw_alv-message = '数据大于' && p_3 && '条，不进行导出！'.
      APPEND gw_alv TO gt_alv.
    ELSE.
      CLEAR lv_text.
      lv_text = '正在下载:' && gs_table-tabname && ',请稍等.....'.
      CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
        EXPORTING
          text = lv_text.

      REFRESH : lt_fields,ifc,ifc_header.

      CALL FUNCTION 'DDIF_TABL_GET'
        EXPORTING
          name          = gs_table-tabname
          langu         = sy-langu
        TABLES
          dd03p_tab     = lt_fields
        EXCEPTIONS
          illegal_input = 1
          OTHERS        = 2.

      DELETE lt_fields WHERE fieldname = '.INCLUDE' OR fieldname = '.INCLU--AP'.

      "创建存储抬头数据的动态内表
      LOOP AT lt_fields INTO lw_fields .
        CLEAR xfc_header.
        xfc_header-fieldname  = lw_fields-fieldname.
        xfc_header-inttype    = 'C' .
        xfc_header-datatype   = 'CHAR'.
        xfc_header-intlen     = lw_fields-leng + lw_fields-decimals.
        IF xfc_header-intlen <= '000030'.
          xfc_header-intlen = '000030'.
        ENDIF.
        APPEND xfc_header TO ifc_header.
      ENDLOOP.

      CALL METHOD cl_alv_table_create=>create_dynamic_table
        EXPORTING
          it_fieldcatalog = ifc_header
        IMPORTING
          ep_table        = dy_table_header.
      ASSIGN dy_table_header->* TO <dyn_table_header>.
      CREATE DATA dy_line_header LIKE LINE OF <dyn_table_header>.
      ASSIGN dy_line_header->* TO <dyn_wa_header>.

*-->填充表头数据

      "填充字段名
      LOOP AT lt_fields INTO lw_fields.
        ASSIGN COMPONENT lw_fields-fieldname OF STRUCTURE <dyn_wa_header> TO FIELD-SYMBOL(<fs>).
        IF sy-subrc = 0.
          <fs> = lw_fields-fieldname.
        ENDIF.
      ENDLOOP.
      APPEND <dyn_wa_header> TO <dyn_table_header>.
      CLEAR <dyn_wa_header>.

      "填充字段描述
      LOOP AT lt_fields INTO lw_fields.
        ASSIGN COMPONENT lw_fields-fieldname OF STRUCTURE <dyn_wa_header> TO <fs>.
        IF sy-subrc = 0.
          <fs> = lw_fields-ddtext.
        ENDIF.
      ENDLOOP.
      APPEND <dyn_wa_header> TO <dyn_table_header>.

      "创建存储数据动态内表
      LOOP AT lt_fields INTO lw_fields .
        CLEAR xfc.
        xfc-fieldname  = lw_fields-fieldname.
        xfc-inttype    = lw_fields-inttype .
        xfc-datatype   = lw_fields-datatype.
        xfc-intlen     = lw_fields-leng + lw_fields-decimals.
        xfc-ref_field  = lw_fields-fieldname.
        xfc-ref_table  = gs_table-tabname.
        xfc-decimals   = lw_fields-decimals.
        APPEND xfc TO ifc.
      ENDLOOP.

      CALL METHOD cl_alv_table_create=>create_dynamic_table
        EXPORTING
          it_fieldcatalog = ifc
        IMPORTING
          ep_table        = dy_table.
      ASSIGN dy_table->* TO <dyn_table>.
      CREATE DATA dy_line LIKE LINE OF <dyn_table>.
      ASSIGN dy_line->* TO <dyn_wa>.

      SELECT * FROM (gs_table-tabname) INTO CORRESPONDING FIELDS OF TABLE <dyn_table>.

      "由于<dyn_table> 和 <dyn_table_header>的表结构不完全一致，所以只能逐条循环赋值
      LOOP AT <dyn_table> INTO <dyn_wa>.
        MOVE-CORRESPONDING <dyn_wa> TO <dyn_wa_header>.
        APPEND <dyn_wa_header> TO <dyn_table_header>.
      ENDLOOP.

*-->导出EXCEL
      "路径拼接
      gv_file = g_file.
      lv_str = gs_table-tabname && '.XLS'.
      REPLACE gv_name WITH lv_str INTO gv_file.

      "导出
      CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
        EXPORTING
          i_filename        = gv_file
        TABLES
          i_tab_sap_data    = <dyn_table_header>
        EXCEPTIONS
          conversion_failed = 1
          OTHERS            = 2.

      gw_alv-deng = c_green.
      gw_alv-tabname = gs_table-tabname.
      gw_alv-message = '导出成功，路径为' && gv_file.
      APPEND gw_alv TO gt_alv.
      WRITE : gw_alv-deng,gw_alv-message.

      CLEAR :dy_table,dy_line,dy_table_header,dy_line_header.
      UNASSIGN: <dyn_table>,<dyn_wa>,<dyn_table_header>,<dyn_wa_header>.
    ENDIF.

  ENDLOOP.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_create_folder
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_create_folder .

  DATA : BEGIN OF ls_folder,
           dire TYPE localfile,
         END OF ls_folder.
  DATA : lt_folder LIKE TABLE OF ls_folder.

  "检查文件夹是否存在
  DATA : status     TYPE c.
  DATA : l_file     TYPE localfile.
  DATA : lv_message TYPE char100.
  DATA : lv_lines   TYPE i.

  IF g_file IS NOT INITIAL.

    g_file = g_file && '\' && p_1.
    l_file = g_file.
    SPLIT l_file AT '\' INTO TABLE lt_folder.
    "把最后的后缀先摘出来
    lv_lines = lines( lt_folder ).
    READ TABLE lt_folder INTO ls_folder INDEX lv_lines.
    CLEAR gv_name.
    gv_name = ls_folder-dire.
    lv_lines = lv_lines - 1.
    DELETE lt_folder INDEX lv_lines."去掉最后一行
    "重新改造文件夹名称
    CLEAR l_file.
    LOOP AT lt_folder INTO ls_folder.
      IF l_file IS INITIAL.
        l_file = ls_folder-dire.
      ELSE.
        CONCATENATE l_file '\' ls_folder-dire INTO l_file.
      ENDIF.
    ENDLOOP.

    "检查该文件夹是否已经存在
    PERFORM checkdir USING l_file CHANGING status.
    IF status = 1.
      lv_message = l_file && '已存在'.
      MESSAGE lv_message TYPE 'E'.
    ELSE.
      READ TABLE lt_folder INTO ls_folder INDEX 1.
      CLEAR l_file .
      l_file = ls_folder-dire.
      LOOP AT lt_folder INTO ls_folder.
        IF sy-tabix > 1.
          CONCATENATE l_file '\' ls_folder-dire INTO l_file.
          PERFORM checkdir USING l_file CHANGING status.
          IF status = 0.
            PERFORM createrdir USING l_file .
          ENDIF.
        ENDIF.
      ENDLOOP.
    ENDIF.
  ENDIF.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form checkdir
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> G_FILE
*&      <-- STATUS
*&---------------------------------------------------------------------*
FORM checkdir USING dir TYPE localfile CHANGING ret TYPE c.
  CALL FUNCTION 'WS_QUERY'
    EXPORTING
*     ENVIRONMENT    =
      filename       = dir
      query          = 'DE'
*     WINID          =
    IMPORTING
      return         = ret
    EXCEPTIONS
      inv_query      = 1
      no_batch       = 2
      frontend_error = 3
      OTHERS         = 4.
ENDFORM.                    "checkdir
*&---------------------------------------------------------------------*
*& Form createrdir
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> L_FILE
*&---------------------------------------------------------------------*
FORM createrdir  USING  dir TYPE localfile.
  CALL FUNCTION 'GUI_CREATE_DIRECTORY'
    EXPORTING
      dirname = dir
    EXCEPTIONS
      failed  = 1
      OTHERS  = 2.
  IF sy-subrc <> 0 AND sy-subrc <> 1.
    MESSAGE '文件夹创建失败，请重新操作' TYPE 'E'.
    CLEAR g_file.
  ELSE.
    g_file = dir && '\' && gv_name.
  ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_get_file
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_get_file .

  DATA: l_str  TYPE string.

  "获取本地存放EXCEL的路径
  l_str = '导出文件'.
  CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
      def_filename     = l_str "
      def_path         = g_file
      mask             = ',*.xls,*.xls,*.xlsx,*.xlsx.'
      mode             = 'S'
      title            = '保存路径'
    IMPORTING
      filename         = g_file
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      OTHERS           = 5.

ENDFORM.

作者：小飞猪猪猪猪猪猪猪–CSDN