I was working in one Project and there came a requirement that if a customer process any output form , the output should be displayed as formatted excel and if given a print it should send the excel as email in background.
Now we had traditional ways of doing in SAP
- 1. 1. Without any formatting you could get all the data into an internal table and create an excel.This could be attained by use of this function module . The limitation here is we can’t format make any columns bold or can’t colour the data. If we want to just populate some data in rows and column and want to send it as an email ,we could achieve this easily.
- 2. 2. Second way of a formatted excel is using OLE method ( Object link Enable ) method in which we have all the functions to create a formatted excel with colors and bold orders. But the limitation here is we have to generate the output in foreground ( presentation server ) , it takes time as contents gets created at your presentation server. So the limitations are no background processing and performance issue as time take in dyamic content creation
Now I had a challenge that what could be the best way to generate a formatted excel and also process the output in background and send it as excel attachment to the desired recipients.
So I had digged out here and there and found that SAP has added the concept of ‘IXML’ in 2010 in their library functions and we could actually generate the formatted excel and also send as an email attachment. Infact SCN was very helpful I could find the generation of excel , however I din’t find a good source code which process the IXML data in background and send as an email attachment.
I hope it would be good for all those who are looking to implement such functionality .
Please let me know your feedback , suggestions and query further to this.
**** Decalaration Please go through the text file for decalartion part *******
*** When ever you write logic for an output form you have to define the entry node , when it is called from NACE.
Get all the data you require based on your functional requirements to process the data
** GT_FINAL is my internal table , where I had all the data which has to populated in my output. There are around 18 columns ( Fields ) in which data is being copied.
** Get file path is when you want the user an option to see the output/save the output in their desktop/presentation server you have to specify where you want to place the file in their system. So we use the below function module **
** Distributing the whole program in a modular way , calling the subroutines for specific functionality , as we do in conventional way.**
*&---------------------------------------------------------------------*
*& Form PROCESSING
*&---------------------------------------------------------------------*
FORM processing.
PERFORM get_data. "
*** PROCESS XML DATA IS THE MOST IMPORTANT SUB ROUTINE WHICH TELL US HOW WE CAN USE OUR INTERNAL TABLE DATA , FORMAT IT and GENERATE THE FORMATTED EXCEl ***
PERFORM process_xml_data. " Create excel through xml code for sending as an attachment
PERFORM get_file_path. " Get the file path
PERFORM download_excel. “Download the processed excel in desktop
*** HOW TO PROCESS THE FORMATTED EXCEL IN BACKGROUND and SEND as an EXCEL ATTACHMENT ***
PERFORM send_mail1. “Sending the excel output via mail
ENDFORM. " PROCESSING
*&---------------------------------------------------------------------*
*& Form PROCESS_XML_DATA
*&---------------------------------------------------------------------*
* Process the xml data to create an excel
*----------------------------------------------------------------------*
FORM process_xml_data .
* Creating a ixml Factory
g_ixml = cl_ixml=>create( ).
* Creating the DOM Object Model
g_document = g_ixml->create_document( ).
*** Little help on the IXML Hierarchy ****
** Create a workbook **
**Set attributes for the workbook **
** Create Node for document **
** We need to attach a style to every node **
** Attributes of style would be setting up the alignment , font , bold , color etc ***
** Style Syntax : ID and give name , create different styles if properties are different for different rows **
** For every cell of excel you need to specify four borders , right , left , bottom and top **
** Create a table rows * Columns **
** Create Rows , attach the style to the row and define the properties **
** Similarly do for column **
** All syntax given in the document below **
* Create Root Node 'Workbook'
g_element_root = g_document->create_simple_element( name = 'Workbook' parent = g_document ).
g_element_root->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:spreadsheet' ).
g_ns_attribute = g_document->create_namespace_decl( name = 'ss' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:spreadsheet' ).
g_element_root->set_attribute_node( g_ns_attribute ).
g_ns_attribute = g_document->create_namespace_decl( name = 'x' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:excel' ).
g_element_root->set_attribute_node( g_ns_attribute ).
* Create node for document properties.
r_element_properties = g_document->create_simple_element( name = 'TEST_REPORT' parent = g_element_root ).
g_value = sy-uname.
g_document->create_simple_element( name = 'Author' value = g_value parent = r_element_properties ).
* Styles
r_styles = g_document->create_simple_element( name = 'Styles' parent = g_element_root ).
* Style for Header
r_style = g_document->create_simple_element( name = 'Style' parent = r_styles ).
r_style->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Header' ).
r_format = g_document->create_simple_element( name = 'Font' parent = r_style ).
r_format->set_attribute_ns( name = 'Bold' prefix = 'ss' value = '1' ).
r_format = g_document->create_simple_element( name = 'Interior' parent = r_style ).
r_format->set_attribute_ns( name = 'Color' prefix = 'ss' value = '#FFFFFF' ).
r_format->set_attribute_ns( name = 'Pattern' prefix = 'ss' value = 'Solid' ).
r_format = g_document->create_simple_element( name = 'Alignment' parent = r_style ).
r_format->set_attribute_ns( name = 'Vertical' prefix = 'ss' value = 'Center' ).
r_format->set_attribute_ns( name = 'WrapText' prefix = 'ss' value = '1' ).
r_border = g_document->create_simple_element( name = 'Borders' parent = r_style ).
r_format = g_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->set_attribute_ns( name = 'Weight' prefix = 'ss' value = '1' ).
r_style = g_document->create_simple_element( name = 'Style' parent = r_styles ).
r_style->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Data' ).
** Create a new style for Delivery Number
r_style5 = g_document->create_simple_element( name = 'Style' parent = r_styles ).
r_style5->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Data5' ).
r_format = g_document->create_simple_element( name = 'Font' parent = r_style5 ).
r_format->set_attribute_ns( name = 'Bold' prefix = 'ss' value = '1' ).
r_format = g_document->create_simple_element( name = 'Interior' parent = r_style5 ).
r_format->set_attribute_ns( name = 'Color' prefix = 'ss' value = '#1E90FF' ).
r_format->set_attribute_ns( name = 'Pattern' prefix = 'ss' value = 'Solid' ).
r_format = g_document->create_simple_element( name = 'Alignment' parent = r_style5 ).
r_format->set_attribute_ns( name = 'Vertical' prefix = 'ss' value = 'Center' ).
r_format->set_attribute_ns( name = 'WrapText' prefix = 'ss' value = '1' ).
r_border = g_document->create_simple_element( name = 'Borders' parent = r_style5 ).
* * Border Bottom
r_format = g_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' ).
** Create Borders Bottom Left , top , right in the same way ***
r_format = g_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' ).
* * Top
r_format = g_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').
** Right
r_format = g_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' ).
**** STYLE CREATION OVER , Different styles created which we will use belwo ***
** Creating borders for the cell
r_border = g_document->create_simple_element( name = 'Borders' parent = r_style1 ).
r_format = g_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 = g_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 = g_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 = g_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 = g_document->create_simple_element( name = 'Worksheet' parent = g_element_root ).
r_worksheet->set_attribute_ns( name = 'Name' prefix = 'ss' value = 'Proforma Confirmation' ). " Add the worksheet name (ERDK954626)
*** Define the table , Rows * Coumns. This would automatically created based on the data in our internal table ***
* Table
r_table = g_document->create_simple_element( name = 'Table' parent = r_worksheet ). " Set Table properties
r_table->set_attribute_ns( name = 'FullColumns' prefix = 'x' value = '1' ).
r_table->set_attribute_ns( name = 'FullRows' prefix = 'x' value = '1' ).
* Column Formatting
r_column = g_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '220' ).
r_column = g_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '80' ).
** Repeat based on how many columns in you want in your output excel **
** CREATING ROWS **
* Blank Row
r_row = g_document->create_simple_element( name = 'Row' parent = r_table ).
r_row = g_document->create_simple_element( name = 'Row' parent = r_table ).
r_row = g_document->create_simple_element( name = 'Row' parent = r_table ).
r_cell = g_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'Index' prefix = 'ss' value = '1' ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data5' ).
WRITE g_delivno TO g_delivno NO-ZERO. " removing zeroes from delivery number
CONCATENATE 'ТОВАРНАЯ НАКЛАДНАЯ №'(050) g_delivno INTO g_value SEPARATED BY ' : '.
r_data = g_document->create_simple_element( name = 'Data' value = g_value parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
r_row = g_document->create_simple_element( name = 'Row' parent = r_table ).
r_row = g_document->create_simple_element( name = 'Row' parent = r_table ).
** Blank Row after Column Headers
r_row = g_document->create_simple_element( name = 'Row' parent = r_table ).
r_row->set_attribute_ns( name = 'Height' prefix = 'ss' value = '40' ).
* Column Headers Row
** First Column - Material
r_cell = g_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'Index' prefix = 'ss' value = '1' ).
r_cell->set_attribute_ns( name = 'MergeDown' prefix = 'ss' value = '1' ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data3' ).
r_data = g_document->create_simple_element( name = 'Data' value = ' Товар, наименование, характеристика, сорт, артикул товара ' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
*e = 'String' ).
** Attribute for filling the cell numbers in a row
** Printing the column numbers in the excel sent via email
r_row = g_document->create_simple_element( name = 'Row' parent = r_table ).
r_row->set_attribute_ns( name = 'Height' prefix = 'ss' value = '20' ).
r_cell = g_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'Index' prefix = 'ss' value = '1' ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data3' ).
r_data = g_document->create_simple_element( name = 'Data' value = '1' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
** Loop and pass the contents to this excel ***
* Data Table
LOOP AT gt_final INTO gst_final.
** Creation of a row for data
r_row = g_document->create_simple_element( name = 'Row' parent = r_table ).
r_row->set_attribute_ns( name = 'Height' prefix = 'ss' value = '30' ).
* Material value.
r_cell = g_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data4' ).
g_value = gst_final-matnr.
r_data = g_document->create_simple_element( name = 'Data' value = g_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ). " Cell format
x = 'ss' value = 'String' ).
*** Repeat the same for all values you want to pass **
ENDLOOP.
* Creating a Stream Factory
g_streamfactory = g_ixml->create_stream_factory( ).
* Connect Internal XML Table to Stream Factory
g_ostream = g_streamfactory->create_ostream_itable( table = g_xml_table ).
* Rendering the Document
g_renderer = g_ixml->create_renderer( ostream = g_ostream document = g_document ).
g_rc = g_renderer->render( ).
* Saving the XML Document
g_xml_size = g_ostream->get_num_written_raw( ).
** Moving the ixml data into an internal table **
** Pass the processed xml data to table for download
LOOP AT g_xml_table INTO gst_xml.
CLEAR gt_objbin.
gt_objbin-line = gst_xml-data.
APPEND gt_objbin.
ENDLOOP.
ENDFORM. " PROCESS_XML_DATA
SENDING EMAIL in BACKGROUND the formatted EXCEL
*&---------------------------------------------------------------------*
*& Form SEND_MAIL1
*&---------------------------------------------------------------------*
FORM send_mail1 .
DATA: l_objpack LIKE sopcklsti1 OCCURS 2 WITH HEADER LINE.
DATA: l_objhead LIKE solisti1 OCCURS 1 WITH HEADER LINE.
DATA: l_objbin LIKE solix OCCURS 10 WITH HEADER LINE.
DATA: l_objtxt LIKE solisti1 OCCURS 10 WITH HEADER LINE.
DATA: l_reclist LIKE somlreci1 OCCURS 5 WITH HEADER LINE.
DATA: l_doc_chng LIKE sodocchgi1.
DATA: l_tab_lines LIKE sy-tabix.
DATA: l_num(3).
DATA: l_subj_date(10) TYPE c.
* Mail Subject
CLEAR l_doc_chng-obj_descr.
WRITE g_delivno TO g_delivno NO-ZERO.
CONCATENATE 'Подтверждение отгрузки Hasbro: ТОРГ-12 №'(025) g_delivno INTO l_doc_chng-obj_descr SEPARATED BY space.
* Mail Contents
l_objtxt = 'Уважаемый Клиент,'(026).
APPEND l_objtxt.
CLEAR l_objtxt.
APPEND l_objtxt.
WRITE g_delivno TO g_delivno NO-ZERO.
CONCATENATE 'В приложении Подтверждение об отгрузке Вашего заказа №'(027)'' INTO l_objtxt SEPARATED BY space. " Mail Contents
APPEND l_objtxt.
CONCATENATE gw_vbkd-bstkd '.' INTO l_objtxt. " Added in Customer PO in 2nd Line
APPEND l_objtxt.
CONCATENATE 'ТОРГ-12 № '(051) g_delivno INTO l_objtxt SEPARATED BY space. " Added Hasbro Deliv no in 3rd Line
APPEND l_objtxt.
CLEAR l_objtxt.
APPEND l_objtxt.
CLEAR l_objtxt.
APPEND l_objtxt.
CLEAR l_objtxt.
APPEND l_objtxt.
l_objtxt = 'С уважением и благодарностью,'(028).
APPEND l_objtxt.
l_objtxt = 'от лица ООО «Хасбро Раша»'(052). ":sy-uname.
APPEND l_objtxt.
CLEAR l_objtxt.
APPEND l_objtxt.
l_objtxt = 'Сообщение было сформировано и отправлено автоматически, пожалуйста, не отвечайте на него.'(054) . " ERDK954925
APPEND l_objtxt.
DESCRIBE TABLE l_objtxt LINES l_tab_lines.
READ TABLE l_objtxt INDEX l_tab_lines.
l_doc_chng-doc_size = ( l_tab_lines - 1 ) * 255 + STRLEN( l_objtxt ).
* Packing List For the E-mail Body
l_objpack-head_start = 1.
l_objpack-head_num = 0.
l_objpack-body_start = 1.
l_objpack-body_num = l_tab_lines.
l_objpack-doc_type = 'RAW'.
APPEND l_objpack.
* Creation of the Document Attachment
LOOP AT g_xml_table INTO gst_xml1.
CLEAR l_objbin.
l_objbin-line = gst_xml1-data.
APPEND l_objbin.
ENDLOOP.
DESCRIBE TABLE l_objbin LINES l_tab_lines.
l_objhead = 'Proforma Confirmation '.
APPEND l_objhead.
* Packing List For the E-mail Attachment
l_objpack-transf_bin = 'X'.
l_objpack-head_start = 1.
l_objpack-head_num = 0.
l_objpack-body_start = 1.
l_objpack-body_num = l_tab_lines.
** Create attachment name always as the previous condition fails in case of multiple deliveries
PERFORM create_attachment_name. “ Name of the worksheet what you are trying to create **
* CONCATENATE 'Proforma Confirmation for Delivery '(033) l_subj_date INTO l_objpack-obj_descr SEPARATED BY space.
l_objpack-obj_descr = g_excel_file1.
l_objpack-doc_type = 'XLS'.
l_objpack-doc_size = l_tab_lines * 255.
APPEND l_objpack.
* Target Recipent
IF gt_email[] IS NOT INITIAL.
LOOP AT gt_email INTO gst_email. " changed the internal table for multiple emails's
CLEAR l_reclist.
IF gst_email-smtp_addr IS NOT INITIAL. " pass the email address only to work area
* create recipient object
l_reclist-receiver = gst_email-smtp_addr . " Appending the email address
l_reclist-rec_type = 'U'.
APPEND l_reclist.
ENDIF.
CLEAR gst_email. " Added a clear statement for the new work area
ENDLOOP.
ENDIF.
*** This is the FM which faciiates the data and send it to targeted recipients **
* Sending the document
CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'
EXPORTING
document_data = l_doc_chng
put_in_outbox = 'X'
TABLES
packing_list = l_objpack
object_header = l_objhead
contents_txt = l_objtxt
contents_hex = l_objbin
receivers = l_reclist
EXCEPTIONS
too_many_receivers = 1
document_not_sent = 2
operation_no_authorization = 4
OTHERS = 99.
ENDFORM. " SEND_MAIL1
Output :
- SAP Managed Tags:
24 Comments
| User | Count |
|---|---|
| 6 | |
| 5 | |
| 3 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 1 | |
| 1 |
