SAP ABAP任意表数据查询+快速下载工具

DeveloperMrMeng
已于 2024-09-14 09:36:56 修改
阅读量887
点赞数 6
分类专栏： SAP技术小知识文章标签： SAP ABAP 数据查询快速下载表格下载关联查询
于 2024-09-06 11:32:48 首次发布
本文链接：https://blog.csdn.net/developermrmeng/article/details/141953664
版权
SAP技术小知识专栏收录该内容
61 篇文章 31 订阅
订阅专栏
背景：

项目上业务顾问有时候需要下载标准表大量的数据到Excel进行一些比对，但是标准SE16N，SE16的下载电子表格功能在遇到大批量数据的时候会非常慢，于是抽空写了个通用的查询下载工具，可以快速的下载数据。
2024.09.13更新：超大数量下载时分批多次下载还是有点麻烦，所以还是需要分批下载，超大文件下载时xlsx还是比较慢，索性直接禁用了，相关代码留着可以参考。

分批下载需要使用游标查询，OPEN CURSOR，但是GUI_DOWNLOAD会隐式关闭游标，导致第二次游标查询引发Dump：DBIF_RSQL_INVALID_CURSOR，所以看起来只能借助服务器暂存一下，查询完之后分批下载。
为了避免占用太多服务器空间，进行ZIP压缩后存储，下载后及时删除文件。
现在可以保留动态选择输入的条件，不会需要再次重新输入。
工具界面：

功能介绍：

单表查询：输入表名，执行后会进入动态条件选择界面，可自行添加选择条件。
跨表关联查询：使用NativeSql语句查询你想要的数据，如果要查询SAP系统表数据，数据库连接名使用默认的‘DEFAULT’即可；如果要查询外部数据库数据，选择对应的DBCO连接名即可。（需要注意的是，查询SAP表数据时，需要添加MANDT条件限制客户端，否则将会查询所有客户端的数据）。
下载功能：目前支持.txt/.csv/.xlsx三种格式的文件下载，.txt和.csv实际上都是文本格式，代码将数据转换为二进制形式下载，所以下载速度是最快的，.xlsx采用的OLE下载方式，下载速度相对来说会慢一些，但还是会标准的下载功能快一些的，但实际上下载为csv格式就可以正常用了，另存为xlsx格式就行，默认为自己电脑桌面路径，如果数据量过大导致资源瓶颈dump的话，也可以自行进行改造，使用游标查询分批将数据保存至服务器目录(AL11)再分批下载，本程序没有处理这种资源瓶颈问题，所以数据量过大时建议按条件进行查询下载。
使用示例：

1.单表查询：

2.单表下载：

3.跨表查询：

4.跨表下载：

源码：

*&---------------------------------------------------------------------*
*& Report ZDOWNLOAD_TABLE_WITH_SEL
*&---------------------------------------------------------------------*
*& Author:DeveloprMrMeng
*& Usage :根据选择屏幕条件动态查询数据或者下载数据
*& Date  :2024.07.15
*&---------------------------------------------------------------------*
REPORT zdownload_table_with_sel.
TABLES:sscrfields.
TYPES:
  ty_excl TYPE rsexfcode,
  tt_excl TYPE STANDARD TABLE OF ty_excl.

DATA:
  go_dock        TYPE REF TO cl_gui_docking_container,
  go_text_editor TYPE REF TO cl_gui_textedit,
  gv_text_newtxt TYPE string,
  gv_text_oldtxt TYPE string,
  gt_texttab     TYPE soli_tab,
  gs_texttab     TYPE soli.

DATA:
  gv_sql              TYPE alk_string,
  gv_package_size     TYPE i VALUE 100000,
  gt_field_ranges_int TYPE rsds_trange,
  gv_file_name        TYPE string,
  gv_file_extension   TYPE string,
  gv_separator        TYPE string,
  gv_body_xstring     TYPE xstring,
  gv_zip_xstring      TYPE xstring,
  gt_zip_list         TYPE STANDARD TABLE OF string,
  gv_zip_name         TYPE string.

DATA:
  gt_fieldcat TYPE lvc_t_fcat,
  gs_fieldcat TYPE lvc_s_fcat.

FIELD-SYMBOLS:
  <gs_table> TYPE data,
  <gt_table> TYPE STANDARD TABLE.

SELECTION-SCREEN FUNCTION KEY 1.
SELECTION-SCREEN BEGIN OF BLOCK opt WITH FRAME TITLE t_opt.
PARAMETERS:
  p_singl RADIOBUTTON GROUP gp1 DEFAULT 'X' USER-COMMAND uc,
  p_multi RADIOBUTTON GROUP gp1.

PARAMETERS:
  p_down AS CHECKBOX USER-COMMAND down DEFAULT 'X'.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN POSITION 3.
SELECTION-SCREEN COMMENT 4(12) t_pksize FOR FIELD p_pksize MODIF ID dow.
PARAMETERS:p_pksize TYPE i DEFAULT 250000 MODIF ID dow.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK opt.

SELECTION-SCREEN BEGIN OF BLOCK single WITH FRAME TITLE t_sigl.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(31) t_tab FOR FIELD p_tab MODIF ID sig.
PARAMETERS:p_tab TYPE dd02l-tabname MODIF ID sig.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK single.

SELECTION-SCREEN BEGIN OF BLOCK multiple WITH FRAME TITLE t_multi.
PARAMETERS:
  p_conn TYPE dbcon-con_name MODIF ID mul DEFAULT 'DEFAULT'.
SELECTION-SCREEN END OF BLOCK multiple.

