ABAP內表轉xml文件以Excel形式發送郵件

轉載：http://www.rzrgm.cn/dy-debug/p/13920667.html
之前做的比較簡單的郵件發送Excel程序無法更改單元格格式，比如遇到數字欄位內容較長，則內容顯示如下
從 Office2007開始, 當我們新建一個word、excel等文檔時，后者遵循了一個開源的規范：Office openXML格式。
所以可以使用ABAP直接創建一個符合上述規范的XML，就可以得到相應的word、excel 用于下載或者作為郵件附件。
openXML介紹：https://baike.baidu.com/item/openXML/8399547?fr=aladdin
ABAP解析XML：http://www.rzrgm.cn/jiangzhengjun/p/4265595.html
代碼：
*&---------------------------------------------------------------------*
*& Report  z_test
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  z_test.

TYPE-POOLS: ixml.

*----------------------------------------------------------------------*
* global variable
*----------------------------------------------------------------------*
DATA:
      l_xml_table_forecast    TYPE TABLE OF x255,
      l_rc                    TYPE i,
      l_xml_size              TYPE i,
      binary_content_forecast TYPE solix_tab,
      sent_to_all             TYPE os_boolean,
      main_text               TYPE bcsy_text,
      send_request            TYPE REF TO cl_bcs,
      document                TYPE REF TO cl_document_bcs,
      recipient               TYPE REF TO if_recipient_bcs,
      bcs_exception           TYPE REF TO cx_bcs,
      mailto                  TYPE ad_smtpadr VALUE 'XXXXX@163.com'. "此處填入自己的郵箱

*----------------------------------------------------------------------*
* Table
*----------------------------------------------------------------------*
DATA: gt_sflight              TYPE TABLE OF sflight,
      gs_sflight              TYPE sflight.

*----------------------------------------------------------------------*
* Start-of-selection.
*----------------------------------------------------------------------*
START-OF-SELECTION.

  PERFORM frm_get_flight.

  PERFORM frm_process_xml_data.

  PERFORM frm_send_email.

*&---------------------------------------------------------------------*
*&      Form  FRM_GET_FLIGHT
*&---------------------------------------------------------------------*
*       附件數據
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM frm_get_flight .

  SELECT * UP TO 10 ROWS                                    "搜10行
    FROM sflight
    INTO CORRESPONDING FIELDS OF TABLE gt_sflight.
ENDFORM.                    " FRM_GET_FLIGHT
*&---------------------------------------------------------------------*
*&      Form  FRM_PROCESS_XML_DATA
*&---------------------------------------------------------------------*
*       附件xml
*----------------------------------------------------------------------*
*      -->P_1      text
*----------------------------------------------------------------------*
FORM frm_process_xml_data.
  DATA: l_ixml          TYPE REF TO if_ixml,
        l_streamfactory TYPE REF TO if_ixml_stream_factory,
        l_ostream       TYPE REF TO if_ixml_ostream,
        l_renderer      TYPE REF TO if_ixml_renderer,
        l_document      TYPE REF TO if_ixml_document.

  DATA: l_element_root  TYPE REF TO if_ixml_element,
        r_element       TYPE REF TO if_ixml_element,
        r_worksheet     TYPE REF TO if_ixml_element,
        r_table         TYPE REF TO if_ixml_element,
        r_column        TYPE REF TO if_ixml_element,
        r_row           TYPE REF TO if_ixml_element,
        r_cell          TYPE REF TO if_ixml_element,
        r_data          TYPE REF TO if_ixml_element,
        l_value         TYPE string.

  FIELD-SYMBOLS:<ls_flight> TYPE sflight.

*  create a ixml factory
  l_ixml = cl_ixml=>create( ).

*  create the DOM object model
  l_document = l_ixml->create_document( ).

*  create workbook
  PERFORM create_workbook USING l_document r_worksheet r_table.

*  column formatting
  PERFORM frm_forecast_column_format USING l_document r_table.

