kiruthika Periyasamy

June 13, 2013 More than 30 minute read

Send Email with Mutliple tabs of excel as a single attachment using OLE

Objective

To send multiple tabs of excel sheet as a single attachment by email.

Introduction

This article is to send email with cell formatted excel attachment having multiple worksheets. This objective can be achieved by ole logic with creation of multiple sheets. This cannot be done by existing standard function modules. This can be created as Function module and we can use this function module wherever this requirement is needed by changing the import parameters. In this way, it is possible to improve the performance of the application by providing clarity of data in multiple tabs of excel sheet.

Steps

In this application, First create a Function Module with Receiver mail id as Import parameter.

Part 1

1. Create one Function Module

Go to SE37 -> Give name of the Function Module -> Create

Give Function group name ->Description -> Save

2. Then create I_mail_id of type ADR6-SMTP_ADDR as Import parameter , E_RETURN of type BAPIRET2 as Export parameter and T_TAB1 and T_TAB2 as tables parameter

3. Save object either in Package or in local object

4. Write logic in source code,

a) Use xml code and OLE logic

b) Create First Workbook

c) Then Create Styles for header, cell, Data

d) Then Create 1^st worksheet and Pass data to 1^st worksheet with styles as per requirements.

e) Then Create 2^nd worksheet and pass the another set of data to 2d worksheet as per specifications.

f) Attachment Data will be in i_xml_data , loop this i_xml_data and append this to objbin.

g) Then Call Mail sending Function Module ‘SO_NEW_DOCUMENT_ATT_SEND_API1’ and pass this attachment data objbin to contents_hex.

h) Pass Importing Parameter to Receiver Mail id of Function Module

i) Thus FM will send mail with multiple tabs of excel sheet as an attachment through email.

Sample Code

FUNCTION ZFM_SEND_MAIL_MULT_TABS.*”———————————————————————-*”*”Local Interface:*” IMPORTING*” REFERENCE(I_MAIL_ID) TYPE ADR6-SMTP_ADDR*” EXPORTING*” REFERENCE(E_RETURN) TYPE BAPIRET2
*”———————————————————————-
*”———————————————————————-
*”———————————————————————-
DATA: lv_date TYPE d.DATA: lv_filename TYPE string.
data : wa_tab1 type Ztab1,
wa_tab2 type Ztab2.

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_style1              TYPE REF TO if_ixml_element,
      r_style2              TYPE REF TO if_ixml_element,
      r_style3              TYPE REF TO if_ixml_element,
      r_style4              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_table       TYPE TABLE OF xml_line,
      wa_xml            TYPE xml_line,
      l_xml_size        TYPE i,
      l_rc              TYPE i.

DATA: d_value type p decimals 2,
      d_num_result(20) TYPE c,
      d_decimal(2)     TYPE c,
      d_number(20) type c.

lv_date = sy-datum – 1.

* 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 = ‘#FFFFFF’ ).
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 tablename
r_style2 = l_document->create_simple_element( name = ‘Style’ parent = r_styles ).
r_style2->set_attribute_ns( name = ‘ID’ prefix = ‘ss’ value = ‘Header1’ ).