SELECTION-SCREEN BEGIN OF BLOCK down WITH FRAME TITLE t_down.
PARAMETERS:
  p_file TYPE rlgrap-filename MODIF ID dow MEMORY ID file.
PARAMETERS:
  p_txt  RADIOBUTTON GROUP gp2 MODIF ID dow,
  p_csv  RADIOBUTTON GROUP gp2 DEFAULT 'X' MODIF ID dow,
  p_xlsx RADIOBUTTON GROUP gp2 MODIF ID dow.
SELECTION-SCREEN END OF BLOCK down.

INITIALIZATION.
* 初始化选择屏幕
  PERFORM frm_initial_screen.

AT SELECTION-SCREEN OUTPUT.
* 更新选择屏幕
  PERFORM frm_modify_screen.

AT SELECTION-SCREEN.
* 处理按钮响应
  PERFORM frm_ucomm_process.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
* 获取保存路径
  PERFORM frm_change_path CHANGING p_file.

START-OF-SELECTION.
* 处理执行按钮
  PERFORM frm_start_process.
*&---------------------------------------------------------------------*
*& Form frm_initial_screen
*&---------------------------------------------------------------------*
*& 初始化选择屏幕
*&---------------------------------------------------------------------*
FORM frm_initial_screen .
  DATA:
    ls_functxt      TYPE smp_dyntxt,
    lv_desktop_path TYPE string.

  t_opt    = '查询选项'(t01).
  t_down   = '下载选项'(t02).
  t_sigl   = '单表查询选择条件'(t03).
  t_multi  = '关联查询选择条件'(t04).
  t_tab    = '表名'(t05).
  t_pksize = 'Package Size'(t06).

  %_p_singl_%_app_%-text = '单表查询'(001).
  %_p_multi_%_app_%-text = 'NativeSql查询'(002).
  %_p_down_%_app_%-text  = '下载文件'(003).
  %_p_file_%_app_%-text  = '文件路径'(004).
  %_p_txt_%_app_%-text   = '.txt'(005).
  %_p_csv_%_app_%-text   = '.csv'(006).
  %_p_xlsx_%_app_%-text  = '.xlsx'(007).
  %_p_conn_%_app_%-text  = '数据库连接名'(008).

  ls_functxt-icon_id   = icon_fencing.
  ls_functxt-icon_text = '动态选择'(009).
  ls_functxt-quickinfo = '动态选择'(009).
  sscrfields-functxt_01 = ls_functxt.

  IF p_file IS INITIAL.
*   默认为当前用户桌面路径
    CALL METHOD cl_gui_frontend_services=>get_desktop_directory
      CHANGING
        desktop_directory    = lv_desktop_path
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        not_supported_by_gui = 3
        OTHERS               = 4.

    CALL METHOD cl_gui_cfw=>update_view.

    IF sy-subrc = 0.
      CONDENSE lv_desktop_path NO-GAPS.
      p_file = lv_desktop_path.
    ENDIF.
  ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_modify_screen
*&---------------------------------------------------------------------*
*& 更新选择屏幕
*&---------------------------------------------------------------------*
FORM frm_modify_screen .
  DATA:
    lt_excl TYPE STANDARD TABLE OF rsexfcode,
    ls_excl TYPE rsexfcode.

  FIELD-SYMBOLS:
    <lt_excel> TYPE tt_excl.

  LOOP AT SCREEN.
    CASE abap_on.
      WHEN p_singl.
        IF screen-group1 = 'MUL'.
          screen-active = 0.
        ENDIF.

        IF screen-name = 'P_TAB'.
          screen-required = 2.
        ENDIF.
      WHEN p_multi.
        IF screen-group1 = 'SIG'.
          screen-active = 0.
        ENDIF.
      WHEN OTHERS.
    ENDCASE.

    IF p_down = abap_off.
      IF screen-group1 = 'DOW'.
        screen-active = 0.
      ENDIF.
    ELSE.
      IF screen-name = 'P_FILE'.
        screen-required = 2.
      ENDIF.
    ENDIF.

    IF screen-name = 'P_XLSX'.
      screen-input = 0.
    ENDIF.
    MODIFY SCREEN.
  ENDLOOP.

  ASSIGN ('(RSDBRUNT)CURRENT_SCR-EXCL') TO <lt_excel>.

  IF p_singl = abap_on.
    DELETE <lt_excel> WHERE fcode = 'FC01'.

    IF go_dock IS NOT INITIAL.
      go_dock->free( ).
      CLEAR go_dock.
    ENDIF.

    IF go_text_editor IS NOT INITIAL.
      go_text_editor->free( ).
      CLEAR go_text_editor.
    ENDIF.
  ELSE.
    PERFORM insert_into_excl(rsdbrunt) USING 'FC01'.

*   创建SQL编辑器容器
    PERFORM frm_create_sql_editor.
  ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_check_input
*&---------------------------------------------------------------------*
*& 检查输入
*&---------------------------------------------------------------------*
FORM frm_check_input .
  CASE abap_on.
    WHEN p_singl.
      IF p_tab IS INITIAL.
        SET CURSOR FIELD 'P_TAB'.
        MESSAGE e055(00).
      ENDIF.
    WHEN p_multi.

    WHEN OTHERS.
  ENDCASE.

  IF p_down = abap_on.
    IF p_file IS INITIAL.
      SET CURSOR FIELD 'P_FILE'.
      MESSAGE e055(00).
    ENDIF.
  ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_ucomm_process
*&---------------------------------------------------------------------*
*& 处理按钮响应
*&---------------------------------------------------------------------*
FORM frm_ucomm_process .
  CASE sy-ucomm.
    WHEN 'ONLI'.
      PERFORM frm_check_input.
    WHEN 'FC01'.
      PERFORM frm_check_input.
      PERFORM frm_dynamic_search USING gt_field_ranges_int.
    WHEN OTHERS.
  ENDCASE.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_create_sql_editor