*  data table
  LOOP AT gt_sflight ASSIGNING <ls_flight>.
    r_row = l_document->create_simple_element( name = 'Row' parent = r_table ).

    r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
    l_value = <ls_flight>-mandt.
    r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ).
    r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

    r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
    l_value = <ls_flight>-carrid.
    r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ).
    r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

    r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
    l_value = <ls_flight>-connid.
    r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ).
    r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

    "這一列的cell都使用了預定義的Style：Detail
    r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
    r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Detail').
    l_value = <ls_flight>-fldate.
    r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ).
    r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

    r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
    l_value = <ls_flight>-price.
    r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ).
    r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

    r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
    l_value = <ls_flight>-currency.
    r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ).
    r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

    r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
    l_value = <ls_flight>-planetype.
    r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ).
    r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

    r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
    l_value = <ls_flight>-seatsmax.
    r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ).
    r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

  ENDLOOP.

* XML作為二進制數據流保存到內表
*   creating a stream factory
  l_streamfactory = l_ixml->create_stream_factory( ).

*   connect internal xml table to stream factory
  l_ostream = l_streamfactory->create_ostream_itable( table = l_xml_table_forecast ).

*   rendering the document
  l_renderer = l_ixml->create_renderer( ostream = l_ostream document = l_document ).
  l_rc = l_renderer->render( ).

* saving the xml document
  l_xml_size = l_ostream->get_num_written_raw( ).
ENDFORM.                    " FRM_PROCESS_XML_DATA
*&---------------------------------------------------------------------*
*&      Form  CREATE_WORKBOOK
*&---------------------------------------------------------------------*
*       新建工作區
*----------------------------------------------------------------------*
*      -->P_L_DOCUMENT  text
*      -->P_R_WORKSHEET  text
*      -->P_R_TABLE  text
*----------------------------------------------------------------------*
FORM create_workbook USING l_document  TYPE REF TO if_ixml_document
                           r_worksheet TYPE REF TO if_ixml_element
                           r_table     TYPE REF TO if_ixml_element.

  DATA: l_element_root        TYPE REF TO if_ixml_element,
        ns_attribute          TYPE REF TO if_ixml_attribute,
        r_element_properties  TYPE REF TO if_ixml_element,
        r_styles              TYPE REF TO if_ixml_element,
        r_style               TYPE REF TO if_ixml_element,
        r_border              TYPE REF TO if_ixml_element,
        r_format              TYPE REF TO if_ixml_element,
        l_value               TYPE string.

*  create root node 'workbook'
  l_element_root = l_document->create_simple_element( name = 'Workbook' parent = l_document ).
  l_element_root->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:spreadsheet' ).

  ns_attribute = l_document->create_namespace_decl( name = 'ss' prefix = 'xmlns'                                                     uri = 'urn:schemas-microsoft-com:office:spreadsheet' ).
  l_element_root->set_attribute_node( ns_attribute ).

  ns_attribute = l_document->create_namespace_decl( name = 'x' prefix = 'xmlns' 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　uri = 'urn:schemas-microsoft-com:office:excel' ).
  l_element_root->set_attribute_node( ns_attribute ).

*  create node for document properties
  r_element_properties = l_document->create_simple_element( name = 'TEST_REPORT' parent = l_element_root ).
  l_value = sy-uname.
  l_document->create_simple_element( name = 'Author' value = l_value parent = r_element_properties ).

* Styles(類似css，可以被cell使用)
  r_styles = l_document->create_simple_element( name = 'Styles' parent = l_element_root ).

* 預定義表頭格式：Head
  r_style = l_document->create_simple_element( name = 'Style' parent = r_styles ).
  r_style->set_attribute_ns( name = 'ID'  prefix = 'ss' value = 'Head' ).

  "邊框
  r_border = l_document->create_simple_element( name = 'Borders' parent = r_style ).

  r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
  r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
  r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
  r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '2' ).

  r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
  r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).
  r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
  r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '2' ).

  r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
  r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).
  r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
  r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '2' ).

  r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
  r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).
  r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
  r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '2' ).

  "顏色
  r_format  = l_document->create_simple_element( name = 'Interior' parent = r_style ).
  r_format->set_attribute_ns( name = 'Color' prefix = 'ss' value = '#BFBFBF' ).
  r_format->set_attribute_ns( name = 'Pattern' prefix = 'ss' value = 'Solid' ).

  "格式居中
  r_format = l_document->create_simple_element( name = 'Alignment' parent = r_style ).
  r_format->set_attribute_ns( name = 'Horizontal' prefix = 'ss' value = 'Center' ).
  r_format->set_attribute_ns( name = 'Vertical' prefix = 'ss' value = 'Center' ).
  r_format->set_attribute_ns( name = 'WrapText'  prefix = 'ss'  value = '1' ).

