*&---------------------------------------------------------------------*
*& Report ZFIR252
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zexport_table.
TABLES:sscrfields,ztexport.
DATA:ok_code LIKE sy-ucomm.
DATA:stbl TYPE lvc_s_stbl.
DATA: gt_field TYPE lvc_t_fcat.
DATA: gt_fieldISP TYPE lvc_t_fcat.
DATA: gt_field2 TYPE lvc_t_fcat.
DATA: gs_field TYPE lvc_s_fcat.
DATA: GS_LAYOUT TYPE LVC_S_LAYO.
DATA: GS_VARIANT TYPE DISVARIANT.
FIELD-SYMBOLS:<itab> TYPE STANDARD TABLE,
<wa>,<ft> TYPE lvc_t_styl,
<fv>,<f1>,<f2>,<f3>.
FIELD-SYMBOLS:<itab2> TYPE STANDARD TABLE,
<wa2>,<ft2> TYPE lvc_t_styl,
<fv2>,<f12>,<f22>,<f32>.
DATA: dy_table TYPE REF TO data, ""取值的动态内表
dy_line TYPE REF TO data.
DATA: dy_table2 TYPE REF TO data, ""输出的动态内表，每个字段暂定char128，
dy_line2 TYPE REF TO data.
FIELD-SYMBOLS: <dyn_table> TYPE table,
<gt_table> TYPE table,
<dyn_wa> TYPE any.
FIELD-SYMBOLS :<fs_tab> TYPE ANY TABLE .
DATA gv_selea TYPE string .""select 哪些字段
DATA gv_from TYPE string .""FROM 哪个表怎么连表
DATA gv_where TYPE string .""WHERE 哪些条件
DATA gt_exp TYPE TABLE OF ztexport .
SELECTION-SCREEN BEGIN OF BLOCK a1 WITH FRAME TITLE TEXT-001.
PARAMETERS : p_versi TYPE zeversi .
PARAMETERS : P_DISP RADIOBUTTON GROUP r2 DEFAULT 'X'USER-COMMAND uc .
PARAMETERS : P_EXPO RADIOBUTTON GROUP r2 .
PARAMETERS : p_file LIKE rlgrap-filename DEFAULT '/gacmotor/sap/' MODIF ID M1. "/gacmotor/sap "AL11中的文件目录都可以导出，需要与basis联系询问具体地址
PARAMETERS : p_numb TYPE sy-index DEFAULT '1000000' MODIF ID M1.
PARAMETERS : p_serv RADIOBUTTON GROUP r1 DEFAULT 'X' MODIF ID M1.
PARAMETERS : p_loca RADIOBUTTON GROUP r1 MODIF ID M1.
SELECTION-SCREEN END OF BLOCK a1.
"SELECTION-SCREEN FUNCTION KEY 1. "
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
PERFORM frm_f4_p_file .
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_versi.
PERFORM frm_f4_p_versi .
*************************************************************************
** INITIALIZATION
*************************************************************************
"该事件在屏幕未显示之前执行，对程序设置值及屏幕元素进行初始化赋值。
INITIALIZATION.
stbl-row = 'X'.
stbl-col = 'X'.
sscrfields-functxt_01 = '维护审计导出字段配置表'.
"假必输
LOOP AT SCREEN.
IF screen-name ='P_VERSI'.
screen-required ='2'.
ENDIF.
MODIFY SCREEN.
ENDLOOP.
*************************************************************************
** AT SELECTION SCREEN
*************************************************************************
AT SELECTION-SCREEN.
CASE sscrfields-ucomm. "处理按钮命令
WHEN'FC01'.
CALL FUNCTION 'VIEW_MAINTENANCE_CALL'
EXPORTING
action = 'U'
view_name = 'ZTEXPORT'.
WHEN OTHERS.
ENDCASE.
*************************************************************************
** AT SELECTION SCREEN OUTPUT
*************************************************************************
AT SELECTION-SCREEN OUTPUT.
LOOP AT SCREEN.
CASE screen-group1.
WHEN 'M1' .
IF P_DISP = 'X'.
screen-active = '0'.
ELSE.
screen-active = '1'.
ENDIF.
ENDCASE .
IF screen-name = 'P_VERSI'.
screen-required = '2'.
ENDIF.
MODIFY SCREEN.
ENDLOOP.
*************************************************************************
* EVENT START OF SELECTION
*************************************************************************
START-OF-SELECTION.
PERFORM frm_check_exp_data .
PERFORM bulid_select .
IF P_EXPO = 'X'.
PERFORM export_data_to_ser .
PERFORM export_data_to_loc .
ELSEIF P_DISP = 'X' .
PERFORM FRM_DISPLAY_ALV .
ENDIF.
*************************************************************************
**EVENT END-OF SELECTION
*************************************************************************
END-OF-SELECTION.
FORM frm_check_exp_data.
"取配置表数据
SELECT * FROM ztexport INTO TABLE @gt_exp WHERE versi = @p_versi .
IF gt_exp IS INITIAL.
MESSAGE '请先维护表ZTEXPORT' TYPE 'S'DISPLAY LIKE 'E'.
ENDIF .
CHECK gt_exp IS NOT INITIAL ."排序，删除重复维护的字段,活动取值不能有相同的字段
SORT gt_exp BY ref_field ref_table as_table as_field xh .
DELETE ADJACENT DUPLICATES FROM gt_exp COMPARING ref_field as_field.
SORT gt_exp BY xh ref_table ref_field . "删除后按照顺序排好
ENDFORM .
FORM bulid_select .
CLEAR : gv_selea,gv_where,gv_from,gt_field.
LOOP AT gt_exp INTO DATA(ls_exp).
"构造导出至服务器的动态内表的表字段
CLEAR :gs_field .
IF ls_exp-as_field IS NOT INITIAL.
gs_field-fieldname = ls_exp-as_field .
ELSE .
gs_field-fieldname = ls_exp-ref_field .
ENDIF.
gs_field-ref_table = ls_exp-ref_table .
gs_field-ref_field = ls_exp-ref_field .
APPEND gs_field TO gt_field .
"构造动态取值的select字段
IF ls_exp-as_table IS NOT INITIAL.
ls_exp-ref_table = ls_exp-as_table .
ENDIF.
IF gv_selea IS INITIAL.
gv_selea = gv_selea && ls_exp-ref_table &&'~'&& ls_exp-ref_field .
ELSE .
gv_selea = gv_selea &&','&& ls_exp-ref_table &&'~'&& ls_exp-ref_field .
ENDIF.
IF ls_exp-as_field IS NOT INITIAL.
gv_selea = gv_selea &&` AS `&& ls_exp-as_field .
ENDIF.
ENDLOOP ."构造限制条件1代表 限制单值等于某个数据，2代表限制范围
SELECT * FROM ztexport INTO TABLE @DATA(gt_expw) WHERE versi = @p_versi .
LOOP AT gt_expw INTO ls_exp WHERE where_typ IS NOT INITIAL .
IF ls_exp-where_typ = '1'.
IF gv_where IS INITIAL.
CONCATENATE gv_where ls_exp-ref_table '~' ls_exp-ref_field `= '` ls_exp-where_low `'` INTO gv_where .
ELSE .
CONCATENATE gv_where ` AND ` ls_exp-ref_table '~' ls_exp-ref_field `= '` ls_exp-where_low `'` INTO gv_where .
ENDIF.
ELSEIF ls_exp-where_typ = '2'.
IF gv_where IS INITIAL.
CONCATENATE gv_where ls_exp-ref_table '~' ls_exp-ref_field ` BETWEEN '` ls_exp-where_low `' AND '` ls_exp-where_high `'` INTO gv_where .
ELSE .
CONCATENATE gv_where ` AND ` ls_exp-ref_table '~' ls_exp-ref_field ` BETWEEN '` ls_exp-where_low `' AND '` ls_exp-where_high `'` INTO gv_where .
ENDIF.
ENDIF.
ENDLOOP.
"构造连表数据
DATA lv_tabix TYPE sy-index .
SELECT DISTINCT ref_table,iffrom FROM ztexport INTO TABLE @DATA(lt_reftab) WHERE versi = @p_versi.
SORT lt_reftab BY iffrom .
LOOP AT lt_reftab INTO DATA(ls_reftab).
"FROM 哪个表
IF ls_reftab-iffrom = 0.
gv_from = ls_reftab-ref_table .
IF ls_exp-as_table IS NOT INITIAL.
gv_selea = gv_selea && ` AS ` && ls_exp-as_table .
ENDIF.
CONTINUE .
ENDIF.
"连接什么表
LOOP AT gt_expw INTO ls_exp WHERE ref_table = ls_reftab-ref_table.
IF ls_exp-join_table IS INITIAL .
CONTINUE .
ENDIF.
lv_tabix = lv_tabix + 1.
IF lv_tabix =1.
gv_from = gv_from &&``&& ls_exp-connet &&``&& ls_exp-ref_table &&` ON `.
ENDIF.
IF lv_tabix >=2.
gv_from = gv_from &&` AND `.
ENDIF.
gv_from = gv_from &&``&& ls_exp-ref_table &&'~'&& ls_exp-ref_field &&`=`&& ls_exp-join_table &&'~'&& ls_exp-join_field .
ENDLOOP.
CLEAR : lv_tabix .
ENDLOOP.
"调用方法生成动态内表存储取出的数据
CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
it_fieldcatalog = gt_field
IMPORTING
ep_table = dy_table.
ASSIGN dy_table->* TO <itab>. "将指针分配给动态内表
CREATE DATA dy_line LIKE LINE OF <itab>.
ASSIGN dy_line->* TO <wa>. "将指针分配给动态内表行
"动态取出数据
IF gv_selea IS NOT INITIAL.
SELECT (gv_selea)
FROM (gv_from)
WHERE (gv_where)
INTO TABLE @<itab>.
ENDIF.
IF <itab> IS INITIAL.
MESSAGE '未找到数据' TYPE 'S'DISPLAY LIKE 'E'.
ENDIF.
ENDFORM .
FORM export_data_to_ser .
CHECK p_serv ='X'.
DATA lv_lenge TYPE i .
DATA lv_str TYPE string ."用于字段转换位string类型
DATA line TYPE string.""行数据
DATA line1 TYPE string.""第一行表头
DATA lines TYPE string.""表数据
DATA ld_xstring TYPE xstring.
DATA ld_string TYPE string.
DATA wf_msg TYPE string.
TYPES: BEGIN OF ty_fieldname,
name TYPE char20,
END OF ty_fieldname.
DATA: lt_fieldname TYPE TABLE OF ty_fieldname WITH HEADER LINE.
FIELD-SYMBOLS: <lt_table> TYPE table.
CHECK <itab> IS NOT INITIAL .
DATA: lv_file TYPE rlgrap-filename.
DATA: lv_time TYPE n LENGTH 4.
DATA: p_filename TYPE string .
DATA: lv_assgin TYPE c LENGTH 30.
DATA: lv_index TYPE sy-index .
SELECT * FROM ztexport INTO TABLE @DATA(lt_expb).
SORT lt_expb BY xh .
DELETE ADJACENT DUPLICATES FROM lt_expb COMPARING xh .""取表头描述
SELECT tabname, fieldname, ddtext ,xh FROM dd03m AS a
INNER JOIN @lt_expb AS b ON tabname = b~ref_table AND fieldname = b~ref_field AND ddlanguage ='1'
INTO TABLE @DATA(lt_03m).
SORT lt_03m BY xh .
DELETE ADJACENT DUPLICATES FROM lt_03m COMPARING xh .
LOOP AT lt_03m INTO DATA(ls_03m).
CONCATENATE line1 ls_03m-ddtext ',' INTO line1 ."每个数据后面加个逗号，excel就会换单元格
ENDLOOP.
" APPEND <WA2> TO <ITAB2>.""函数自带表头传入参数lt_fieldname就会有表头
DATA lv_spaece TYPE string .
lv_spaece =cl_abap_char_utilities=>get_simple_spaces_for_cur_cp()."返回一个带有空白字符的字符串
DO . ""
lv_time = sy-index .
CONCATENATE p_file sy-datum '-' sy-uzeit lv_time '.CSV' INTO lv_file. "服务器保存的目录和文件名
CONCATENATE lines line1 INTO lines . "将表头添加进表
DO p_numb TIMES. "按照凭证上的次数生成文件
UNASSIGN <wa> .
lv_index = lv_index + 1 .
READ TABLE <itab> ASSIGNING <wa> INDEX lv_index .
IF <wa> IS ASSIGNED .
IF p_serv = 'X'. "构造导出到服务器的数据
LOOP AT lt_expb INTO DATA(ls_exp).
CONCATENATE '<WA>-' ls_exp-ref_field INTO lv_assgin .
ASSIGN (lv_assgin) TO FIELD-SYMBOL(<fs_value>).
IF <fs_value> IS ASSIGNED.
lv_str =<fs_value>."可能有金额字段，需要转化位字符串
IF ls_exp-del_special = 'X'. "
PERFORM frm_del_str CHANGING lv_str. "" 去掉特殊字符
CONCATENATE line lv_str ',' INTO line ."数字加个单分号会当作文本展示每个数据后面加个逗号，excel就会换单元格
ELSE .
CONCATENATE line lv_str ',' INTO line . "每个数据后面加个逗号，excel就会换单元格
ENDIF.
ENDIF.
ENDLOOP.
" UNASSIGN <WA> .
CONCATENATE lines cl_abap_char_utilities=>cr_lf(1) line INTO lines. "将换行符和这行数据加入表数据中
CLEAR line .
ELSEIF p_loca ='X'.
MOVE-CORRESPONDING <wa> TO <wa2>.
APPEND <wa2> TO <itab2>.
UNASSIGN: <wa>.
CLEAR :<wa2>.
ENDIF.
ELSE .
EXIT .
ENDIF.
ENDDO.
"每循环一次导出一次数据
IF p_serv = 'X'.
CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
EXPORTING
text = lines
encoding = '8404'
IMPORTING
buffer = ld_xstring
EXCEPTIONS
failed = 1
OTHERS = 2.
" DATA(utf8)=cl_abap_codepage=>convert_to(LINES). "如果要使用AL11直接下载后台跑出的文件到本地用这个方法转码，而不是上面那个函数
OPEN DATASET lv_file FOR OUTPUT IN BINARY MODE ."TEXT MODE encoding UTF-8
" OPEN DATASET LV_FILE FOR OUTPUT IN TEXT MODE ENCODING UTF-8 WITH BYTE-ORDER MARK."TEXT MODE encoding UTF-8
IF sy-subrc =0.
TRANSFER ld_xstring TO lv_file.
" TRANSFER LD_XSTRING TO LV_FILE.
CLOSE DATASET lv_file.
MESSAGE '已成功导出至服务器' TYPE 'S' .
ENDIF .
CLEAR : line,lines .
ENDIF.
IF <wa> IS NOT ASSIGNED .
EXIT .
ENDIF.
ENDDO.
CLEAR : lv_index .
ENDFORM .
FORM export_data_to_loc.
CHECK p_loca = 'X' .
DATA lv_lenge TYPE i .
DATA lv_str TYPE string . "用于字段转换位string类型
DATA line TYPE string.""行数据
DATA line1 TYPE string.""第一行表头
DATA lines TYPE string.""表数据
DATA ld_xstring TYPE xstring.
DATA ld_string TYPE string.
DATA wf_msg TYPE string.
CHECK <itab> IS NOT INITIAL .
DATA: lv_file TYPE rlgrap-filename.
DATA: lv_time TYPE n LENGTH 4.
DATA: p_filename TYPE string .
DATA: lv_assgin TYPE c LENGTH 30.
DATA: lv_index TYPE sy-index .
TYPES: BEGIN OF ty_fieldname,
name TYPE char20,
END OF ty_fieldname.
DATA: lt_fieldname TYPE TABLE OF ty_fieldname WITH HEADER LINE.
FIELD-SYMBOLS: <lt_table> TYPE table.
LOOP AT gt_exp INTO DATA(ls_exp).
"构造导出至本地的动态内表的表字段 暂时用长度128
CLEAR :gs_field .
IF ls_exp-as_field IS NOT INITIAL.
gs_field-fieldname = ls_exp-as_field .
ELSE .
gs_field-fieldname = ls_exp-ref_field .
ENDIF.
gs_field-inttype = 'C' .
gs_field-intlen = '128' .
APPEND gs_field TO gt_field2 .
ENDLOOP.
CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
it_fieldcatalog = gt_field2
IMPORTING
ep_table = dy_table2.
ASSIGN dy_table2->* TO <itab2>. "将指针分配给动态内表
CREATE DATA dy_line LIKE LINE OF <itab2>.
ASSIGN dy_line->* TO <wa2>. "将指针分配给动态内表行
""取表头描述
SELECT * FROM ztexport INTO TABLE @DATA(lt_expb).
SORT lt_expb BY xh .
DELETE ADJACENT DUPLICATES FROM lt_expb COMPARING xh .
SELECT tabname, fieldname, ddtext ,xh FROM dd03m AS a
INNER JOIN @lt_expb AS b ON tabname = b~ref_table AND fieldname = b~ref_field AND ddlanguage ='1'
INTO TABLE @DATA(lt_03m).
SORT lt_03m BY xh .
DELETE ADJACENT DUPLICATES FROM lt_03m COMPARING xh .
LOOP AT lt_03m INTO DATA(ls_03m).
CONCATENATE '<WA2>-' ls_03m-fieldname INTO lv_assgin .
ASSIGN (lv_assgin) TO FIELD-SYMBOL(<fs_value>).
IF <fs_value> IS ASSIGNED .<fs_value>= ls_03m-ddtext .
ENDIF.
UNASSIGN <fs_value>.
lt_fieldname-name = ls_03m-ddtext. "EXCEL表头 LOOP p_table的组件，并依次将字段中文描述赋值给lt_fieldname
APPEND lt_fieldname.
ENDLOOP.
DO . ""
lv_time = sy-index .
DO p_numb TIMES. "按照凭证上的次数生成文件
UNASSIGN <wa> .
lv_index = lv_index + 1 .
READ TABLE <itab> ASSIGNING <wa> INDEX lv_index .
IF <wa> IS ASSIGNED .
MOVE-CORRESPONDING <wa> TO <wa2> .
APPEND <wa2> TO <itab2> .
UNASSIGN: <wa> .
CLEAR :<wa2> .
ELSE .
EXIT .
ENDIF.
ENDDO.
CONCATENATE p_file sy-datum '-' sy-uzeit lv_time '.XLS' INTO lv_file. "服务器保存的目录和文件名
p_filename = lv_file .
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = p_filename
filetype ='DAT'"ASC格式 1000- 不会显示为 -1000 DBF格式 字符前空格 前导0不会显示
codepage = '8404' "四位字符集代码 可通过表TCP00A，查询对应字符集代码
TABLES
data_tab =<itab2>
fieldnames = lt_fieldname.
.
MESSAGE '导出成功' TYPE 'S'.
CLEAR: <itab2>.
IF <wa> IS NOT ASSIGNED .
EXIT .
ENDIF.
ENDDO.
CLEAR : lv_index .
ENDFORM .
FORM frm_f4_p_file .
DATA lv_name TYPE c LENGTH 30.
lv_name = sy-datum && sy-uzeit .
CALL FUNCTION 'WS_FILENAME_GET'"用于选择本地文件，获取本地文件的路径
EXPORTING
def_filename = lv_name "默认的文件名称
* DEF_PATH =' '"默认的文件地址
* MASK = ',EXCEL.XLS,*.XLSX,*.XLS,TEXT.TXT,*.TXT.'
mode = 'O' "S 保存 O 打开
title ='选择文件'"窗口的显示名称
IMPORTING "传出给选择屏幕上的文本框
filename = p_file
* RC =
EXCEPTIONS
inv_winsys =1
no_batch =2
selection_cancel =3
selection_error =4
OTHERS =5.
IF sy-subrc <>0.
* Implement suitable error handling here
ENDIF.
ENDFORM .
FORM frm_f4_p_versi .
DATA: gt_return TYPE TABLE OF ddshretval,
gs_return TYPE ddshretval.
DATA: gt_f4 TYPE TABLE OF ztexport,
gs_f4 TYPE ztexport,
gt_f41 TYPE TABLE OF ztexport.
SELECT *
FROM ztexport
INTO TABLE @gt_f4.
DELETE ADJACENT DUPLICATES FROM gt_f4 COMPARING VERSI .
CALL FUNCTION 'F4IF_INT_TABLE_VALUE_REQUEST'
EXPORTING
retfield ='VERSI'
value_org ='S'
TABLES
value_tab = gt_f4
return_tab = gt_return
EXCEPTIONS
parameter_error =1
no_values_found =2
OTHERS =3.
IF sy-subrc =0.
READ TABLE gt_return INTO gs_return INDEX 1.
p_versi = gs_return-fieldval.
ELSE.
ENDIF.
ENDFORM .
FORM frm_del_str CHANGING p_str TYPE string.
REPLACE ALL OCCURRENCES OF ',' IN p_str WITH ``.
REPLACE ALL OCCURRENCES OF '#' IN p_str WITH ``.
REPLACE ALL OCCURRENCES OF '#' IN p_str WITH ``.
REPLACE ALL OCCURRENCES OF '，' IN p_str WITH ``.
REPLACE ALL OCCURRENCES OF '，' IN p_str WITH ``.
REPLACE ALL OCCURRENCES OF ',' IN p_str WITH ``.
DATA(lv_char)=cl_abap_char_utilities=>cr_lf+1(1).
REPLACE ALL OCCURRENCES OF lv_char IN p_str WITH ``.
ENDFORM .
FORM FRM_DISPLAY_ALV .
GS_LAYOUT-ZEBRA ='X'." GS_LAYOUT-BOX_FNAME = 'SET'.
* GS_LAYOUT-STYLEFNAME = 'STYLE'.
GS_LAYOUT-CWIDTH_OPT = 'X'.
GS_VARIANT-REPORT = SY-REPID.
GS_VARIANT-HANDLE = '0001'.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
EXPORTING
I_CALLBACK_PROGRAM = SY-REPID
IT_FIELDCAT_LVC = GT_FIELD
IS_LAYOUT_LVC = GS_LAYOUT
" IT_EVENTS = GT_EVENTS
IS_VARIANT = GS_VARIANT
* I_CALLBACK_TOP_OF_PAGE ='FRM_TOP_OF_PAGE'
* I_CALLBACK_USER_COMMAND ='FRM_USER_COMMAND'
I_CALLBACK_PF_STATUS_SET ='FRM_SET_STATUS'
" I_SAVE ='A'
I_DEFAULT ='X'
TABLES
T_OUTTAB =<ITAB>.
ENDFORM .
FORM FRM_SET_STATUS USING P_EXTAB TYPE SLIS_T_EXTAB .
SET PF-STATUS 'STATU'.
SET TITLEBAR 'STATU'.
ENDFORM.