*&---------------------------------------------------------------------*
*& 创建SQL编辑器容器
*&---------------------------------------------------------------------*
FORM frm_create_sql_editor .
  CHECK go_dock IS INITIAL.

  CREATE OBJECT go_dock
    EXPORTING
      repid = sy-cprog
      dynnr = sy-dynnr
      ratio = 62
      side  = cl_gui_docking_container=>dock_at_bottom.

  CREATE OBJECT go_text_editor
    EXPORTING
      parent                     = go_dock
      wordwrap_mode              = '2'
      wordwrap_position          = '100'
      wordwrap_to_linebreak_mode = cl_gui_textedit=>true
    EXCEPTIONS
      error_cntl_create          = 1
      error_cntl_init            = 2
      error_cntl_link            = 3
      error_dp_create            = 4
      gui_type_not_supported     = 5.

  IMPORT gv_sql FROM MEMORY ID sy-repid.

  gv_text_newtxt = gv_sql.

  REFRESH gt_texttab.
  CALL FUNCTION 'SO_STRING_TO_TAB'
    EXPORTING
      content_str = gv_text_newtxt
    TABLES
      content_tab = gt_texttab.

  go_text_editor->set_text_as_r3table( EXPORTING table = gt_texttab ).

  CALL METHOD cl_gui_docking_container=>set_focus
    EXPORTING
      control = go_text_editor.

  CALL METHOD go_text_editor->set_readonly_mode
    EXPORTING
      readonly_mode          = 0
    EXCEPTIONS
      error_cntl_call_method = 1
      invalid_parameter      = 2
      OTHERS                 = 3.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_start_process
*&---------------------------------------------------------------------*
*& 处理执行按钮
*&---------------------------------------------------------------------*
FORM frm_start_process .
* 设置文件后缀
  PERFORM frm_set_file_extension.

  CASE abap_on.
    WHEN p_singl.
*     动态条件查询
      PERFORM frm_dynamic_search USING gt_field_ranges_int.
    WHEN p_multi.
*     从编辑器获取最新SQL
      PERFORM frm_get_sql.

*     调用ADBC方式获取数据
      PERFORM frm_call_adbc.
    WHEN OTHERS.
  ENDCASE.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_get_sql
*&---------------------------------------------------------------------*
*& 从编辑器获取最新SQL
*&---------------------------------------------------------------------*
FORM frm_get_sql .
  go_text_editor->get_text_as_r3table(
    IMPORTING
      table = gt_texttab
  ).

  CLEAR gv_sql.

  CALL FUNCTION 'SO_TAB_TO_STRING'
    IMPORTING
      content_str = gv_sql
    TABLES
      content_tab = gt_texttab.

  EXPORT gv_sql TO MEMORY ID sy-repid.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_call_adbc
*&---------------------------------------------------------------------*
*& 调用ADBC方式获取数据
*&---------------------------------------------------------------------*
FORM frm_call_adbc .
  DATA:
    lo_adbc_connect TYPE REF TO cl_sql_connection,
    lo_adbc_query   TYPE REF TO cl_sql_statement,
    lo_adbc_result  TYPE REF TO cl_sql_result_set,
    lo_metadata     TYPE REF TO data,
    ls_metadata     TYPE adbc_rs_metadata_descr,
    lt_metadata     TYPE adbc_rs_metadata_descr_tab.

  DATA:
    lo_sql_exception           TYPE REF TO cx_sql_exception,
    lo_parameter_invalid       TYPE REF TO cx_parameter_invalid,
    lo_parameter_invalid_range TYPE REF TO cx_parameter_invalid_range,
    lo_parameter_invalid_type  TYPE REF TO cx_parameter_invalid_type,
    lo_struct_creation         TYPE REF TO cx_sy_struct_creation,
    lo_table_creation          TYPE REF TO cx_sy_table_creation,
    lo_exception               TYPE REF TO data,
    lv_err_text                TYPE string,
    lv_rows_ret                TYPE i.

  DATA:
    lo_structdescr TYPE REF TO cl_abap_structdescr,
    lo_tabletype   TYPE REF TO cl_abap_tabledescr,
    lo_result_ref  TYPE REF TO data.

  DATA:
    lv_title_line   TYPE string,
    lv_body_line    TYPE string,
    lv_body_string  TYPE string,
    lv_body_xstring TYPE xstring.

  FIELD-SYMBOLS:
    <lv_field_value> TYPE any.

* 获取连接
  TRY .
      lo_adbc_connect = cl_sql_connection=>get_connection( con_name = p_conn ).
    CATCH cx_sql_exception INTO lo_sql_exception.
      lv_err_text = lo_sql_exception->get_text( ).
      WRITE lv_err_text.
      RETURN.
  ENDTRY.

* 创建连接对象
  lo_adbc_query = lo_adbc_connect->create_statement( ).

* 执行Sql
  TRY .
      lo_adbc_result = lo_adbc_query->execute_query( gv_sql ).
    CATCH cx_sql_exception INTO lo_sql_exception.
      lv_err_text = lo_sql_exception->get_text( ).
      WRITE lv_err_text.
      RETURN.
    CATCH cx_parameter_invalid INTO lo_parameter_invalid.
      lv_err_text = lo_parameter_invalid->get_text( ).
      WRITE lv_err_text.
      RETURN.
  ENDTRY.

