导语:最近有个需求,是需要一段时间把自定义表的数据拉出来,核对一下,涉及的表呢比较多,所以写了一个通用的批量程序,代码放在了最后,直接粘贴到系统就可以使用,拿走的小伙伴点个赞哈,后面会写一个改进版可以添加查询条件的。
一、文件夹名称
??系统会先根据文件夹名称创建一个文件夹在本地,然后将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.
CONCATENATE l_file '\' ls_folder-dire INTO l_file.
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
|