r_format = l_document->create_simple_element( name = ‘Font’ parent = r_style2 ).
r_format->set_attribute_ns( name = ‘Bold’ prefix = ‘ss’ value = ‘1’ ).
r_format->set_attribute_ns( name = ‘Color’ prefix = ‘ss’ value = ‘#FFFFFF’ ).

r_format = l_document->create_simple_element( name = ‘Interior’ parent = r_style2 ).
r_format->set_attribute_ns( name = ‘Color’ prefix = ‘ss’ value = ‘#4F81BD’ ).
r_format->set_attribute_ns( name = ‘Pattern’ prefix = ‘ss’ value = ‘Solid’ ).

r_format = l_document->create_simple_element( name = ‘Alignment’ parent = r_style2 ).
r_format->set_attribute_ns( name = ‘Vertical’ prefix = ‘ss’ value = ‘Center’ ).
r_format->set_attribute_ns( name = ‘Horizontal’ 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_style2 ).
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 = ‘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 header2
r_style4 = l_document->create_simple_element( name = ‘Style’   parent = r_styles ).
r_style4->set_attribute_ns( name = ‘ID’ prefix = ‘ss’ value = ‘Header2’ ).

r_format = l_document->create_simple_element( name = ‘Font’ parent = r_style4 ).
r_format->set_attribute_ns( name = ‘Color’ prefix = ‘ss’ value = ‘#FFFFFF’ ).

r_format = l_document->create_simple_element( name = ‘Interior’ parent = r_style4 ).
r_format->set_attribute_ns( name = ‘Color’ prefix = ‘ss’ value = ‘#4F81BD’ ).
r_format->set_attribute_ns( name = ‘Pattern’ prefix = ‘ss’ value = ‘Solid’ ).

r_format = l_document->create_simple_element( name = ‘Alignment’ parent = r_style4 ).
r_format->set_attribute_ns( name = ‘Vertical’ prefix = ‘ss’ value = ‘Center’ ).
r_format->set_attribute_ns( name = ‘Horizontal’ 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_style4 ).
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 = ‘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 main title
r_style3 = l_document->create_simple_element( name = ‘Style’ parent = r_styles ).
r_style3->set_attribute_ns( name = ‘ID’ prefix = ‘ss’ value = ‘Main’ ).

r_format = l_document->create_simple_element( name = ‘Font’ parent = r_style3 ).
r_format->set_attribute_ns( name = ‘Bold’ prefix = ‘ss’ value = ‘1’ ).
r_format->set_attribute_ns( name = ‘Color’ prefix = ‘ss’ value = ‘#366092’ ).
******************* Style for Data
r_style1 = l_document->create_simple_element( name = ‘Style’ parent = r_styles ).
r_style1->set_attribute_ns( name = ‘ID’ prefix = ‘ss’ value = ‘Data’ ).

r_border = l_document->create_simple_element( name = ‘Borders’ parent = r_style1 ).
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 = ‘Alignment’ parent = r_style1 ).
r_format->set_attribute_ns( name = ‘Vertical’ prefix = ‘ss’ value = ‘Center’ ).
r_format->set_attribute_ns( name = ‘Horizontal’ prefix = ‘ss’ value = ‘Left’ ).
* Worksheet(First tab)
r_worksheet = l_document->create_simple_element( name = ‘Worksheet’ parent = l_element_root ).
r_worksheet->set_attribute_ns( name = ‘Name’ prefix = ‘ss’ value = ‘Commodity Impact’ ).
* 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 = ‘150’ ).

r_column = l_document->create_simple_element( name = ‘Column’ parent = r_table ).
r_column->set_attribute_ns( name = ‘Width’ prefix = ‘ss’ value = ‘150’ ).

r_column = l_document->create_simple_element( name = ‘Column’ parent = r_table ).
r_column->set_attribute_ns( name = ‘Width’ prefix = ‘ss’ value = ‘150’ ).
* Blank Row
r_row = l_document->create_simple_element( name = ‘Row’ parent = r_table ).
r_row->set_attribute_ns( name = ‘AutoFitHeight’ prefix = ‘ss’ value = ‘0’ ).

r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘MergeAcross’ prefix = ‘ss’ value = ‘3’ ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Main’ ).* r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘s78’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘Sheet 1-Table1’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).

r_row = l_document->create_simple_element( name = ‘Row’ parent = r_table ).
r_row->set_attribute_ns( name = ‘AutoFitHeight’ prefix = ‘ss’ value = ‘0’ ).
r_row->set_attribute_ns( name = ‘Height’ prefix = ‘ss’ value = ’27’ ).

r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘MergeAcross’ prefix = ‘ss’ value = ‘3’ ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Main’ ).* r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘s78’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘Data of 1st table’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).
*

r_row = l_document->create_simple_element( name = ‘Row’ parent = r_table ).
r_row->set_attribute_ns( name = ‘AutoFitHeight’ prefix = ‘ss’ value = ‘0’ ).

*Title of table

r_row = l_document->create_simple_element( name = ‘Row’ parent = r_table ).
r_row->set_attribute_ns( name = ‘AutoFitHeight’ prefix = ‘ss’ value = ‘1’ ).
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘MergeAcross’ prefix = ‘ss’ value = ‘2’ ).

r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header1’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘COMMODITY IMPACT’ 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’ ).*Commodity
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 = ‘Commodity’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).
* fy commodity
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 = ‘FY Commodity Spend ($MM)’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).
* change vs jan
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header’ ).
CONCATENATE ‘Change vs’ IW_FCYCL_P into l_value SEPARATED BY space.

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

* Blank Row after Column Headers
r_row = l_document->create_simple_element( name = ‘Row’ parent = r_table ).
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Data’ ).

r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Data’ ).

DATA: WA_INPUT TYPE P DECIMALS 8,

WA_OUTPUT TYPE P DECIMALS 2.
* Data Table
LOOP AT T_tab2 INTO wa_tab2.

    r_row = l_document->create_simple_element( name = ‘Row’ parent = r_table ).
* commodity
    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 = wa_tab2-/BIC/GPU_FDUOM1.
    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 format

WA_INPUT = wa_tab2-FY_COMM_SPND.
CALL FUNCTION ‘ROUND’

EXPORTING

INPUT = WA_INPUT

IMPORTING

OUTPUT = WA_OUTPUT.

* fy
    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 = WA_OUTPUT.
    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 format

WA_INPUT = wa_tab2-FIGCY_PREVMNTH.
CALL FUNCTION ‘ROUND’

EXPORTING

INPUT = WA_INPUT

IMPORTING

OUTPUT = WA_OUTPUT.

* changes vs jan
    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 = wa_output.
    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 format

clear wa_tab2.
ENDLOOP.

*************** * Worksheet2( second tab)
r_worksheet = l_document->create_simple_element( name = ‘Worksheet’ parent = l_element_root ).

r_worksheet->set_attribute_ns( name = ‘Name’ prefix = ‘ss’ value = ‘Tab2‘ ).
* 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 = ‘150’ ).

r_column = l_document->create_simple_element( name = ‘Column’ parent = r_table ).
r_column->set_attribute_ns( name = ‘Width’ prefix = ‘ss’ value = ‘150’ ).

r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘MergeAcross’ prefix = ‘ss’ value = ‘3’ ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Main’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘Atlas – PPV Forecast Transformation’ 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 ).
r_cell->set_attribute_ns( name = ‘MergeAcross’ prefix = ‘ss’ value = ‘3’ ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Main’ ).*   r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘s78’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘FEEDSTOCK COMMODITY IMPACT’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).
************ 2 Blank rows
r_row = l_document->create_simple_element( name = ‘Row’ parent = r_table ).
   r_row->set_attribute_ns( name = ‘AutoFitHeight’ prefix = ‘ss’ value = ‘0’ ).

r_row = l_document->create_simple_element( name = ‘Row’ parent = r_table ).
r_row->set_attribute_ns( name = ‘AutoFitHeight’ prefix = ‘ss’ value = ‘0’ ).

* 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’ ).
*Supplier.
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘SUPPLIER’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).
* SUBREGION
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘SUBREGION’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).
* MATERIAL VOLUME
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘MATERIAL VOLUME’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).
* COMMODITY
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘COMMODITY’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).
* FY_EXPOSURE
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘FY_EXPOSURE’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).

* UOM
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘UOM’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).

* CURRENT COMMODITY PRICE
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘CURRENT COMMODITY 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_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
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’ ).

* COMMODITY SPEND
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘COMMODITY SPEND’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).
* CHANGE VS PREVIOUS
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘CHANGE VS PREVIOUS’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).

* CREATION DATE(MONTH/YEAR)
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘CREATION DATE(MONTH/YEAR)’ parent = r_cell ).
r_data->set_attribute_ns( name = ‘Type’ prefix = ‘ss’ value = ‘String’ ).
* FIG CYCLE
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Header2’ ).
r_data = l_document->create_simple_element( name = ‘Data’ value = ‘FIG CYCLE’ 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 ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Data’ ).

r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Data’ ).*
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Data’ ).

r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Data’ ).

r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Data’ ).**
r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Data’ ).

r_cell = l_document->create_simple_element( name = ‘Cell’ parent = r_row ).
r_cell->set_attribute_ns( name = ‘StyleID’ prefix = ‘ss’ value = ‘Data’ ).

* Data Table
LOOP AT T_tab1 INTO wa_tab1.

    r_row = l_document->create_simple_element( name = ‘Row’ parent = r_table ).
* supplier
    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 = wa_tab1-/BIC/GPUCNTCT.
    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 format
* sub region
    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 = wa_tab1-SUBREGTXTLG.
    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 format
* material volume
    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 = wa_tab1-/BIC/GPUFSEJUL.
    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 format
* commodity
    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 = wa_tab1-/BIC/GPU_FDUOM.
    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 format

* Fy_exposure
    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 = wa_tab1-FY_EXPOSURE.
    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 format
*UOM
    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 = wa_tab1-/BIC/GPUUOM.
    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’ ).

* current commodity price
    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 = wa_tab1-/BIC/GPUCCPRIC.
    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’ ).
*   currency
    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 = wa_tab1-CURRENCY.
    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’ ).

WA_INPUT = wa_tab1-/BIC/GPUCSDJUL.
CALL FUNCTION ‘ROUND’

EXPORTING

INPUT = WA_INPUT

IMPORTING

OUTPUT = WA_OUTPUT.

*   commodity spend
    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 = WA_OUTPUT.
    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’ ).

WA_INPUT = wa_tab1-CHNG_VS_PREV.
CALL FUNCTION ‘ROUND’

EXPORTING

INPUT = WA_INPUT

IMPORTING

OUTPUT = WA_OUTPUT.

*   change vs prev
    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 = WA_OUTPUT.
    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’ ).

*   creation date
    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 = wa_tab1-/BIC/GPUCOCDAT_1.
    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’ ).
*   Fig cycle
    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 = wa_tab1-/BIC/GPU_FCYCL.
    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’ ).
clear wa_tab1.

ENDLOOP.

* *Mail sending functionality*************
* 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 ).
* 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( ).

DATA: objpack   LIKE sopcklsti1 OCCURS 2 WITH HEADER LINE.
DATA: objhead   LIKE solisti1 OCCURS 1 WITH HEADER LINE.
DATA: objbin    LIKE solix OCCURS 10 WITH HEADER LINE.
DATA: objtxt    LIKE solisti1 OCCURS 10 WITH HEADER LINE.
DATA: reclist   LIKE somlreci1 OCCURS 5 WITH HEADER LINE.
DATA: doc_chng LIKE sodocchgi1.
DATA: tab_lines LIKE sy-tabix.
DATA: l_num(3).
DATA: subj_date(10) TYPE c.

doc_chng-obj_descr = ‘Multiple Tabs’ .
*

DESCRIBE TABLE objtxt LINES tab_lines.
READ TABLE objtxt INDEX tab_lines.
doc_chng-doc_size = ( tab_lines – 1 ) * 255 + STRLEN( objtxt ).
* Packing List For the E-mail Body
objpack-head_start = 1.
objpack-head_num   = 0.
objpack-body_start = 1.
objpack-body_num   = tab_lines.
objpack-doc_type   = ‘RAW’.
APPEND objpack.
* Creation of the Document Attachment
LOOP AT l_xml_table INTO wa_xml.
    CLEAR objbin.
    objbin-line = wa_xml-data.
    APPEND objbin.
ENDLOOP.

DESCRIBE TABLE objbin LINES tab_lines.
objhead = Multiple tabs’.
APPEND objhead.
* Packing List For the E-mail Attachment
objpack-transf_bin = ‘X’.
objpack-head_start = 1.
objpack-head_num = 0.
objpack-body_start = 1.
objpack-body_num = tab_lines.
objpack-obj_descr = ‘Summary Table’.
objpack-doc_type = ‘XLS’.
objpack-doc_size = tab_lines * 255.
APPEND objpack.
* Target Recipent
CLEAR reclist.
reclist-receiver = I_MAILID.
reclist-rec_type = ‘U’.
RECLIST-EXPRESS = ‘X’.

APPEND reclist.

* Sending the document
CALL FUNCTION ‘SO_NEW_DOCUMENT_ATT_SEND_API1’
    EXPORTING
      document_data              = doc_chng
      put_in_outbox              = ‘X’
      COMMIT_WORK                = ‘X’
    TABLES
      packing_list               = objpack
      object_header              = objhead
      contents_txt               = objtxt
      contents_hex               = objbin
      receivers                  = reclist
    EXCEPTIONS
      too_many_receivers         = 1
      document_not_sent          = 2
      operation_no_authorization = 4
      OTHERS                     = 99.

IF sy-subrc EQ 0.

MOVE: text–412 TO ee_return-message,
‘I’ TO ee_return-type.

ENDIF.
ENDFUNCTION.

Technology

Tool & Technology: ABAP

Output

Execute FM by providing values for sender id and tables and see the attachment with multiple tabs of excel sheet as Output. Here we are creating two worksheets in OLE logic, thus two tabs will be in output attachment excel sheet with cell formatting (Color, font, borders, etc ).

Summary

3 Comments

You must be Logged on to comment or reply to a post.

Abhishek Sharma

July 11, 2018 at 4:36 pm

Hi,

Nice Article.. Thanks for sharing..

Caption of this shows OLE but I haven't seen use of OLE in code.

My question is I have created my formatted excel file using OLE2 object and now I wanted to send it as attachment. please guide me how can i do this ..

Thanks-

P D

May 16, 2019 at 12:45 pm

Abhishek Sharma,

Did you find answer for this ? i have have a similar requirement.

Jackson Jerald

August 16, 2020 at 5:59 am

Hi All,

Is it possible to make some columns as Locked/Protected? (Not Editable) using this above program.

Send Email with Mutliple tabs of excel as a single attachment using OLE

Objective

Introduction

Steps

Sample Code

Technology

Output

Summary

Assigned Tags