* 获取元数据
  TRY .
      lt_metadata = lo_adbc_result->get_metadata( ).
    CATCH cx_sql_exception.
      lv_err_text = lo_sql_exception->get_text( ).
      WRITE lv_err_text.
      RETURN.
  ENDTRY.

* 获取元数据字段信息
  TRY .
      lo_metadata = lo_adbc_result->get_struct_ref( md_tab      = lt_metadata
                                                    string_only = abap_true
                                                    p_strict    = abap_false
                                                   ).
    CATCH cx_sy_struct_creation INTO lo_struct_creation.
      lv_err_text = lo_struct_creation->get_text( ).
      WRITE lv_err_text.
      RETURN.
    CATCH cx_parameter_invalid_range INTO lo_parameter_invalid_range.
      lv_err_text = lo_parameter_invalid_range->get_text( ).
      WRITE lv_err_text.
      RETURN.
  ENDTRY.

* 创建结构类型对象
  lo_structdescr ?= cl_abap_typedescr=>describe_by_data_ref( lo_metadata ).

* 创建表类型对象
  TRY .
      lo_tabletype = cl_abap_tabledescr=>create( p_line_type  = lo_structdescr
                                                 p_table_kind = cl_abap_tabledescr=>tablekind_std ).
    CATCH cx_sy_table_creation INTO lo_table_creation.
      lv_err_text = lo_table_creation->get_text( ).
      WRITE lv_err_text.
      RETURN.
  ENDTRY.

* 创建表对象引用
  CREATE DATA lo_result_ref TYPE HANDLE lo_tabletype.

* 传递sql结果至表对象引用
  TRY .
      lo_adbc_result->set_param_table( itab_ref = lo_result_ref ).
    CATCH cx_parameter_invalid INTO lo_parameter_invalid.
      lv_err_text = lo_parameter_invalid->get_text( ).
      WRITE lv_err_text.
      RETURN.
  ENDTRY.

  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
*     PERCENTAGE       = 0
      text = '查询中...'(t07).

* 获取所有数据
  TRY .
      lo_adbc_result->next_package( EXPORTING upto = 0 ).
    CATCH cx_sql_exception INTO lo_sql_exception.
      lv_err_text = lo_sql_exception->get_text( ).
      WRITE lv_err_text.
      RETURN.
    CATCH cx_parameter_invalid_type INTO lo_parameter_invalid_type.
      lv_err_text = lo_parameter_invalid_type->get_text( ).
      WRITE lv_err_text.
      RETURN.
  ENDTRY.

* 关闭连接
  lo_adbc_result->close( ).

* 赋值到动态内表
  ASSIGN lo_result_ref->* TO <gt_table>.

  IF <gt_table> IS INITIAL.
    MESSAGE '不包含符合条件数据！'(m01) TYPE 'S' DISPLAY LIKE 'E'.
    LEAVE LIST-PROCESSING.
  ENDIF.

* 设置字段控制
  LOOP AT lt_metadata INTO ls_metadata.
    gs_fieldcat-fieldname = ls_metadata-column_name.
    TRANSLATE gs_fieldcat-fieldname TO UPPER CASE.
    gs_fieldcat-scrtext_l = ls_metadata-column_name.
    APPEND gs_fieldcat TO gt_fieldcat.
    CLEAR gs_fieldcat.
  ENDLOOP.

  IF p_down = abap_on.
    IF p_xlsx <> abap_on.
*     标题行拼接换行符
      LOOP AT gt_fieldcat INTO gs_fieldcat.
        lv_title_line = lv_title_line && gv_separator && gs_fieldcat-scrtext_l.
      ENDLOOP.

      SHIFT lv_title_line.

*     标题行拼接换行符
      lv_body_string = lv_title_line && cl_abap_char_utilities=>cr_lf.

      LOOP AT <gt_table> ASSIGNING <gs_table>.
        DO.
          ASSIGN COMPONENT sy-index OF STRUCTURE <gs_table> TO <lv_field_value>.
          IF sy-subrc <> 0.
            EXIT.
          ENDIF.

*         内容行拼接分隔符
          lv_body_line = lv_body_line && gv_separator && <lv_field_value>.
        ENDDO.

        SHIFT lv_body_line.

*       内容行拼接换行符
        lv_body_line  = lv_body_line && cl_abap_char_utilities=>cr_lf.

*       拼接内容行
        lv_body_string = lv_body_string && lv_body_line.

        CLEAR lv_body_line.
      ENDLOOP.

      CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
        EXPORTING
          text     = lv_body_string
          mimetype = 'UTF-8'
        IMPORTING
          buffer   = gv_body_xstring.

      CONCATENATE cl_abap_char_utilities=>byte_order_mark_utf8
                  gv_body_xstring
             INTO gv_body_xstring IN BYTE MODE.
    ENDIF.

*   下载文件
    PERFORM frm_download_file.
  ELSE.
*   ALV展示
    PERFORM frm_display_alv.
  ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_change_path
*&---------------------------------------------------------------------*
*& 获取保存路径
*&---------------------------------------------------------------------*
*&      <-- CV_FILE_PATH   文件路径
*&---------------------------------------------------------------------*
FORM frm_change_path  CHANGING cv_file_path.
  DATA lv_path TYPE string.

  CALL METHOD cl_gui_frontend_services=>directory_browse
    CHANGING
      selected_folder      = lv_path
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.

  cv_file_path = lv_path.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  FRM_DISPLAY_ALV
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM frm_display_alv.
  DATA:
    ls_layout       TYPE lvc_s_layo,
    lt_excluding    TYPE slis_t_extab,
    lt_event        TYPE slis_t_event,
    ls_event        TYPE slis_alv_event,
    lt_event_exit   TYPE slis_t_event_exit,
    ls_event_exit   TYPE slis_event_exit,
    ls_ddval        TYPE lvc_s_drop,
    ls_grid_setting TYPE lvc_s_glay.

  FIELD-SYMBOLS:
    <ls_fieldcat> TYPE lvc_s_fcat.

