04-07-2017 6:09 AM
* Output column headings to active excel sheet.
PERFORM fill_cell USING 1 1 1 200 TEXT-140.
PERFORM fill_cell USING 1 2 1 200 TEXT-141.
PERFORM fill_cell USING 1 3 1 200 TEXT-142.
PERFORM fill_cell USING 1 4 1 200 TEXT-143.
PERFORM fill_cell USING 1 5 1 200 TEXT-144.
PERFORM fill_cell USING 1 6 1 109 lv_date_from.
PERFORM fill_cell USING 1 7 1 109 lv_date_to.
PERFORM fill_cell USING 1 8 1 109 TEXT-164.
PERFORM fill_cell USING 1 9 1 109 TEXT-165.
PERFORM fill_cell USING 1 10 1 109 TEXT-147.
PERFORM fill_cell USING 1 11 1 109 TEXT-148.
PERFORM fill_cell USING 1 12 1 109 TEXT-149.
PERFORM fill_cell USING 1 13 1 109 TEXT-150.
PERFORM fill_cell USING 1 14 1 109 TEXT-151.
PERFORM fill_cell USING 1 15 1 109 TEXT-152.
PERFORM fill_cell USING 1 16 1 109 TEXT-153.
*&---------------------------------------------------------------------*
*& Form FILL_CELL
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
* -->P_1 text
* -->P_1 text
* -->P_1 text
* -->P_109 text
* -->P_TEXT_140 text
*&---------------------------------------------------------------------*
FORM fill_cell USING i j bold col
val.
DATA:h_excel TYPE ole2_object, " Excel object
h_mapl TYPE ole2_object, " list of workbooks
h_map TYPE ole2_object, " workbook
h_zl TYPE ole2_object, " cell
h_f TYPE ole2_object, " font
h_c TYPE ole2_object. " color
CALL METHOD OF h_excel 'Cells' = h_zl
EXPORTING
#1 = i
#2 = j.
SET PROPERTY OF h_zl 'Value' = val .
GET PROPERTY OF h_zl 'Font' = h_f.
SET PROPERTY OF h_f 'Bold' = bold .
SET PROPERTY OF h_f 'Color' = col.
ENDFORM.
<br>
TEXT-140 "'Storage LOC'
TEXT-141 "'Plant'
TEXT-142 "'Vendor'
TEXT-143 "'Material'
TEXT-144 "'Description'
lv_date_from "'Stock on 0000.01.01'
lv_date_to "'Stock on 9999.12.12'
TEXT-164 " Total Receipts
TEXT-165 " Total Issues
TEXT-147 "'Mvt Type'
TEXT-148 "'Spl Stock'
TEXT-149 "'Material Doc'
TEXT-150 "'Item'
TEXT-151 "'Posting Date'
TEXT-152 "'Quantity'
TEXT-153 "'Unit'
Hi All
I have a requirement where i need to include borders in the excel sheet of the attachment and send mail to vendor.
The functionality is :
1) The ALV grid report is displayed with check boxes.
2) The selected check boxes is the details to be sent to vendor.
3) Once the checkboxes are selected there is a push button to select the email of the vendor and send mail.
4) Vendor will receive the mail with the attachment.
The mail is being sent with the excel. But the functional consultant wants the borders as well in the file. Which i am finding it difficult to implement.
I have tried the OLE method and written the code for headers as pasted. and in a similar way done for the item level also looping the final internal table.
Could you please tell me where i am wrong? Is there any corrections i need to make? Also what does 1 1 1 200 stand for in the Perform statements.
Kindly help me in this.
04-11-2017 8:02 AM
Hi ,
I used XML technique to resolve this report.
For people who are using this technique kindly use this its very helpful.
PERFORM process_xml_data.
FORM process_xml_data .
".............For formatted excel conversion............................
TYPES: BEGIN OF xml_line,
data(255) TYPE x,
END OF xml_line.
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,
ns_attribute TYPE REF TO if_ixml_attribute,
r_element_properties 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,
l_type TYPE string,
l_text(100) TYPE c,
r_styles TYPE REF TO if_ixml_element,
r_style TYPE REF TO if_ixml_element,
r_style3 TYPE REF TO if_ixml_element,
r_format TYPE REF TO if_ixml_element,
r_border TYPE REF TO if_ixml_element,
num_rows TYPE i.
DATA : l_xml_table1 TYPE TABLE OF xml_line,
l_xml_table2 TYPE TABLE OF xml_line,
wa_xml TYPE xml_line,
l_xml_size TYPE i,
l_rc TYPE i,
lv_budat(10) TYPE c.
DATA: lv_date_from(20) TYPE c,
lv_date_to(20) TYPE c.
* MAIL SENDING DECLARATIONS
DATA : ls_data TYPE sodocchgi1,
lt_mailtext TYPE TABLE OF solisti1,
ls_mailtext LIKE LINE OF lt_mailtext,
ls_belege TYPE stype_belege,
ls_belege1 TYPE stype_belege,
lt_pack_list TYPE TABLE OF sopcklsti1,
ls_pack_list TYPE sopcklsti1,
lt_mailrec TYPE TABLE OF somlrec90,
ls_mailrec TYPE somlrec90,
lt_mailatt TYPE TABLE OF solisti1,
* ls_mailatt1 LIKE LINE OF lt_mailatt,
ls_mailatt1 LIKE LINE OF gt_belege1_mail,
ls_mailatt TYPE solisti1,
lv_cnt TYPE i,
lc_f TYPE c VALUE 'F',
lc_u TYPE c VALUE 'U',
lc_xls TYPE char3 VALUE 'XLS', "Excel
lc_file1 TYPE char5 VALUE 'FILE1', "File name
lc_raw TYPE char3 VALUE 'RAW', "File format
lc_int TYPE char3 VALUE 'INT', "External mail
lc_x TYPE c VALUE 'X',
lc_a TYPE c VALUE 'A',
reclist LIKE somlreci1 OCCURS 5 WITH HEADER LINE,
lv_lifnr TYPE lifnr,
lv_menge TYPE char17,
lv_endmenge TYPE char17,
lv_anfmenge TYPE char17,
lv_soll TYPE char17,
lv_haben TYPE char17,
lv_erfmg TYPE char17,
lv_bwart TYPE char3,
lv_sobkz TYPE char1,
lv_mblnr TYPE char10,
lv_zeile TYPE char4,
lv_meins TYPE char3,
lv_tabix TYPE sy-tabix,
lv_mailid TYPE string,
h TYPE i.
DATA : lt_objbin LIKE solix OCCURS 10 WITH HEADER LINE.
DATA : lt_objbin1 LIKE solix OCCURS 10 WITH HEADER LINE.
DATA : ls_sval TYPE sval,
lt_sval TYPE STANDARD TABLE OF sval,
sreturn TYPE char2,
lt_return TYPE TABLE OF ddshretval.
* Creating a ixml Factory
l_ixml = cl_ixml=>create( ).
* Creating the DOM Object Model
l_document = l_ixml->create_document( ).
* 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
r_styles = l_document->create_simple_element( name = 'Styles' parent = l_element_root ).
* Style for Header
r_style = l_document->create_simple_element( name = 'Style' parent = r_styles ).
r_style->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Header' ).
r_format = l_document->create_simple_element( name = 'Font' parent = r_style ).
r_format->set_attribute_ns( name = 'Bold' prefix = 'ss' value = '1' ).
r_format = l_document->create_simple_element( name = 'Interior' parent = r_style ).
r_format->set_attribute_ns( name = 'Color' prefix = 'ss' value = '#92D050' ).
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 = 'Vertical' prefix = 'ss' value = 'Center' ).
r_format->set_attribute_ns( name = 'WrapText' prefix = 'ss' value = '1' ).
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' ).
* Style for Data
r_style3 = l_document->create_simple_element( name = 'Style' parent = r_styles ).
r_style3->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Data' ).
r_border = l_document->create_simple_element( name = 'Borders' parent = r_style3 ).
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' ).
* Column Formatting
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '40' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '100' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '140' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '40' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '90' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '50' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '55' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '65' ).
* Blank Row
r_row = l_document->create_simple_element( name = 'Row' parent = r_table ).
IF datum[] IS INITIAL.
lv_date_from = TEXT-145.
lv_date_to = TEXT-013.
ELSE.
CONCATENATE 'Stock On-' datum-low(4)'.'datum-low+4(2)'.'datum-low+6(2) INTO lv_date_from.
CONCATENATE 'Stock On-' datum-high(4)'.'datum-high+4(2)'.'datum-high+6(2) INTO lv_date_to.
ENDIF.
* 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' ).
* Stor.Loc.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Storage Loc' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Plant
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Plant' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Vendor
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Vendor' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Material
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Material' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Description
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Description' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Stock on from date
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Stock on start date' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Stock on to date
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Stock on end date' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Total Receipts
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Total Receipts' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Total Issues
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Total Issues' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Mvt Typ
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Mvt Type' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Special Stock
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Spl Stock' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Material Doc
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Material Doc' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Item
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Item' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Posting date
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Posting Date' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Quantity
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Quantity' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Unit
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Unit' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Data Table
LOOP AT gt_belege1_mail ASSIGNING FIELD-SYMBOL(<ls_xml_att>).
r_row = l_document->create_simple_element( name = 'Row' parent = r_table ).
* Storage Loc.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-lgort.
CONDENSE l_value NO-GAPS.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ). " Cell forma
* Plant.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-werks.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Vendor.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-lifnr.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Material.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-matnr.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Description.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-maktx.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Stock on Start Date Quantity.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-anfmenge.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Stock on End Date Quantity.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-endmenge.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Total Receipts.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-soll.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Total Issues.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-haben.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Mvt Type.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-bwart.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Spl Stock.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-sobkz.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Material Doc.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-mblnr.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Item.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-zeile.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Posting Date.
CONCATENATE <ls_xml_att>-budat(4)'.'<ls_xml_att>-budat+4(2)'.'<ls_xml_att>-budat+6(2) INTO lv_budat.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = lv_budat.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Quantity.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-menge.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Unit.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-meins.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
ENDLOOP.
* 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_table1 ).
* 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( ).
".............................Attachment Begin............................
**Fetching email ids from table ZT0206
SELECT * FROM zt0206 INTO TABLE @DATA(lt_emailid)
FOR ALL ENTRIES IN @gt_belege1_check
WHERE lifnr = @gt_belege1_check-lifnr
AND werks = @gt_belege1_check-werks.
* Check for mail ids not maintained in table
IF lt_emailid IS INITIAL AND sy-subrc <> 0.
MESSAGE TEXT-162 TYPE 'E' DISPLAY LIKE 'S'.
ENDIF.
* F4 help for fetching mail ids from table ZT0206
CALL FUNCTION 'F4IF_INT_TABLE_VALUE_REQUEST'
EXPORTING
* DDIC_STRUCTURE = ' '
retfield = 'ZZMAILID'
* PVALKEY = ' '
dynpprog = sy-repid
dynpnr = sy-dynnr
dynprofield = 'EmailID '
* STEPL = 0
window_title = 'Email id of Vendor'
* VALUE = ' '
value_org = 'S'
multiple_choice = 'X'
* DISPLAY = ' '
* CALLBACK_PROGRAM = ' '
* CALLBACK_FORM = ' '
* CALLBACK_METHOD =
* MARK_TAB =
* IMPORTING
* USER_RESET =
TABLES
value_tab = lt_emailid
* FIELD_TAB =
return_tab = lt_return
* DYNPFLD_MAPPING =
EXCEPTIONS
parameter_error = 1
no_values_found = 2
OTHERS = 3.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
**
IF lt_return IS NOT INITIAL.
CLEAR :ls_mailtext.
*Body of the message
ls_mailtext-line = TEXT-156 .
APPEND ls_mailtext TO lt_mailtext.
CLEAR ls_mailtext.
ls_mailtext-line = TEXT-157 .
APPEND ls_mailtext TO lt_mailtext.
CLEAR ls_mailtext.
ls_mailtext-line = TEXT-158.
APPEND ls_mailtext TO lt_mailtext.
CLEAR ls_mailtext.
ls_mailtext-line = TEXT-159 .
APPEND ls_mailtext TO lt_mailtext.
CLEAR ls_mailtext.
DESCRIBE TABLE lt_mailtext LINES lv_cnt.
READ TABLE lt_mailtext INTO ls_mailtext INDEX lv_cnt.
ls_data-doc_size = ( lv_cnt - 1 ) * 255 + strlen( ls_mailtext ).
CLEAR: ls_data,ls_pack_list,ls_mailrec.
***Packing list
REFRESH lt_pack_list.
ls_pack_list-transf_bin = space.
ls_pack_list-head_start = 1.
ls_pack_list-head_num = 0.
ls_pack_list-body_start = 1.
ls_pack_list-body_num = lv_cnt.
ls_pack_list-doc_type = lc_raw.
APPEND ls_pack_list TO lt_pack_list.
* Creation of the Document Attachment
LOOP AT l_xml_table1 INTO wa_xml.
CLEAR lt_objbin.
lt_objbin-line = wa_xml-data.
APPEND lt_objbin.
ENDLOOP.
DESCRIBE TABLE lt_objbin LINES lv_cnt.
**Packing list for E-mail Attachment
ls_pack_list-transf_bin = lc_x.
ls_pack_list-head_start = 1.
ls_pack_list-head_num = 0.
ls_pack_list-body_start = 1.
ls_pack_list-body_num = lv_cnt.
ls_pack_list-doc_type = lc_xls.
ls_pack_list-obj_name = lc_file1.
ls_pack_list-obj_descr = lc_file1.
ls_pack_list-doc_size = ls_pack_list-body_num * 255.
APPEND ls_pack_list TO lt_pack_list.
*** Document Meta Data
ls_data-doc_size = 1.
ls_data-obj_name = TEXT-155.
ls_data-obj_langu = sy-langu.
ls_data-obj_descr = TEXT-154.
ls_data-sensitivty = lc_f.
DESCRIBE TABLE lt_mailtext LINES lv_cnt.
READ TABLE lt_mailtext INTO ls_mailtext INDEX lv_cnt.
ls_data-doc_size = ( lv_cnt - 1 ) * 255 + strlen( ls_mailtext ).
** DESCRIBE TABLE lt_mailatt LINES lv_cnt.
** READ TABLE lt_mailatt INTO ls_mailatt INDEX lv_cnt.
** ls_data-doc_size = ( lv_cnt - 1 ) * 255 + strlen( ls_mailatt ).
***Mail receivers
LOOP AT lt_return ASSIGNING FIELD-SYMBOL(<ls_return>).
ls_mailrec-rec_type = lc_u.
ls_mailrec-com_type = lc_int.
ls_mailrec-receiver = <ls_return>-fieldval. "lv_mailid.
ls_mailrec-notif_del = lc_x.
ls_mailrec-notif_ndel = lc_x.
** ls_mailrec-express = lc_x.
ls_mailrec-copy = lc_x.
APPEND ls_mailrec TO lt_mailrec.
ENDLOOP.
IF lt_return IS NOT INITIAL.
CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'
EXPORTING
document_data = ls_data
put_in_outbox = lc_x
commit_work = lc_x
TABLES
packing_list = lt_pack_list
* contents_bin = lt_mailatt
contents_txt = lt_mailtext
contents_hex = lt_objbin
receivers = lt_mailrec
EXCEPTIONS
too_many_receivers = 1
document_not_sent = 2
document_type_not_exist = 3
operation_no_authorization = 4
parameter_error = 5
x_error = 6
enqueue_error = 7
OTHERS = 8.
ENDIF.
MESSAGE TEXT-160 TYPE 'S'.
ELSE.
MESSAGE TEXT-161 TYPE 'S'.
ENDIF.
04-07-2017 7:29 AM
Rekha,
Those numbers represent the values that your are passing to I J BOLD COL VAL,respectively.
Regarding the colouring of headers in the excel attachment,I think you have to append the HTML code along with your excel header and their respective values and then convert that whole data into BINARY using the FM SCMS_TEXT_TO_BINARY and then use CL_BCS method to send that excel as attachment.
PERFORM fill_cell USING 1 "value for I
16 "value for J
1 "value for BOLD
109 "value for COL
TEXT-153. "value for VAL
FORM fill_cell USING I
J
BOLD
COL
VAL.
......
..........
..............
ENDFORM
04-07-2017 8:21 AM
Hi Kiran,
Thanks for your reply,
I am using the FM " SO_NEW_DOCUMENT_ATT_SEND_API1" to send the mail. Are the values put in the perform correct? Are i and j (Rows and columns respectively?
I had earlier also tried XML technique also but it did not quite work.
*Collect excel data
CLEAR ls_belege.
LOOP AT gt_belege1_mail INTO ls_belege1.
IF ls_belege1-soll NE 0.
lv_soll = ls_belege1-soll.
ENDIF.
IF ls_belege1-haben NE 0.
lv_haben = ls_belege1-haben.
ENDIF.
IF ls_belege1-endmenge NE 0.
lv_endmenge = ls_belege1-endmenge.
ENDIF.
IF ls_belege1-anfmenge NE 0.
lv_anfmenge = ls_belege1-anfmenge.
ENDIF.
lv_erfmg = ls_belege1-erfmg.
lv_bwart = ls_belege1-bwart.
lv_sobkz = ls_belege1-sobkz.
lv_mblnr = ls_belege1-mblnr.
lv_zeile = ls_belege1-zeile.
lv_menge = ls_belege1-menge.
lv_meins = ls_belege1-meins.
CONCATENATE ls_belege1-budat(4)'.'ls_belege1-budat+4(2)'.'ls_belege1-budat+6(2) INTO lv_budat.
CONCATENATE ls_belege1-lgort
ls_belege1-werks
ls_belege1-lifnr
ls_belege1-matnr
ls_belege1-maktx
lv_anfmenge
lv_endmenge
lv_soll
lv_haben
* lv_erfmg
lv_bwart
lv_sobkz
lv_mblnr
lv_zeile
lv_budat
lv_menge
lv_meins
INTO ls_mailatt-line SEPARATED BY
cl_abap_char_utilities=>horizontal_tab.
CONCATENATE cl_abap_char_utilities=>cr_lf ls_mailatt-line INTO
ls_mailatt-line.
APPEND ls_mailatt TO lt_mailatt.
CLEAR: ls_belege1,lv_menge,lv_erfmg,lv_bwart,
lv_sobkz,lv_mblnr,lv_zeile,
lv_budat,lv_menge,lv_meins,
ls_mailatt.
ENDLOOP.
LOOP AT lt_mailatt INTO ls_mailatt1.
h = sy-tabix + 1.
PERFORM fill_cell USING h h 1 1 ls_mailatt1-line.
MODIFY lt_mailatt FROM ls_mailatt1.
ENDLOOP.
04-11-2017 8:02 AM
Hi ,
I used XML technique to resolve this report.
For people who are using this technique kindly use this its very helpful.
PERFORM process_xml_data.
FORM process_xml_data .
".............For formatted excel conversion............................
TYPES: BEGIN OF xml_line,
data(255) TYPE x,
END OF xml_line.
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,
ns_attribute TYPE REF TO if_ixml_attribute,
r_element_properties 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,
l_type TYPE string,
l_text(100) TYPE c,
r_styles TYPE REF TO if_ixml_element,
r_style TYPE REF TO if_ixml_element,
r_style3 TYPE REF TO if_ixml_element,
r_format TYPE REF TO if_ixml_element,
r_border TYPE REF TO if_ixml_element,
num_rows TYPE i.
DATA : l_xml_table1 TYPE TABLE OF xml_line,
l_xml_table2 TYPE TABLE OF xml_line,
wa_xml TYPE xml_line,
l_xml_size TYPE i,
l_rc TYPE i,
lv_budat(10) TYPE c.
DATA: lv_date_from(20) TYPE c,
lv_date_to(20) TYPE c.
* MAIL SENDING DECLARATIONS
DATA : ls_data TYPE sodocchgi1,
lt_mailtext TYPE TABLE OF solisti1,
ls_mailtext LIKE LINE OF lt_mailtext,
ls_belege TYPE stype_belege,
ls_belege1 TYPE stype_belege,
lt_pack_list TYPE TABLE OF sopcklsti1,
ls_pack_list TYPE sopcklsti1,
lt_mailrec TYPE TABLE OF somlrec90,
ls_mailrec TYPE somlrec90,
lt_mailatt TYPE TABLE OF solisti1,
* ls_mailatt1 LIKE LINE OF lt_mailatt,
ls_mailatt1 LIKE LINE OF gt_belege1_mail,
ls_mailatt TYPE solisti1,
lv_cnt TYPE i,
lc_f TYPE c VALUE 'F',
lc_u TYPE c VALUE 'U',
lc_xls TYPE char3 VALUE 'XLS', "Excel
lc_file1 TYPE char5 VALUE 'FILE1', "File name
lc_raw TYPE char3 VALUE 'RAW', "File format
lc_int TYPE char3 VALUE 'INT', "External mail
lc_x TYPE c VALUE 'X',
lc_a TYPE c VALUE 'A',
reclist LIKE somlreci1 OCCURS 5 WITH HEADER LINE,
lv_lifnr TYPE lifnr,
lv_menge TYPE char17,
lv_endmenge TYPE char17,
lv_anfmenge TYPE char17,
lv_soll TYPE char17,
lv_haben TYPE char17,
lv_erfmg TYPE char17,
lv_bwart TYPE char3,
lv_sobkz TYPE char1,
lv_mblnr TYPE char10,
lv_zeile TYPE char4,
lv_meins TYPE char3,
lv_tabix TYPE sy-tabix,
lv_mailid TYPE string,
h TYPE i.
DATA : lt_objbin LIKE solix OCCURS 10 WITH HEADER LINE.
DATA : lt_objbin1 LIKE solix OCCURS 10 WITH HEADER LINE.
DATA : ls_sval TYPE sval,
lt_sval TYPE STANDARD TABLE OF sval,
sreturn TYPE char2,
lt_return TYPE TABLE OF ddshretval.
* Creating a ixml Factory
l_ixml = cl_ixml=>create( ).
* Creating the DOM Object Model
l_document = l_ixml->create_document( ).
* 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
r_styles = l_document->create_simple_element( name = 'Styles' parent = l_element_root ).
* Style for Header
r_style = l_document->create_simple_element( name = 'Style' parent = r_styles ).
r_style->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Header' ).
r_format = l_document->create_simple_element( name = 'Font' parent = r_style ).
r_format->set_attribute_ns( name = 'Bold' prefix = 'ss' value = '1' ).
r_format = l_document->create_simple_element( name = 'Interior' parent = r_style ).
r_format->set_attribute_ns( name = 'Color' prefix = 'ss' value = '#92D050' ).
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 = 'Vertical' prefix = 'ss' value = 'Center' ).
r_format->set_attribute_ns( name = 'WrapText' prefix = 'ss' value = '1' ).
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' ).
* Style for Data
r_style3 = l_document->create_simple_element( name = 'Style' parent = r_styles ).
r_style3->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Data' ).
r_border = l_document->create_simple_element( name = 'Borders' parent = r_style3 ).
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' ).
* Column Formatting
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '40' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '100' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '140' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '40' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '90' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '50' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '55' ).
r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).
r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '65' ).
* Blank Row
r_row = l_document->create_simple_element( name = 'Row' parent = r_table ).
IF datum[] IS INITIAL.
lv_date_from = TEXT-145.
lv_date_to = TEXT-013.
ELSE.
CONCATENATE 'Stock On-' datum-low(4)'.'datum-low+4(2)'.'datum-low+6(2) INTO lv_date_from.
CONCATENATE 'Stock On-' datum-high(4)'.'datum-high+4(2)'.'datum-high+6(2) INTO lv_date_to.
ENDIF.
* 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' ).
* Stor.Loc.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Storage Loc' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Plant
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Plant' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Vendor
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Vendor' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Material
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Material' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Description
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Description' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Stock on from date
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Stock on start date' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Stock on to date
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Stock on end date' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Total Receipts
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Total Receipts' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Total Issues
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Total Issues' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Mvt Typ
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Mvt Type' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Special Stock
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Spl Stock' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Material Doc
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Material Doc' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Item
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Item' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Posting date
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Posting Date' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Quantity
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Quantity' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Unit
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data' value = 'Unit' parent = r_cell ).
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Data Table
LOOP AT gt_belege1_mail ASSIGNING FIELD-SYMBOL(<ls_xml_att>).
r_row = l_document->create_simple_element( name = 'Row' parent = r_table ).
* Storage Loc.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-lgort.
CONDENSE l_value NO-GAPS.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ). " Cell forma
* Plant.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-werks.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Vendor.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-lifnr.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Material.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-matnr.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Description.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-maktx.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Stock on Start Date Quantity.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-anfmenge.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Stock on End Date Quantity.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-endmenge.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Total Receipts.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-soll.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Total Issues.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-haben.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Mvt Type.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-bwart.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Spl Stock.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-sobkz.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Material Doc.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-mblnr.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Item.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-zeile.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Posting Date.
CONCATENATE <ls_xml_att>-budat(4)'.'<ls_xml_att>-budat+4(2)'.'<ls_xml_att>-budat+6(2) INTO lv_budat.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = lv_budat.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Quantity.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-menge.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
* Unit.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = <ls_xml_att>-meins.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
ENDLOOP.
* 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_table1 ).
* 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( ).
".............................Attachment Begin............................
**Fetching email ids from table ZT0206
SELECT * FROM zt0206 INTO TABLE @DATA(lt_emailid)
FOR ALL ENTRIES IN @gt_belege1_check
WHERE lifnr = @gt_belege1_check-lifnr
AND werks = @gt_belege1_check-werks.
* Check for mail ids not maintained in table
IF lt_emailid IS INITIAL AND sy-subrc <> 0.
MESSAGE TEXT-162 TYPE 'E' DISPLAY LIKE 'S'.
ENDIF.
* F4 help for fetching mail ids from table ZT0206
CALL FUNCTION 'F4IF_INT_TABLE_VALUE_REQUEST'
EXPORTING
* DDIC_STRUCTURE = ' '
retfield = 'ZZMAILID'
* PVALKEY = ' '
dynpprog = sy-repid
dynpnr = sy-dynnr
dynprofield = 'EmailID '
* STEPL = 0
window_title = 'Email id of Vendor'
* VALUE = ' '
value_org = 'S'
multiple_choice = 'X'
* DISPLAY = ' '
* CALLBACK_PROGRAM = ' '
* CALLBACK_FORM = ' '
* CALLBACK_METHOD =
* MARK_TAB =
* IMPORTING
* USER_RESET =
TABLES
value_tab = lt_emailid
* FIELD_TAB =
return_tab = lt_return
* DYNPFLD_MAPPING =
EXCEPTIONS
parameter_error = 1
no_values_found = 2
OTHERS = 3.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
**
IF lt_return IS NOT INITIAL.
CLEAR :ls_mailtext.
*Body of the message
ls_mailtext-line = TEXT-156 .
APPEND ls_mailtext TO lt_mailtext.
CLEAR ls_mailtext.
ls_mailtext-line = TEXT-157 .
APPEND ls_mailtext TO lt_mailtext.
CLEAR ls_mailtext.
ls_mailtext-line = TEXT-158.
APPEND ls_mailtext TO lt_mailtext.
CLEAR ls_mailtext.
ls_mailtext-line = TEXT-159 .
APPEND ls_mailtext TO lt_mailtext.
CLEAR ls_mailtext.
DESCRIBE TABLE lt_mailtext LINES lv_cnt.
READ TABLE lt_mailtext INTO ls_mailtext INDEX lv_cnt.
ls_data-doc_size = ( lv_cnt - 1 ) * 255 + strlen( ls_mailtext ).
CLEAR: ls_data,ls_pack_list,ls_mailrec.
***Packing list
REFRESH lt_pack_list.
ls_pack_list-transf_bin = space.
ls_pack_list-head_start = 1.
ls_pack_list-head_num = 0.
ls_pack_list-body_start = 1.
ls_pack_list-body_num = lv_cnt.
ls_pack_list-doc_type = lc_raw.
APPEND ls_pack_list TO lt_pack_list.
* Creation of the Document Attachment
LOOP AT l_xml_table1 INTO wa_xml.
CLEAR lt_objbin.
lt_objbin-line = wa_xml-data.
APPEND lt_objbin.
ENDLOOP.
DESCRIBE TABLE lt_objbin LINES lv_cnt.
**Packing list for E-mail Attachment
ls_pack_list-transf_bin = lc_x.
ls_pack_list-head_start = 1.
ls_pack_list-head_num = 0.
ls_pack_list-body_start = 1.
ls_pack_list-body_num = lv_cnt.
ls_pack_list-doc_type = lc_xls.
ls_pack_list-obj_name = lc_file1.
ls_pack_list-obj_descr = lc_file1.
ls_pack_list-doc_size = ls_pack_list-body_num * 255.
APPEND ls_pack_list TO lt_pack_list.
*** Document Meta Data
ls_data-doc_size = 1.
ls_data-obj_name = TEXT-155.
ls_data-obj_langu = sy-langu.
ls_data-obj_descr = TEXT-154.
ls_data-sensitivty = lc_f.
DESCRIBE TABLE lt_mailtext LINES lv_cnt.
READ TABLE lt_mailtext INTO ls_mailtext INDEX lv_cnt.
ls_data-doc_size = ( lv_cnt - 1 ) * 255 + strlen( ls_mailtext ).
** DESCRIBE TABLE lt_mailatt LINES lv_cnt.
** READ TABLE lt_mailatt INTO ls_mailatt INDEX lv_cnt.
** ls_data-doc_size = ( lv_cnt - 1 ) * 255 + strlen( ls_mailatt ).
***Mail receivers
LOOP AT lt_return ASSIGNING FIELD-SYMBOL(<ls_return>).
ls_mailrec-rec_type = lc_u.
ls_mailrec-com_type = lc_int.
ls_mailrec-receiver = <ls_return>-fieldval. "lv_mailid.
ls_mailrec-notif_del = lc_x.
ls_mailrec-notif_ndel = lc_x.
** ls_mailrec-express = lc_x.
ls_mailrec-copy = lc_x.
APPEND ls_mailrec TO lt_mailrec.
ENDLOOP.
IF lt_return IS NOT INITIAL.
CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'
EXPORTING
document_data = ls_data
put_in_outbox = lc_x
commit_work = lc_x
TABLES
packing_list = lt_pack_list
* contents_bin = lt_mailatt
contents_txt = lt_mailtext
contents_hex = lt_objbin
receivers = lt_mailrec
EXCEPTIONS
too_many_receivers = 1
document_not_sent = 2
document_type_not_exist = 3
operation_no_authorization = 4
parameter_error = 5
x_error = 6
enqueue_error = 7
OTHERS = 8.
ENDIF.
MESSAGE TEXT-160 TYPE 'S'.
ELSE.
MESSAGE TEXT-161 TYPE 'S'.
ENDIF.
04-13-2017 10:07 AM
Hi,
To the same above code I included "Perform AAA using value" statements to the repetitive syntax and the number of lines are reduced. Every value i have passed a text element. I was suggested to use MACROS but i dint know how to implement it.
Regards,
Rekha