1、SUBMIT调用COOIS报表获取报表结果
2、根据布局格式整理Excel的栏位数据
3、把Excel压缩ZIP
4、发送邮件
运行效果:
************************************************************************
* 程 序 名:
* 程序描述:调用COOIS工序报表发送邮件
* 事务代码:
************************************************************************
* 修改日志
************************************************************************
* 日期 版本 修改人 描述
* -------- ---- ------------ -------------------------------------------
*
************************************************************************
REPORT zpprtest.
************************************************************************
* Type Pools Definitions 定义类型池
************************************************************************
TYPE-POOLS slis.
************************************************************************
* Tables Definitions
************************************************************************
TABLES: aufk,marc,afko,varit.
************************************************************************
* Data Definitions 定义数据
************************************************************************
DATA: gt_wip TYPE TABLE OF iooper.
DATA: gs_layout TYPE slis_layout_alv, "布局
gt_fieldcat TYPE slis_t_fieldcat_alv, "字段
gs_variant TYPE disvariant. "变式
DATA: gv_mail_address TYPE ad_smtpadr,
gv_wip TYPE string.
FIELD-SYMBOLS:<gt_wip> TYPE STANDARD TABLE.
************************************************************************
* Includes Module 包含模块
************************************************************************
************************************************************************
* Selection Screen 选择屏幕
************************************************************************
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS: s_aufnr FOR aufk-aufnr,
s_matnr FOR marc-matnr,
s_auart FOR aufk-auart,
s_werks FOR marc-werks DEFAULT '2000',
s_dispo FOR marc-dispo,
s_gltrp FOR afko-gltrp,
s_gstrp FOR afko-gstrp.
PARAMETERS: p_selid LIKE tj48t-selid DEFAULT 'ZPP0005',
p_var LIKE varid-variant DEFAULT '制造各课在制报表'.
SELECTION-SCREEN END OF BLOCK b1.
SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE TEXT-002.
PARAMETERS: p_layout TYPE disvariant-variant.
SELECTION-SCREEN SKIP 1.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (70) TEXT-003.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK b2.
SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME TITLE TEXT-007.
PARAMETERS: p_mail AS CHECKBOX,
p_title TYPE so_obj_des.
SELECT-OPTIONS: s_mail FOR gv_mail_address NO INTERVALS.
SELECTION-SCREEN END OF BLOCK b3.
************************************************************************
* Initialization 初始化事件
************************************************************************
INITIALIZATION.
************************************************************************
* At Selection Screen PAI事件
************************************************************************
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_var.
PERFORM frm_f4_for_selection_condition.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_layout.
PERFORM frm_f4_for_layout.
AT SELECTION-SCREEN.
PERFORM frm_pai_of_selection_screen..
************************************************************************
* At Selection Screen Output PBO事件
************************************************************************
AT SELECTION-SCREEN OUTPUT.
AT SELECTION-SCREEN ON p_var.
PERFORM frm_check_input_for_variant.
************************************************************************
* Report Format 报表格式
************************************************************************
TOP-OF-PAGE.
END-OF-PAGE.
************************************************************************
* Main Process 主要逻辑
************************************************************************
START-OF-SELECTION.
"获取数据
PERFORM frm_get_data.
"显示数据
PERFORM frm_display_data.
END-OF-SELECTION.
*&---------------------------------------------------------------------*
*& Form FRM_GET_DATA
*&---------------------------------------------------------------------*
*& 获取数据
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_get_data .
DATA:lo_data TYPE REF TO data.
cl_salv_bs_runtime_info=>clear_all( ).
cl_salv_bs_runtime_info=>set( EXPORTING display = abap_false
metadata = abap_false
data = abap_true ).
SUBMIT ppio_entry "COOIS工单资讯系统
"VIA SELECTION-SCREEN "停留在选择界面
USING SELECTION-SET p_var "COOIS变式
WITH s_aufnr IN s_aufnr "工单
WITH s_matnr IN s_matnr "物料
WITH s_werks IN s_werks "工厂
WITH s_auart IN s_auart "工单类型
WITH s_dispo IN s_dispo "MRP控制员
WITH s_eckst IN s_gstrp "开工日期
WITH s_ecken IN s_gltrp "完工日期
WITH p_selid = p_selid "状态选择参数
AND RETURN. "返回当前程序
TRY.
cl_salv_bs_runtime_info=>get_data_ref( IMPORTING r_data = lo_data ).
ASSIGN lo_data->* TO <gt_wip>.
IF sy-subrc = 0.
gt_wip = <gt_wip>.
ENDIF.
CATCH cx_salv_bs_sc_runtime_info.
MESSAGE TEXT-008 TYPE 'E'.
ENDTRY.
cl_salv_bs_runtime_info=>clear_all( ).
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_DISPLAY_DATA
*&---------------------------------------------------------------------*
*& 显示数据
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_display_data .
DATA: lv_inclname TYPE trdir-name.
"栏位最适宽度
gs_layout-colwidth_optimize = 'X'.
"ALV条纹
gs_layout-zebra = 'X'.
lv_inclname = sy-repid.
"ALV栏位
CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
EXPORTING
i_program_name = sy-repid
i_structure_name = 'IOOPER'
i_inclname = lv_inclname
CHANGING
ct_fieldcat = gt_fieldcat
EXCEPTIONS
inconsistent_interface = 1
program_error = 2
OTHERS = 3.
IF p_mail = 'X'.
"发送邮件
PERFORM frm_send_mail TABLES s_mail.
ENDIF.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
i_callback_program = sy-repid
is_layout = gs_layout
is_variant = gs_variant
it_fieldcat = gt_fieldcat
i_save = 'A'
TABLES
t_outtab = gt_wip
EXCEPTIONS
program_error = 1
OTHERS = 2.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_PAI_OF_SELECTION_SCREEN
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_pai_of_selection_screen .
DATA: ls_variant TYPE disvariant.
IF NOT p_layout IS INITIAL.
gs_variant-report = sy-repid.
MOVE gs_variant TO ls_variant.
MOVE p_layout TO ls_variant-variant.
CALL FUNCTION 'REUSE_ALV_VARIANT_EXISTENCE'
EXPORTING
i_save = 'A'
CHANGING
cs_variant = ls_variant.
gs_variant = ls_variant.
ELSE.
CLEAR gs_variant.
gs_variant-report = sy-repid.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_F4_FOR_LAYOUT
*&---------------------------------------------------------------------*
*& 布局搜索帮助
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_f4_for_layout .
DATA:ls_variant TYPE disvariant,
lv_exit TYPE char1.
ls_variant-report = sy-repid.
CALL FUNCTION 'REUSE_ALV_VARIANT_F4'
EXPORTING
is_variant = ls_variant
i_save = 'A'
IMPORTING
e_exit = lv_exit
es_variant = ls_variant
EXCEPTIONS
not_found = 2.
IF sy-subrc = 2.
MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ELSE.
IF lv_exit EQ space.
p_layout = ls_variant-variant.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_F4_FOR_SELECTION_CONDITION
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_f4_for_selection_condition .
DATA: lv_result TYPE string.
CALL FUNCTION 'F4_REPORT_VARIANT'
EXPORTING
program = 'PPIO_ENTRY'
IMPORTING
result = lv_result.
p_var = lv_result.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_CHECK_INPUT_FOR_VARIANT
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_check_input_for_variant .
IF p_var IS INITIAL.
MESSAGE TEXT-004 TYPE 'E'.
ELSE.
SELECT SINGLE * FROM varit
WHERE report = 'PPIO_ENTRY'
AND variant = p_var.
IF sy-subrc NE 0.
MESSAGE TEXT-005 TYPE 'E'.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_SEND_MAIL
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> S_MAIL
*&---------------------------------------------------------------------*
FORM frm_send_mail TABLES lt_mail_addr STRUCTURE s_mail.
DATA: lo_send_request TYPE REF TO cl_bcs, "请求
lo_document TYPE REF TO cl_document_bcs, "文档
lo_recipient TYPE REF TO if_recipient_bcs, "收件人
lo_bcs_exception TYPE REF TO cx_bcs. "异常
DATA: lt_main_text TYPE bcsy_text, "主要内容
lt_binary_content TYPE solix_tab, "二进制内容
lv_size TYPE so_obj_len, "文件內容大小
lv_sent_to_all TYPE os_boolean.
DATA: lo_zip TYPE REF TO cl_abap_zip. "ZIP
DATA: lv_size_int TYPE i,
lv_xstring TYPE xstring,
lv_zip_name TYPE string,
lv_zip_xstring TYPE xstring.
"整理附件数据
PERFORM frm_process_mail_data.
* --------------------------------------------------------------
* convert the text string into UTF-16LE binary data including
* byte-order-mark. Mircosoft Excel prefers these settings
* all this is done by new class cl_bcs_convert (see note 1151257)
* WIP
TRY.
cl_bcs_convert=>string_to_solix(
EXPORTING
iv_string = gv_wip
iv_codepage = '4103' "suitable for MS Excel, leave empty
iv_add_bom = 'X' "for other doc types
IMPORTING
et_solix = lt_binary_content
ev_size = lv_size ).
CATCH cx_bcs.
MESSAGE e445(so).
ENDTRY.
* string转xstring
lv_size_int = lv_size.
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
EXPORTING
input_length = lv_size_int
IMPORTING
buffer = lv_xstring
TABLES
binary_tab = lt_binary_content.
TRY.
* -------- create persistent send request ------------------------
lo_send_request = cl_bcs=>create_persistent( ).
* -------- create and set document with attachment ---------------
* create document object from internal table with text
* append 'Hello world!' to main_text. "#EC NOTEXT
* 邮件内容
APPEND 'Dear Mr and Miss:' TO lt_main_text.
APPEND '' TO lt_main_text.
APPEND ' This Email is sent by systme, Do not Return the Email.' TO lt_main_text.
APPEND '' TO lt_main_text.
APPEND 'Thanks.' TO lt_main_text.
lo_document = cl_document_bcs=>create_document(
i_type = 'RAW'
i_text = lt_main_text
i_subject = p_title ).
* 压缩成zip
CREATE OBJECT lo_zip.
CONCATENATE 'COOIS_WIP_' sy-datum '.xls' INTO lv_zip_name.
CALL METHOD lo_zip->add
EXPORTING
name = lv_zip_name
content = lv_xstring.
CALL METHOD lo_zip->save
RECEIVING
zip = lv_zip_xstring.
CALL METHOD cl_bcs_convert=>xstring_to_solix
EXPORTING
iv_xstring = lv_zip_xstring
RECEIVING
et_solix = lt_binary_content.
lv_size = xstrlen( lv_zip_xstring ).
* add the spread sheet as attachment to document object
* 添加ZIP文件
CALL METHOD lo_document->add_attachment
EXPORTING
i_attachment_type = 'ZIP'
i_attachment_subject = '制造各课在制'
i_attachment_size = lv_size
i_att_content_hex = lt_binary_content.
* 添加Excel文件
* lo_document->add_attachment(
* i_attachment_type = 'xls' "#EC NOTEXT
* i_attachment_subject = '制造各课在制'
* i_attachment_size = lv_size
* i_att_content_hex = lt_binary_content ).
* add document object to send request
lo_send_request->set_document( lo_document ).
* --------- add recipient (e-mail address) -----------------------
LOOP AT lt_mail_addr.
* create recipient object
lo_recipient =
cl_cam_address_bcs=>create_internet_address( lt_mail_addr-low ).
* add recipient object to send request
lo_send_request->add_recipient( lo_recipient ).
ENDLOOP.
* ---------- send document ---------------------------------------
lv_sent_to_all = lo_send_request->send( i_with_error_screen = 'X' ).
IF lv_sent_to_all IS INITIAL.
MESSAGE i500(sbcoms).
ELSE.
COMMIT WORK.
MESSAGE s022(so).
ENDIF.
* ------------ exception handling ----------------------------------
* replace this rudimentary exception handling with your own one !!!
CATCH cx_bcs INTO lo_bcs_exception.
MESSAGE i865(so) WITH lo_bcs_exception->error_type.
ENDTRY.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_PROCESS_MAIL_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_process_mail_data .
CONSTANTS: lc_tab TYPE c VALUE cl_bcs_convert=>gc_tab,
lc_crlf TYPE c VALUE cl_bcs_convert=>gc_crlf.
FIELD-SYMBOLS: <ls_line> TYPE any,
<lv_field> TYPE any.
DATA: lt_fieldcat TYPE slis_t_fieldcat_alv,
ls_fieldcat TYPE slis_fieldcat_alv,
lt_sort_table TYPE abap_sortorder_tab,
ls_sort_table TYPE abap_sortorder,
lv_index TYPE sy-tabix,
lv_line TYPE string,
lv_filed TYPE string.
DATA: lt_db_fieldcat TYPE TABLE OF ltdxdata, "显示的字段
lt_db_sort_info TYPE TABLE OF ltdxdata, "排序字段
lt_db_sort_no_info TYPE TABLE OF ltdxdata, "排序序号
lt_db_filter TYPE TABLE OF ltdxdata, "筛选条件
ls_dxdata TYPE ltdxdata,
ls_varkey TYPE ltdxkey.
lt_fieldcat[] = gt_fieldcat[].
"获取选择的布局
IF p_layout IS NOT INITIAL.
ls_varkey-report = sy-repid.
ls_varkey-variant = p_layout.
ls_varkey-type = 'F'.
CALL FUNCTION 'LT_DBDATA_READ_FROM_LTDX'
EXPORTING
* I_TOOL = 'LT'
is_varkey = ls_varkey
TABLES
t_dbfieldcat = lt_db_fieldcat
t_dbsortinfo = lt_db_sort_info
t_dbfilter = lt_db_filter
* T_DBLAYOUT =
EXCEPTIONS
not_found = 1
wrong_relid = 2
OTHERS = 3.
IF lt_db_fieldcat IS NOT INITIAL.
LOOP AT lt_fieldcat INTO ls_fieldcat.
lv_index = sy-tabix.
READ TABLE lt_db_fieldcat INTO ls_dxdata WITH KEY
key1 = ls_fieldcat-fieldname
param = 'NO_OUT'
value = 'X'.
IF sy-subrc = 0.
DELETE lt_fieldcat INDEX lv_index.
CONTINUE.
ENDIF.
READ TABLE lt_db_fieldcat INTO ls_dxdata WITH KEY
key1 = ls_fieldcat-fieldname
param = 'COL_POS'.
IF sy-subrc = 0.
ls_fieldcat-col_pos = ls_dxdata-value.
ENDIF.
MODIFY lt_fieldcat FROM ls_fieldcat.
ENDLOOP.
SORT lt_fieldcat BY col_pos.
ENDIF.
IF lt_db_sort_info IS NOT INITIAL.
lt_db_sort_no_info = lt_db_sort_info.
DELETE lt_db_sort_no_info WHERE param NE 'SPOS'.
SORT lt_db_sort_no_info BY value.
"排序
LOOP AT lt_db_sort_no_info INTO DATA(ls_db_sort_no_info).
LOOP AT lt_db_sort_info INTO ls_dxdata WHERE value = 'X'
AND ( param = 'UP' OR param = 'DOWN' )
AND key1 = ls_db_sort_no_info-key1.
ls_sort_table-name = ls_dxdata-key1.
IF ls_dxdata-param = 'DOWN'.
ls_sort_table-descending = 'X'.
ENDIF.
APPEND ls_sort_table TO lt_sort_table.
CLEAR ls_sort_table.
ENDLOOP.
ENDLOOP.
SORT <gt_wip> BY (lt_sort_table).
ENDIF.
ENDIF.
"删除无在制数据
DELETE <gt_wip> WHERE ('zwip <= 0').
"Excel标题
LOOP AT lt_fieldcat INTO ls_fieldcat.
IF sy-tabix > 1.
CONCATENATE lv_line
ls_fieldcat-seltext_l INTO lv_line SEPARATED BY lc_tab.
ELSE.
CONCATENATE lv_line
ls_fieldcat-seltext_l INTO lv_line.
ENDIF.
ENDLOOP.
"追加标题后换行
CONCATENATE gv_wip lv_line lc_crlf INTO gv_wip.
"Excel内容
LOOP AT <gt_wip> ASSIGNING <ls_line>.
CLEAR lv_line.
LOOP AT lt_fieldcat INTO ls_fieldcat.
ASSIGN COMPONENT ls_fieldcat-fieldname OF STRUCTURE <ls_line> TO <lv_field>.
IF ls_fieldcat-datatype = 'DATS'.
IF <lv_field> NE '00000000' AND <lv_field> NE ''.
lv_filed = <lv_field>+0(4) && '/' && <lv_field>+4(2) && '/' && <lv_field>+6(2).
ELSE.
lv_filed = ''.
ENDIF.
ELSE.
lv_filed = <lv_field>.
ENDIF.
IF sy-tabix > 1.
CONCATENATE lv_line lv_filed INTO lv_line SEPARATED BY lc_tab.
ELSE.
CONCATENATE lv_line lv_filed INTO lv_line.
ENDIF.
ENDLOOP.
"追加内容后换行
CONCATENATE gv_wip lv_line lc_crlf INTO gv_wip.
ENDLOOP.
ENDFORM.