* 设置布局控制
  ls_layout-zebra      = 'X'.                     "颜色间隔
  ls_layout-sel_mode   = 'D'.                     "选择模式
  ls_layout-cwidth_opt = 'X'.                     "列宽自适应
* 展示ALV
  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
    EXPORTING
      i_callback_program = sy-repid
      is_layout_lvc      = ls_layout
      it_fieldcat_lvc    = gt_fieldcat
      it_excluding       = lt_excluding
      i_grid_settings    = ls_grid_setting
*     IT_SORT_LVC        =
      i_save             = 'A'
*     IS_VARIANT         =
      it_events          = lt_event
      it_event_exit      = lt_event_exit
    TABLES
      t_outtab           = <gt_table>
    EXCEPTIONS
      program_error      = 1
      OTHERS             = 2.
  IF sy-subrc <> 0.
*   Implement suitable error handling here
  ENDIF.
ENDFORM. "FRM_DISPLAY_ALV
*&---------------------------------------------------------------------*
*& Form frm_download_file
*&---------------------------------------------------------------------*
*& 下载文件
*&---------------------------------------------------------------------*
FORM frm_download_file .
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      text = '下载中...'(t06).

  CASE abap_on.
    WHEN p_txt OR p_csv.
*     以txt格式下载文件
      PERFORM frm_download_txt.
    WHEN p_xlsx.
*     以xlsx格式下载文件
      PERFORM frm_download_xlsx.
    WHEN OTHERS.
  ENDCASE.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_DYNAMIC_SEARCH
*&---------------------------------------------------------------------*
*& 动态条件查询
*&---------------------------------------------------------------------*
*& -->  UT_FIELD_RANGES_INT        选择条件SELECT-OPTION
*&---------------------------------------------------------------------*
FORM frm_dynamic_search  USING ut_field_ranges_int TYPE rsds_trange.
  DATA:
    lv_selection_id     TYPE rsdynsel-selid,
    lt_tables_tab       TYPE STANDARD TABLE OF rsdstabs,
    ls_tables_tab       TYPE rsdstabs,
    lt_fields_tab       TYPE STANDARD TABLE OF rsdsfields,
    ls_fields_tab       TYPE rsdsfields,
    lt_fcode_tab        TYPE STANDARD TABLE OF rsdsfcode,
    ls_fcode_tab        TYPE rsdsfcode,
    ls_pfkey            TYPE rsdspfkey,
    lt_where_clauses    TYPE rsds_twhere,
    ls_where_clauses    TYPE rsds_where,
    ls_where_tab        TYPE rsdswhere,
    ls_where_cond       TYPE string,
    ls_field_ranges_int TYPE rsds_range,
    ls_range_t          TYPE rsds_frange.

  DATA:
    lt_dfies_tab TYPE STANDARD TABLE OF dfies,
    ls_dfies_tab TYPE dfies.

  CALL FUNCTION 'DDIF_NAMETAB_GET'
    EXPORTING
      tabname   = p_tab
    TABLES
      dfies_tab = lt_dfies_tab
    EXCEPTIONS
      not_found = 1
      OTHERS    = 2.

  IF sy-subrc <> 0.
*   Implement suitable error handling here
    MESSAGE ID sy-msgid
          TYPE sy-msgty
        NUMBER sy-msgno
          WITH sy-msgv1
               sy-msgv2
               sy-msgv3
               sy-msgv4.
  ENDIF.

  LOOP AT lt_dfies_tab INTO ls_dfies_tab WHERE keyflag = 'X' AND fieldname <> 'MANDT'.
    ls_fields_tab-tablename = ls_dfies_tab-tabname.
    ls_fields_tab-fieldname = ls_dfies_tab-fieldname.
    APPEND ls_fields_tab TO lt_fields_tab.
  ENDLOOP.

* 将上次查询条件追加进来
  LOOP AT ut_field_ranges_int INTO ls_field_ranges_int WHERE tablename = p_tab.
    LOOP AT ls_field_ranges_int-frange_t INTO ls_range_t.
      READ TABLE lt_fields_tab TRANSPORTING NO FIELDS
        WITH KEY tablename = ls_field_ranges_int-tablename
                 fieldname = ls_range_t-fieldname.
      IF sy-subrc <> 0.
        ls_fields_tab-tablename = ls_field_ranges_int-tablename.
        ls_fields_tab-fieldname = ls_range_t-fieldname.
        APPEND ls_fields_tab TO lt_fields_tab.
      ENDIF.
    ENDLOOP.
  ENDLOOP.

  ls_tables_tab-prim_tab = p_tab.
  APPEND ls_tables_tab TO lt_tables_tab.

  CALL FUNCTION 'FREE_SELECTIONS_INIT'
    EXPORTING
      kind                     = 'T'
      field_ranges_int         = ut_field_ranges_int
    IMPORTING
      selection_id             = lv_selection_id
    TABLES
      tables_tab               = lt_tables_tab
      fields_tab               = lt_fields_tab
    EXCEPTIONS
      fields_incomplete        = 1
      fields_no_join           = 2
      field_not_found          = 3
      no_tables                = 4
      table_not_found          = 5
      expression_not_supported = 6
      incorrect_expression     = 7
      illegal_kind             = 8
      area_not_found           = 9
      inconsistent_area        = 10
      kind_f_no_fields_left    = 11
      kind_f_no_fields         = 12
      too_many_fields          = 13
      dup_field                = 14
      field_no_type            = 15
      field_ill_type           = 16
      dup_event_field          = 17
      node_not_in_ldb          = 18
      area_no_field            = 19
      OTHERS                   = 20.

  IF sy-subrc EQ 0.
    ls_pfkey-pfkey   = sy-pfkey.
    ls_pfkey-program = sy-repid.

    CALL FUNCTION 'FREE_SELECTIONS_DIALOG'
      EXPORTING
        selection_id    = lv_selection_id
        title           = sy-title
        frame_text      = TEXT-001
        status          = 1
        no_intervals    = ''
        as_window       = ''
        as_subscreen    = ''
        tree_visible    = 'X'                                           "是否显示条件Tree
      IMPORTING
        where_clauses   = lt_where_clauses                              "返回选择条件
      TABLES
        fields_tab      = lt_fields_tab                                 "选择画面中选中字段
        fcode_tab       = lt_fcode_tab
      EXCEPTIONS
        internal_error  = 1
        no_action       = 2
        selid_not_found = 3
        illegal_status  = 4
        OTHERS          = 5.
    IF sy-subrc EQ 0.
      READ TABLE lt_where_clauses INTO ls_where_clauses INDEX 1.

      LOOP AT ls_where_clauses-where_tab INTO ls_where_tab.
        ls_where_cond = ls_where_cond && ` ` && ls_where_tab-line.
      ENDLOOP.

