ABAP——根据选择的布局发送Excel报表邮件

要求：根据选择的布局发送Excel报表邮件

文本：

代码：

************************************************************************
* 程 序 名：
* 程序描述：根据选择的布局栏位发送Excel邮件
* 事务代码：
************************************************************************
* 修改日志
************************************************************************
* 日期     版本 修改人       描述
* -------- ---- ------------ -------------------------------------------
* 20230520 1.0  Amell        创建程序
*
************************************************************************
REPORT zpprtest.

************************************************************************
* Type Pools Definitions          定义类型池
************************************************************************
TYPE-POOLS slis.

************************************************************************
* Tables Definitions
************************************************************************
TABLES: marc.
************************************************************************
* Data Definitions                定义数据
************************************************************************
TYPES: BEGIN OF  ty_data,
         matnr LIKE mara-matnr,       "物料號碼
         maktx LIKE makt-maktx,       "物料说明
         groes LIKE mara-groes,       "规格
       END OF ty_data.

DATA: gt_data TYPE TABLE OF ty_data.

DATA: gs_layout   TYPE lvc_s_layo, "布局
      gt_fieldcat TYPE lvc_t_fcat, "字段
      gs_variant  TYPE disvariant. "配置

DATA: gv_mail_address TYPE ad_smtpadr,
      gv_mat          TYPE string.

************************************************************************
* Includes Module                 包含模块
************************************************************************

************************************************************************
* Selection Screen                选择屏幕
************************************************************************
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
PARAMETERS: p_werks LIKE marc-werks DEFAULT '2000' OBLIGATORY.

SELECT-OPTIONS: s_matnr FOR marc-matnr.
SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE TEXT-002.
PARAMETERS: p_var  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-004.
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.
  PERFORM frm_pai_of_selection_screen.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_var.
  PERFORM frm_f4_for_variant.

AT SELECTION-SCREEN ON BLOCK b3.
  PERFORM frm_check_mail.

************************************************************************
* At Selection Screen Output      PBO事件
************************************************************************
AT SELECTION-SCREEN OUTPUT.

************************************************************************
* 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_F4_FOR_VARIANT
*&---------------------------------------------------------------------*
*& 布局搜索帮助
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_f4_for_variant .

  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 'E' NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ELSE.
    IF lv_exit EQ space.
      p_var = ls_variant-variant.
    ENDIF.
  ENDIF.

ENDFORM.

*&---------------------------------------------------------------------*
*& Form FRM_PAI_OF_SELECTION_SCREEN
*&---------------------------------------------------------------------*
*& 根据选择的布局获取布局的参数
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_pai_of_selection_screen .

  DATA: ls_variant TYPE disvariant.

  IF NOT p_var IS INITIAL.
    gs_variant-report = sy-repid.
    MOVE gs_variant TO ls_variant.
    MOVE p_var 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_CHECK_MAIL
*&---------------------------------------------------------------------*
*& 邮件检查
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_check_mail .
  IF p_mail = 'X'.
    IF p_title IS INITIAL.
      MESSAGE TEXT-005 TYPE 'E'.
    ENDIF.
    IF s_mail[] IS INITIAL.
      MESSAGE TEXT-006 TYPE 'E'.
    ENDIF.
  ENDIF.
ENDFORM.

*&---------------------------------------------------------------------*
*& Form FRM_GET_DATA
*&---------------------------------------------------------------------*
*& 获取数据
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_get_data .

  SELECT marc~matnr, "料号
         mara~groes, "规格
         makt~maktx  "物料说明
         INTO CORRESPONDING FIELDS OF TABLE @gt_data
         FROM marc
         INNER JOIN mara ON marc~matnr EQ mara~matnr
         LEFT JOIN makt ON mara~matnr EQ makt~matnr
         WHERE marc~matnr IN @s_matnr "料号
         AND marc~werks EQ @p_werks   "工厂
         AND spras = @sy-langu.       "当前登陆语言

ENDFORM.

*&---------------------------------------------------------------------*
*& Form FRM_DISPLAY_DATA
*&---------------------------------------------------------------------*
*& 显示数据
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_display_data .

  "栏位最适宽度
  gs_layout-cwidth_opt = 'X'.

  "ALV条纹
  gs_layout-zebra = 'X'.

  "构建ALV的栏位
  PERFORM frm_create_field.

  "整理附件数据
  PERFORM frm_process_mail_data.

  IF p_mail = 'X'.
    "发送邮件
    PERFORM frm_send_mail.
  ENDIF.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
    EXPORTING
      i_callback_program = sy-repid       "当前程序名
      is_layout_lvc      = gs_layout      "Layout
      is_variant         = gs_variant     "Variant
      it_fieldcat_lvc    = gt_fieldcat    "Fieldcat
      i_save             = 'A'
    TABLES
      t_outtab           = gt_data
    EXCEPTIONS
      program_error      = 1
      OTHERS             = 2.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_CREATE_FIELD
*&---------------------------------------------------------------------*
*& 创建报表栏位
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_create_field .

  DATA: ls_fieldcat TYPE lvc_s_fcat,
        lv_count    TYPE i.

  DEFINE fieldcat.
    ADD 1 TO lv_count.

    "栏位显示顺序
    ls_fieldcat-col_pos    = lv_count.
    "内表栏位
    ls_fieldcat-fieldname  = &1.
    "参考栏位
    ls_fieldcat-ref_field = &2.
    "参考表
    ls_fieldcat-ref_table = &3.
    "单位
    ls_fieldcat-qfieldname = &4.
    "栏位标题（长文或中等文或短文）
    ls_fieldcat-scrtext_s = ls_fieldcat-scrtext_m =
    ls_fieldcat-scrtext_l = &5.
    "显示长文或中等文或短文
    ls_fieldcat-colddictxt = &6.

    APPEND ls_fieldcat TO gt_fieldcat.
    CLEAR ls_fieldcat.
  END-OF-DEFINITION.

  fieldcat 'MATNR' 'MATNR' 'MARC' '' '' ''.
  fieldcat 'MAKTX' 'MAKTX' 'MAKT' '' '' ''.
  fieldcat 'GROES' 'GROES' 'MARA' '' '' ''.

ENDFORM.

*&---------------------------------------------------------------------*
*& Form FRM_SEND_MAIL
*&---------------------------------------------------------------------*
*& 发送邮件
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_send_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. "发送成功标识

  "整理附件数据
  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_mat
          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.

  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 ).

*     add the spread sheet as attachment to document object

*     WIP
      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 s_mail.
*     create recipient object
        lo_recipient =
        cl_cam_address_bcs=>create_internet_address( s_mail-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
*&---------------------------------------------------------------------*
*& 整理邮件的Excel数据
*&---------------------------------------------------------------------*
*& -->  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 lvc_t_fcat,
        ls_fieldcat   TYPE lvc_s_fcat,
        lt_sort_table TYPE abap_sortorder_tab,
        ls_sort_table TYPE abap_sortorder,
        lt_dd03m      TYPE TABLE OF dd03m,
        ls_dd03m      TYPE dd03m,
        lv_index      TYPE sy-tabix,
        lv_line       TYPE string,
        lv_filed      TYPE string.

  DATA: lt_dbfieldcat  TYPE TABLE OF ltdxdata,
        lt_dbsortinfo  TYPE TABLE OF ltdxdata,
        lt_dbsortinfo2 TYPE TABLE OF ltdxdata,
        lt_dbfilter    TYPE TABLE OF ltdxdata,
        ls_dxdata      TYPE ltdxdata,
        ls_dxdata2     TYPE ltdxdata,
        ls_varkey      TYPE ltdxkey.

  lt_fieldcat = gt_fieldcat.

  "根据选择的布局，获取布局的栏位、排序条件、过滤条件
  IF NOT p_var IS INITIAL.
    ls_varkey-report = sy-repid.
    ls_varkey-variant = p_var.
    ls_varkey-type = 'F'.

    CALL FUNCTION 'LT_DBDATA_READ_FROM_LTDX'
      EXPORTING
        is_varkey    = ls_varkey
      TABLES
        t_dbfieldcat = lt_dbfieldcat
        t_dbsortinfo = lt_dbsortinfo
        t_dbfilter   = lt_dbfilter
      EXCEPTIONS
        not_found    = 1
        wrong_relid  = 2
        OTHERS       = 3.

    IF NOT lt_dbfieldcat IS INITIAL.

      LOOP AT lt_fieldcat INTO ls_fieldcat.

        lv_index = sy-tabix.

        "在Fieldcat里删除不显示的栏位
        READ TABLE lt_dbfieldcat 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.

        "修改Fieldcat里栏位的顺序号
        READ TABLE lt_dbfieldcat 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_dbsortinfo IS NOT INITIAL.

      lt_dbsortinfo2 = lt_dbsortinfo.

      DELETE lt_dbsortinfo2 WHERE param NE 'SPOS'.

      "先按照栏位排序的优先级排序
      SORT lt_dbsortinfo2 BY value.

      "再补充栏位的倒序和顺序数据
      LOOP AT lt_dbsortinfo2 INTO ls_dxdata2.
        LOOP AT lt_dbsortinfo INTO ls_dxdata WHERE value = 'X'
          AND ( param = 'UP' OR param = 'DOWN' ) AND key1 = ls_dxdata2-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_data BY (lt_sort_table).
    ENDIF.
  ENDIF.

  "根据参考表和参考栏位抓取栏位资料
  IF NOT lt_fieldcat IS INITIAL.
    SELECT * INTO TABLE lt_dd03m
           FROM dd03m
           FOR ALL ENTRIES IN lt_fieldcat
           WHERE tabname = lt_fieldcat-ref_table
           AND fieldname = lt_fieldcat-ref_field
           AND ddlanguage = sy-langu.

    SORT lt_dd03m BY tabname fieldname.
  ENDIF.

  "Excel标题
  LOOP AT lt_fieldcat INTO ls_fieldcat.

    lv_index = sy-tabix.

    "如果没有在创建栏位时给栏位说明，则根据参考表和参考栏位抓取栏位说明
    IF ls_fieldcat-scrtext_l IS INITIAL.
      READ TABLE lt_dd03m INTO ls_dd03m WITH KEY
           tabname = ls_fieldcat-ref_table
           fieldname = ls_fieldcat-ref_field
           BINARY SEARCH.
      IF sy-subrc = 0.
        ls_fieldcat-scrtext_l = ls_dd03m-scrtext_l.
      ELSE.
        ls_fieldcat-scrtext_l = TEXT-007.
      ENDIF.
    ENDIF.

    IF lv_index > 1.
      CONCATENATE lv_line
                ls_fieldcat-scrtext_l INTO lv_line SEPARATED BY lc_tab.
    ELSE.
      CONCATENATE lv_line
              ls_fieldcat-scrtext_l INTO lv_line.
    ENDIF.

  ENDLOOP.

  "追加标题后换行
  CONCATENATE gv_mat lv_line lc_crlf INTO gv_mat.

  "Excel内容
  LOOP AT gt_data ASSIGNING <ls_line>.

    CLEAR lv_line.

    LOOP AT lt_fieldcat INTO ls_fieldcat.

      lv_index = sy-tabix.

      ASSIGN COMPONENT ls_fieldcat-fieldname OF STRUCTURE <ls_line> TO <lv_field>.

      READ TABLE lt_dd03m INTO ls_dd03m WITH KEY
           tabname = ls_fieldcat-ref_table
           fieldname = ls_fieldcat-ref_field
           BINARY SEARCH.
      "如果是日期栏位则进行额外处理
      IF sy-subrc = 0 AND ls_dd03m-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 lv_index > 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_mat lv_line lc_crlf INTO gv_mat.

  ENDLOOP.
ENDFORM.