* 預定義明細格式格式：Detail
  r_style = l_document->create_simple_element( name = 'Style' parent = r_styles ).
  r_style->set_attribute_ns( name = 'ID'  prefix = 'ss' value = 'Detail' ).

  "邊框
  r_border = l_document->create_simple_element( name = 'Borders' parent = r_style ).

  r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
  r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
  r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
  r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

  r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
  r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).
  r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
  r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

  r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
  r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).
  r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
  r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

  r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
  r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).
  r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
  r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

*  worksheet
  r_worksheet = l_document->create_simple_element( name = 'Worksheet' parent = l_element_root ).
  r_worksheet->set_attribute_ns( name = 'Name' prefix = 'ss' value = 'Sheet1' ).

*  table
  r_table = l_document->create_simple_element( name = 'Table' parent = r_worksheet ).
  r_table->set_attribute_ns( name = 'FullColumns' prefix = 'x' value = '1' ).
  r_table->set_attribute_ns( name = 'FullRows'    prefix = 'x' value = '1' ).
ENDFORM.                    " CREATE_WORKBOOK
*&---------------------------------------------------------------------*
*&      Form  FRM_FORECAST_COLUMN_FORMAT
*&---------------------------------------------------------------------*
*       表頭
*----------------------------------------------------------------------*
*      -->P_L_DOCUMENT  text
*      -->P_R_TABLE  text
*----------------------------------------------------------------------*
FORM frm_forecast_column_format  USING    l_document TYPE REF TO if_ixml_document
                                          r_table TYPE REF TO if_ixml_element.

  DATA: l_element_root TYPE REF TO if_ixml_element,
        r_column       TYPE REF TO if_ixml_element,
        r_row          TYPE REF TO if_ixml_element,
        r_cell         TYPE REF TO if_ixml_element,
        r_data         TYPE REF TO if_ixml_element,
        l_value        TYPE string.
  DATA: lv_exe_date    TYPE char10,
        lv_exe_time    TYPE char10,
        lt_month_names TYPE TABLE OF t247,
        ls_month_name  TYPE t247,
        lv_date_add    TYPE sy-datum,
        lv_count       TYPE i.

  WRITE sy-datum TO lv_exe_date.
  CONCATENATE sy-uzeit+0(2) ':' sy-uzeit+2(2) INTO lv_exe_time.

  CALL FUNCTION 'MONTH_NAMES_GET'
    EXPORTING
      language              = sy-langu
    TABLES
      month_names           = lt_month_names
    EXCEPTIONS
      month_names_not_found = 1
      OTHERS                = 2.

  IF sy-subrc = 0.
    READ TABLE lt_month_names WITH KEY mnr = sy-datum+4(2) INTO ls_month_name.
  ENDIF.

*  Format columns width based on the data length 設定sheet前8列的列寬
*  line
  DO 1 TIMES.
    r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
    r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '30' ).
  ENDDO.

  DO 14 TIMES.
    r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
    r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '100' ).
  ENDDO.

*  information row 基本信息
  r_row = l_document->create_simple_element( name = 'Row' parent = r_table ).
  r_row->set_attribute_ns( name = 'AutoFitHeight' prefix = 'ss' value = '1' ).

*  type
  r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
  l_value = 'SFLIGHT_FORECAST'.
  r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

  DO 3 TIMES. "空三個cell
    r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
  ENDDO.

*   name
  r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).

  l_value = 'TEST_USER'.
  r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).          
  r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).

*   Date
  r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).
  l_value = lv_exe_date.
  r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).          
  r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).

*   Time
  r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).
  l_value = lv_exe_time.
  r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).         
  r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).

*   Column Headers Row 表頭行
  r_row = l_document->create_simple_element( name = 'Row'  parent = r_table ).
  r_row->set_attribute_ns( name = 'AutoFitHeight'  prefix = 'ss'  value = '1' ).

*  mandt
  r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
  r_data = l_document->create_simple_element( name = 'Data' value = 'mandt' parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

*  carrid
  r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
  r_data = l_document->create_simple_element( name = 'Data' value = 'carrid' parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

*  connid
  r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
  r_data = l_document->create_simple_element( name = 'Data' value = 'connid' parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

*  fldate 這個cell使用了預定義的Style：Head
  r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
  r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Head').
  r_data = l_document->create_simple_element( name = 'Data' value = 'fldate' parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

*  price
  r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
  r_data = l_document->create_simple_element( name = 'Data' value = 'price' parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

*  currency
  r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
  r_data = l_document->create_simple_element( name = 'Data' value = 'currency' parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

*  planetype
  r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
  r_data = l_document->create_simple_element( name = 'Data' value = 'planetype' parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

*  seatsmax
  r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
  r_data = l_document->create_simple_element( name = 'Data' value = 'seatsmax' parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
ENDFORM.                    " FRM_FORECAST_COLUMN_FORMAT
*&---------------------------------------------------------------------*
*&      Form  FRM_SEND_EMAIL
*&---------------------------------------------------------------------*
*       發郵件
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM frm_send_email .
  DATA:ls_solix          TYPE solix,
       lc_xls_type       TYPE so_obj_tp VALUE 'XLS',
       lt_binary_content TYPE solix_tab.

  "二進制文件
  LOOP AT l_xml_table_forecast INTO ls_solix-line.

    APPEND ls_solix TO binary_content_forecast.
    CLEAR ls_solix.
  ENDLOOP.

  TRY .
*    -------------create persistent sent request----------------
      send_request = cl_bcs=>create_persistent( ).

*    -------------create and set document with attachment-------
*    create document object from internal table with text
      APPEND 'Mail text!' TO main_text.
      document = cl_document_bcs=>create_document(
        i_type = 'HTM'
        i_text = main_text
        i_subject = 'Test created' ).

*    add the spread sheet as attachment to document object
*    excel附件
      document->add_attachment(
        i_attachment_type = lc_xls_type
        i_attachment_subject = 'SpreadSheet'
        i_att_content_hex = binary_content_forecast ).

*    send document object to send request
      send_request->set_document( document ).

*    --------------add recipient (e-mail address)--------------
*    create recipient object
      recipient = cl_cam_address_bcs=>create_internet_address( mailto ).

*    add recipient object to send request
      send_request->add_recipient( recipient ).

*    --------------send document ------------------------------

      CALL METHOD send_request->set_send_immediately( 'X' )."立即發送
      sent_to_all = send_request->send( i_with_error_screen = 'X' ).

      COMMIT WORK.

      IF sent_to_all IS INITIAL.
        MESSAGE i500(sbcoms) WITH mailto.
      ELSE.
        MESSAGE s022(so).
      ENDIF.

*    ---------------exception handling ------------------------
    CATCH cx_bcs INTO bcs_exception.
      MESSAGE i865(so) WITH bcs_exception->error_type.
  ENDTRY.
ENDFORM.                    " FRM_SEND_EMAIL
運行效果：
附件：其中fldate列使用了格式
參考：
https://blogs.sap.com/2015/08/30/how-to-generate-a-formatted-excel-in-background-and-send-it-as-an-email-using-ixml-method/
https://blogs.sap.com/2020/04/07/formatted-excel-using-xml/
posted @ 2024-01-10 20:08 鯨與海閱讀(705) 評論(0) 收藏舉報
刷新頁面返回頂部
鯨與海

ABAP內表轉xml文件以Excel形式發送郵件

公告