*     查询取数:下载or展示
      PERFORM frm_get_data USING p_tab ls_where_cond.

      CALL FUNCTION 'FREE_SELECTIONS_WHERE_2_RANGE'
        EXPORTING
          where_clauses            = lt_where_clauses
        IMPORTING
          field_ranges             = gt_field_ranges_int
        EXCEPTIONS
          expression_not_supported = 1
          incorrect_expression     = 2
          OTHERS                   = 3.

*     动态查询
      PERFORM frm_dynamic_search USING gt_field_ranges_int.
    ENDIF.
  ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_build_tab
*&---------------------------------------------------------------------*
*& 构建动态内表
*&---------------------------------------------------------------------*
FORM frm_build_tab .
  DATA:
    lo_tab_ref  TYPE REF TO data,
    lo_line_ref TYPE REF TO data.
  CREATE DATA lo_tab_ref TYPE STANDARD TABLE OF (p_tab).
  CREATE DATA lo_line_ref TYPE (p_tab).
  ASSIGN lo_tab_ref->* TO <gt_table>.
  ASSIGN lo_line_ref->* TO <gs_table>.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_get_fieldcat
*&---------------------------------------------------------------------*
*& 获取Fieldcat
*&---------------------------------------------------------------------*
FORM frm_get_fieldcat .
  CLEAR gt_fieldcat.
* 设置字段控制
  CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
    EXPORTING
      i_structure_name       = p_tab
    CHANGING
      ct_fieldcat            = gt_fieldcat
    EXCEPTIONS
      inconsistent_interface = 1
      program_error          = 2
      OTHERS                 = 3.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_download_txt
*&---------------------------------------------------------------------*
*& 以txt格式下载文件
*&---------------------------------------------------------------------*
FORM frm_download_txt .
  DATA:
    lv_file_xstring TYPE xstring,
    lv_len          TYPE i,
    lt_data_tab     TYPE STANDARD TABLE OF w3mime,
    lv_filename     TYPE string.

  LOOP AT gt_zip_list INTO gv_zip_name.
    OPEN DATASET gv_zip_name FOR INPUT IN BINARY MODE.
    IF sy-subrc = 0.
      READ DATASET gv_zip_name INTO lv_file_xstring LENGTH lv_len.
      CLOSE DATASET gv_zip_name.

      lv_filename = p_file && '/' && gv_zip_name.

      CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
        EXPORTING
          buffer        = lv_file_xstring
        IMPORTING
          output_length = lv_len
        TABLES
          binary_tab    = lt_data_tab.

      CALL FUNCTION 'GUI_DOWNLOAD'
        EXPORTING
          bin_filesize            = lv_len
          filename                = lv_filename
          filetype                = 'BIN'
*         WRITE_FIELD_SEPARATOR   = ' '
          confirm_overwrite       = 'X'
          codepage                = '8404'
*         REPLACEMENT             = '#'
*         WRITE_BOM               = ' '
        TABLES
          data_tab                = lt_data_tab
        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 ID sy-msgid
              TYPE sy-msgty
            NUMBER sy-msgno
              WITH sy-msgv1
                   sy-msgv2
                   sy-msgv3
                   sy-msgv4.
      ELSE.
        MESSAGE lv_filename TYPE 'S'.
      ENDIF.

      DELETE DATASET gv_zip_name.
    ENDIF.

    CLEAR:
      lv_file_xstring,
      lv_len,
      lt_data_tab.
  ENDLOOP.

  CLEAR gt_zip_list.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_download_xlsx
