Skip to Content

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

/wp-content/uploads/2013/06/scr1_230888.jpg

Give Function group name ->Description -> Save

/wp-content/uploads/2013/06/scr2_230889.jpg

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 1st worksheet and  Pass data to 1st worksheet with styles as per requirements.

e)      Then Create 2nd 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.

/wp-content/uploads/2013/06/scr3_230890.jpg

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

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

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

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

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

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

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

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

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

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

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

  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_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_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 = ‘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’ ).

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

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

  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: text412 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 ).

/wp-content/uploads/2013/06/out1_230891.jpg

/wp-content/uploads/2013/06/out2_230892.jpg

Summary

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.

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply