SAP内表数据作为带有格式的excel附件 发送邮件
从 Office2007开始, 当我们新建一个word、excel等文档时，后者遵循了一个开源的规范：Office openXML格式。
所以可以使用ABAP直接创建一个符合上述规范的XML，就可以得到相应的word、excel 用于下载或者作为邮件附件。
openXML介绍：https://baike.baidu.com/item/openXML/8399547?fr=aladdin
ABAP解析XML：https://www.cnblogs.com/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列使用了格式
参考：