*&---------------------------------------------------------------------*
*& 以xlsx格式下载文件
*&---------------------------------------------------------------------*
FORM frm_download_xlsx .
* OLE使用相关参数定义
  DATA:
    myexcel      TYPE ole2_object,
    mysheet      TYPE ole2_object,
    mysheetname  TYPE ole2_object,
    mycell       TYPE ole2_object,
    mycell_begin TYPE ole2_object,
    mycell_end   TYPE ole2_object,
    myworkbook   TYPE ole2_object,
    myrange      TYPE ole2_object,
    myrange_all  TYPE ole2_object,
    myborders    TYPE ole2_object,
    myinterior   TYPE ole2_object.

  DATA:
    lv_filename    TYPE string,
    lv_title_line1 TYPE char8000_d,
    lv_title_line2 TYPE char8000_d,
    lv_body_line   TYPE char8000_d,
    lt_body        TYPE STANDARD TABLE OF char8000_d,
    lv_body_string TYPE string,
    lv_total_col   TYPE i,
    lv_total_row   TYPE i,
    lv_rc          TYPE i,
    lv_group_size  TYPE i,
    lv_count       TYPE i,
    lv_row_index   TYPE i,
    lv_past_times  TYPE i.

  CONSTANTS:
    lc_max_siz TYPE i VALUE 100000.

  FIELD-SYMBOLS:
    <lv_field_value> TYPE any.

  lv_filename = p_file && '\' && gv_file_name.

  lv_total_col = lines( gt_fieldcat ).

* 创建excel对象
  CREATE OBJECT myexcel 'excel.application'.

* 设置后台执行  0后台 1前台
  SET PROPERTY OF myexcel 'visible' = 0.

* 创建工作簿对象
  CALL METHOD OF
    myexcel
      'Workbooks' = myworkbook.

* 添加空白工作簿
  CALL METHOD OF
    myworkbook
    'Add'.

  FREE OBJECT myworkbook.

* 创建Sheet
  CALL METHOD OF
      myexcel
      'Worksheets' = mysheet
    EXPORTING
      #1           = 1.

* 激活当前Sheet
  CALL METHOD OF
    mysheet
    'Activate'.

  LOOP AT gt_fieldcat INTO gs_fieldcat.
*   标题行：描述
    lv_title_line1 = lv_title_line1 && gv_separator && gs_fieldcat-scrtext_l.
*   标题行：字段名
    lv_title_line2 = lv_title_line2 && gv_separator && gs_fieldcat-fieldname.
  ENDLOOP.

  SHIFT lv_title_line1.
  APPEND lv_title_line1 TO lt_body.

  ADD 1 TO lv_group_size.
  ADD 1 TO lv_count.

  SHIFT lv_title_line2.
  APPEND lv_title_line2 TO lt_body.

  ADD 1 TO lv_group_size.
  ADD 1 TO lv_count.

  lv_total_row = lines( <gt_table> ) + 2.

  LOOP AT <gt_table> ASSIGNING <gs_table>.
    DO.
      ASSIGN COMPONENT sy-index OF STRUCTURE <gs_table> TO <lv_field_value>.
      IF sy-subrc <> 0.
        EXIT.
      ENDIF.

*     内容行拼接分隔符
      lv_body_line = lv_body_line && gv_separator && <lv_field_value>.
    ENDDO.

    SHIFT lv_body_line.
    APPEND lv_body_line TO lt_body.

    CLEAR lv_body_line.

    ADD 1 TO lv_group_size.
    ADD 1 TO lv_count.

*   按照指定数据分组大小依次分组粘贴
    IF lv_group_size >= lc_max_siz OR lv_count = lv_total_row.
*     将内容复制进粘贴板
      CALL METHOD cl_gui_frontend_services=>clipboard_export
        IMPORTING
          data                 = lt_body
        CHANGING
          rc                   = lv_rc
        EXCEPTIONS
          cntl_error           = 1
          error_no_gui         = 2
          not_supported_by_gui = 3
          OTHERS               = 4.

      ADD 1 TO lv_past_times.
      lv_row_index = ( lv_past_times * lc_max_siz ) - lc_max_siz + 1.

*     选中单元格
      CALL METHOD OF
          mysheet
          'Cells' = mycell
        EXPORTING
          #1      = lv_row_index
          #2      = 1.

*     选中单元格
      CALL METHOD OF
        mycell
        'Select'.

*     内容粘贴
      CALL METHOD OF
        mycell
        'PasteSpecial'.

      CLEAR:
        lt_body,
        lv_group_size.
    ENDIF.
  ENDLOOP.

* 全选
  CALL METHOD OF
    mysheet
      'Columns' = myrange_all.

* 自动适配列宽
  CALL METHOD OF
    myrange_all
    'AutoFit'.

* 范围起始单元格
  CALL METHOD OF
      mysheet
      'Cells' = mycell_begin
    EXPORTING
      #1      = 1
      #2      = 1.

* 范围截止单元格
  CALL METHOD OF
      mysheet
      'Cells' = mycell_end
    EXPORTING
      #1      = 2
      #2      = lv_total_col.

* 获取范围对象
  CALL METHOD OF
      mysheet
      'Range' = myrange
    EXPORTING
      #1      = mycell_begin
      #2      = mycell_end.

* 选中范围
  CALL METHOD OF
    myrange
    'Select'.

* 变更颜色
  GET PROPERTY OF myrange 'Interior' = myinterior.
  SET PROPERTY OF myinterior 'ColorIndex' = 15.

* 设置框线
  GET PROPERTY OF myrange 'Borders' = myborders.
  SET PROPERTY OF myborders 'Weight' = 2.

* 定位到第三行第一列
  CALL METHOD OF
      mysheet
      'Cells' = mycell
    EXPORTING
      #1      = 3
      #2      = 1.

* 选中单元格
  CALL METHOD OF
    mycell
    'Select'.

* 切换至首页
  CALL METHOD OF
      myexcel
      'Worksheets' = mysheet
    EXPORTING
      #1           = 1.

* 激活当前Sheet
  CALL METHOD OF
    mysheet
    'Activate'.

  FREE OBJECT mysheet.

* 获取活动的工作簿
  GET PROPERTY OF myexcel 'ActiveWorkbook' = myworkbook.

* 另存为
  CALL METHOD OF
    myworkbook
    'SaveAs'
    EXPORTING
      #1 = lv_filename                                                  "保存路径
      #2 = 51.                                                          "XlFileFormat：默认为*.xls  51为*.xlsx

* 保存并关闭
  CALL METHOD OF
    myworkbook
    'Close'
    EXPORTING
      #1 = 'True'.                                                      "覆盖时弹窗确认

  FREE OBJECT:
    myworkbook.

* 退出EXCEL
  CALL METHOD OF
    myexcel
    'Quit'.

* 释放对象
  FREE OBJECT mycell_begin.
  FREE OBJECT mycell_end.
  FREE OBJECT mycell.
  FREE OBJECT myrange.
  FREE OBJECT myinterior.
  FREE OBJECT myborders.
  FREE OBJECT mysheet.
  FREE OBJECT myexcel.

* 下载成功！
  MESSAGE lv_filename TYPE 'S'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_set_file_extension
*&---------------------------------------------------------------------*
*& 设置文件后缀
*&---------------------------------------------------------------------*
FORM frm_set_file_extension .
  IF p_down = abap_on.
    CASE abap_on.
      WHEN p_txt.
        gv_file_extension = '.txt'.
        gv_separator      = cl_abap_char_utilities=>horizontal_tab.
      WHEN p_csv.
        gv_file_extension = '.csv'.
        gv_separator      = ','.
      WHEN p_xlsx.
        gv_file_extension = '.xlsx'.
        gv_separator      = cl_abap_char_utilities=>horizontal_tab.
      WHEN OTHERS.
    ENDCASE.
  ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_get_data
*&---------------------------------------------------------------------*
*& 查询取数
*&---------------------------------------------------------------------*
*& -->  UV_TAB               数据库表名
*& -->  UV_WHERE_COND        Where条件
*&---------------------------------------------------------------------*
FORM frm_get_data USING uv_tab        TYPE dd02l-tabname
                        uv_where_cond TYPE string.
  DATA:
    lv_title_line   TYPE string,
    lv_body_line    TYPE string,
    lv_body_string  TYPE string,
    lv_body_xstring TYPE xstring,
    lv_cursor       TYPE cursor,
    lo_zip          TYPE REF TO cl_abap_zip.

  FIELD-SYMBOLS:
    <lv_field_value> TYPE any.

  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      text = '查询中...'(t07).

* 构建全局动态内表
  PERFORM frm_build_tab.

* 获取Fieldcat
  PERFORM frm_get_fieldcat.

  IF p_down = abap_on.
*   标题行拼接换行符
    LOOP AT gt_fieldcat INTO gs_fieldcat.
      lv_title_line = lv_title_line && gv_separator && gs_fieldcat-scrtext_l.
    ENDLOOP.

    SHIFT lv_title_line.

    OPEN CURSOR WITH HOLD lv_cursor FOR SELECT * FROM (uv_tab) WHERE (uv_where_cond).

    DO.
      FETCH NEXT CURSOR lv_cursor INTO TABLE <gt_table> PACKAGE SIZE p_pksize.
      IF sy-subrc <> 0.
        EXIT.
      ENDIF.

      gv_file_name = uv_tab && '_' && sy-datlo && sy-timlo && '_' && sy-index && gv_file_extension.
      gv_zip_name  = uv_tab && '_' && sy-datlo && sy-timlo && '_' && sy-index && '.ZIP'.

      OPEN DATASET gv_zip_name FOR OUTPUT IN BINARY MODE.

*     标题行拼接换行符
      lv_body_string = lv_title_line && cl_abap_char_utilities=>cr_lf.

      LOOP AT <gt_table> ASSIGNING <gs_table>.
        DO.
          ASSIGN COMPONENT sy-index OF STRUCTURE <gs_table> TO <lv_field_value>.
          IF sy-subrc <> 0.
            EXIT.
          ENDIF.

*         内容行拼接分隔符
          lv_body_line = lv_body_line && gv_separator && <lv_field_value>.
        ENDDO.

        SHIFT lv_body_line.

*       内容行拼接换行符
        lv_body_line  = lv_body_line && cl_abap_char_utilities=>cr_lf.

*       拼接内容行
        lv_body_string = lv_body_string && lv_body_line.

        CLEAR lv_body_line.
      ENDLOOP.

      CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
        EXPORTING
          text     = lv_body_string
          mimetype = 'UTF-8'
        IMPORTING
          buffer   = gv_body_xstring.

      CONCATENATE cl_abap_char_utilities=>byte_order_mark_utf8
                  gv_body_xstring
             INTO gv_body_xstring IN BYTE MODE.

      CREATE OBJECT lo_zip.

      lo_zip->add( name    = gv_file_name
                   content = gv_body_xstring ).

      gv_zip_xstring = lo_zip->save( ).

      TRANSFER gv_zip_xstring TO gv_zip_name.
      CLOSE DATASET gv_zip_name.
      APPEND gv_zip_name TO gt_zip_list.

      CLEAR:
        gv_body_xstring,
        gv_zip_name,
        gv_file_name,
        lv_body_line.

      FREE:
        lo_zip.
    ENDDO.

    CLOSE CURSOR lv_cursor.

*   下载文件
    PERFORM frm_download_file.
  ELSE.
    SELECT *
       INTO TABLE <gt_table>
       FROM (uv_tab)
      WHERE (uv_where_cond).

    IF <gt_table> IS INITIAL.
      MESSAGE '不包含符合条件数据！'(m01) TYPE 'S' DISPLAY LIKE 'E'.
      LEAVE LIST-PROCESSING.
    ENDIF.

*   ALV输出
    PERFORM frm_display_alv.
  ENDIF.
ENDFORM.