Code Gallery
Skip to end of metadata
Go to start of metadata

Author: Himanshu Kanekar
Submitted: 14-Oct-2010
Related Links:

A very common requirement by the user is to export data to a Formatted Microsoft Excel Spreadsheet and send it to an Email or post it on a FTP Server.  If the report is a simple ALV grid then the ALV control can do it automatically, but it would be a simple Excel Spreadsheet without any formatting. In order to create a Formatted Excel Sheet we can either use the OLE (Object Linking & Enabling) Concept or the XML method.

The OLE method involves opening an instance of Microsoft Excel and then preparing the sheet. The major disadvantage of this method is that it can be executed in Foreground only.

An ABAP Program can create an XML file and it can be opened directly into Excel.  The advantages and possibilities are endless:

  • An xml file is plain text so can be created on the Presentation Server or Application Server;
  • It can be sent as an Attachment to an Email;
  • Colours, Fonts, Number Formats and so on can be specified;
  • Formatting can be applied to Rows, Columns or Single Cells;
  • Column Heights and Widths can be specified, or Auto-Formatted;
  • Multiple Worksheets can be created;
  • Formulas can be entered in cells;

This is a Sample Code for fetching the User Login Details and sending it as an XLS attachment to an Email using the XML method - 

REPORT  sy-repid.

TYPE-POOLS: ixml.

*------------------------------------------------------------------------*
*                           Data Declarations                            *
*------------------------------------------------------------------------*

* Structure for Final Internal Table
TYPES: BEGIN OF ty_final,
        srno(3) TYPE n,
        user_id TYPE usr02-bname,
        full_name TYPE bapiaddr3-fullname,
        dept TYPE bapiaddr3-department,
        login(3) TYPE c,
       END OF ty_final.

* Structure for USR02
TYPES: BEGIN OF ty_usr02,
        bname TYPE usr02-bname,
        trdat TYPE usr02-trdat,
       END OF ty_usr02.

* Internal Table & Work Area for Final Internal Table
DATA: it_final TYPE TABLE OF ty_final,
      wa_final TYPE ty_final.

* Internal Table & Work Area for USR02 Internal Table
DATA: it_usr02 TYPE TABLE OF ty_usr02,
      wa_usr02 TYPE ty_usr02.

* Work Area for ADD3_DATA Structre
DATA: wa_addr TYPE bapiaddr3.
DATA: it_return TYPE TABLE OF bapiret2.

DATA: lv_date TYPE d.
DATA: lv_filename TYPE string.

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


*------------------------------------------------------------------------*
*                             Initialization                             *
*------------------------------------------------------------------------*

INITIALIZATION.
  lv_date = sy-datum - 1.


*------------------------------------------------------------------------*
*                           Start of Selection                           *
*------------------------------------------------------------------------*

START-OF-SELECTION.

  PERFORM get_user_data.
  PERFORM process_xml_data.
  PERFORM send_mail.


*&---------------------------------------------------------------------*
*&      Form  get_user_data
*&---------------------------------------------------------------------*
*       Fetch User details from USR02
*----------------------------------------------------------------------*

FORM get_user_data.

  REFRESH it_final.
  SELECT DISTINCT bname trdat FROM usr02 INTO TABLE it_usr02.
  SORT it_usr02 BY bname.

  IF NOT it_usr02[] IS INITIAL.

    LOOP AT it_usr02 INTO wa_usr02.

      CLEAR wa_final.
      wa_final-srno = sy-tabix.                   " Serial No.
      wa_final-user_id = wa_usr02-bname.          " User ID

      CALL FUNCTION 'BAPI_USER_GET_DETAIL'
        EXPORTING
          username = wa_usr02-bname
        IMPORTING
          address  = wa_addr
        TABLES
          return   = it_return.

      IF sy-subrc EQ 0.
        wa_final-full_name = wa_addr-fullname.    " Full Name
        wa_final-dept = wa_addr-department.       " Department
      ENDIF.

      IF wa_usr02-trdat EQ lv_date.
        wa_final-login = 'YES'.                   " Login on Previous Day
      ELSE.
        wa_final-login = 'NO'.
      ENDIF.

      APPEND wa_final TO it_final.

    ENDLOOP.

  ENDIF.

ENDFORM.                    " get_user_data


*&---------------------------------------------------------------------*
*&      Form  SEND_MAIL
*&---------------------------------------------------------------------*
*       Send Email
*----------------------------------------------------------------------*

FORM send_mail.

  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.

* Mail Subject
  CONCATENATE lv_date+6(2) '-' lv_date+4(2) '-' lv_date+0(4) INTO subj_date.
  CONCATENATE 'SAP Application Usage Report ' subj_date INTO doc_chng-obj_descr SEPARATED BY space.

* Mail Contents
  objtxt = 'Dear User,'.
  APPEND objtxt.

  CLEAR objtxt.
  APPEND objtxt.

  CONCATENATE 'Please find the attached SAP Application Usage Report for ' subj_date INTO objtxt SEPARATED BY space.              " Mail Contents
  APPEND objtxt.

  CLEAR objtxt.
  APPEND objtxt.

  objtxt = 'Thanks & Regards,'.
  APPEND objtxt.

  objtxt = 'Himanshu Kanekar'.
  APPEND objtxt.

  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 = 'SAP Login Details'.
  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.
  CONCATENATE 'SAP_Login_Details' subj_date INTO objpack-obj_descr SEPARATED BY space.
  objpack-doc_type = 'XLS'.
  objpack-doc_size = tab_lines * 255.
  APPEND objpack.

* Target Recipent
  CLEAR reclist.
  reclist-receiver = 'user@company.com'.
  reclist-rec_type = 'U'.
  APPEND reclist.

* Sending the document
  CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'
    EXPORTING
      document_data              = doc_chng
      put_in_outbox              = '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.

ENDFORM.                    " SEND_MAIL



*&---------------------------------------------------------------------*
*&      Form  process_xml_data
*&---------------------------------------------------------------------*
*       Process XML Data
*----------------------------------------------------------------------*

FORM process_xml_data .

* 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_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' ).


* 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 = '90' ).

  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 = '150' ).

  r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
  r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '90' ).

* Blank Row
  r_row = l_document->create_simple_element( name = 'Row'  parent = r_table ).

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

* Sr. No.
  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 = 'Sr. No.'  parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

* User Name
  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 = 'User Name'  parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

* Full Name
  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 = 'Full Name'  parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

* Department
  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 = 'Department'  parent = r_cell ).
  r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

* Login
  r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).
  r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).
  CONCATENATE 'Login - ' lv_date+6(2) '/' lv_date+4(2) '/' lv_date+0(4) INTO l_value.
  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' ).

  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 it_final INTO wa_final.

    r_row = l_document->create_simple_element( name = 'Row'  parent = r_table ).

* Sr. No.
    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 = sy-tabix.
    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 = 'Number' ).                               " Cell format

* User Name
    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_final-user_id.
    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

* Full Name
    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_final-full_name.
    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

* Department
    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_final-dept.
    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

* Login
    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_final-login.
    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

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

ENDFORM.                    " process_xml_data

The output of the Report is a Formatted Excel Sheet as an email attachment.

9 Comments

  1. Unknown User (10072enqe)

    Hi,

    can you please let me know how did you get the values to the properties.

    Like color,

    I want to Merge few cells.. how to get different font sizes etc.

    is there any example

    kindly help

  2. Former Member

    Depending on the way this is run - you may need a COMMIT WORK AND WAIT after the CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1' .

  3. Jabin G George

    Hi Himanshu,

    Thank you for a nice explanation.

    I have followed the same approach and I had successully created the excel and even send it via email as an attachment , However When i try to open using MS-Excel 2010, I get an error .problems came up in the following areas during load: workbook setting

    this file cannot be opened because of errors. 

    Kindly help and provide your inputs .

    Thank you in advance.

    Regards

    Jabin George

     

    1. Vimal Raj

      Hi Jabin,

      If you resolve this issue kindly help me, for my case single mail attachment its working perfectly for multiple Excel attachment its not working properly.

       

      Regards

      Vimal Raj

  4. Vimal Raj

    First of all thank you, its very helpful to me, but for multiple attachment the excel is showing some Workbook loading error, kindly help me, to resolve that.

  5. VINOD YADAV

    Thanks Himanshu for such a nice explanation!!

    But still facing problem when opening multiple worksheets and I  am not sure whetehr we could use table l_xml_table from form "process_xml_data"  in form "send_mail" as in mine case i did not get desired output.

    Kind Regards

    Vinod Yadav

  6. NICOLA POISA

    Thank you very much,

     very good job.


    I've only a problem with "QUANTITY" fields ( and  € fields...)

    a quantity of 2 pieces, for example,  became 2000  in XLS....    how to set the decimals  ????

     

  7. VINOD YADAV

    Hi everyone,

    But still facing problem when opening multiple worksheets and I  am not sure whetehr we could use table l_xml_table from form "process_xml_data"  in form "send_mail" as in mine case i did not get desired output.

    Can anyone share before feeding table l_xml_table from form "process_xml_data"  in form "send_mail" they tried to convert it's format?

    Kind Regards

    Vinod Yadav

     

     

    1. Vimal Raj

      Hi Vinod,

      declare your quantity field as CHAR.

       

      OR "go throw this program you will get same idea"

      *&---------------------------------------------------------------------*
      *& Report  ZWM_MAIL_4_EXPIRE_MATERIAL
      *&
      *&---------------------------------------------------------------------*
      *&
      *&
      *&---------------------------------------------------------------------*

      REPORT  zwm_mail_4_expire_material.

      TYPES : BEGIN OF ty_mseg,
              bwart TYPE bwart,
              matnr TYPE matnr,
              werks TYPE werks_d,
              lgort TYPE lgort_d,
              charg TYPE charg_d,
              vfdat TYPE vfdat,
              xchpf TYPE xchpf,
              mhdhb TYPE mhdhb,
              END OF ty_mseg.

      TYPES : BEGIN OF ty_makt,
              matnr TYPE matnr,
              spras TYPE spras,
              maktx TYPE maktx,
              END OF ty_makt.

      TYPES : BEGIN OF ty_final,
              matnr TYPE matnr,
              maktx TYPE maktx,
              werks TYPE werks_d,
              lgort TYPE lgort_d,
              charg TYPE charg_d,
              vfdat TYPE char10,
              labst TYPE labst,
              END OF ty_final.

      TYPES : BEGIN OF ty_mchb,
              matnr TYPE mchb-matnr,
              werks TYPE mchb-werks,
              lgort TYPE mchb-lgort,
              charg TYPE mchb-charg,
              clabs TYPE mchb-clabs,
              END OF ty_mchb.

      TYPES: BEGIN OF xml_line,
              data(255) TYPE x,
             END OF xml_line.

      DATA : it_mseg   TYPE STANDARD TABLE OF ty_mseg,
             wa_mseg   TYPE ty_mseg,
             it_mseg30 TYPE STANDARD TABLE OF ty_mseg,
             wa_mseg30 TYPE ty_mseg.

      DATA : it_makt   TYPE STANDARD TABLE OF ty_makt,
             wa_makt   TYPE ty_makt,
             it_mchb   TYPE STANDARD TABLE OF ty_mchb,
             wa_mchb   TYPE ty_mchb,
             it_makt30 TYPE STANDARD TABLE OF ty_makt,
             wa_makt30 TYPE ty_makt,
             it_mchb30   TYPE STANDARD TABLE OF ty_mchb,
             wa_mchb30   TYPE ty_mchb.

      DATA : it_final   TYPE STANDARD TABLE OF ty_final,
             wa_final   TYPE ty_final,
             it_final30 TYPE STANDARD TABLE OF ty_final,
             wa_final30 TYPE ty_final,
             it_temp    TYPE STANDARD TABLE OF ty_final,
             wa_temp    TYPE ty_final,
             it_temp1   TYPE STANDARD TABLE OF ty_final,
             wa_temp1   TYPE ty_final.

      DATA : it_mail TYPE STANDARD TABLE OF zmail,
             wa_mail TYPE zmail.

      DATA : lv_vfdat  TYPE vfdat,
             lv_today  TYPE vfdat,
             lv_todate TYPE vfdat,
             lv_temp   TYPE char10,
             lv_labst  TYPE char17.

      ".............For First formatted excel conversion............................

      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.

      "..............................................................................

      ".............For Second formatted excel conversion............................

      DATA : l_ixml1            TYPE REF TO if_ixml,
             l_streamfactory1   TYPE REF TO if_ixml_stream_factory,
             l_ostream1         TYPE REF TO if_ixml_ostream,
             l_renderer1        TYPE REF TO if_ixml_renderer,
             l_document1        TYPE REF TO if_ixml_document.

      DATA : l_element_root1        TYPE REF TO if_ixml_element,
             ns_attribute1          TYPE REF TO if_ixml_attribute,
             r_element_properties1  TYPE REF TO if_ixml_element,
             r_element1             TYPE REF TO if_ixml_element,
             r_worksheet1           TYPE REF TO if_ixml_element,
             r_table1               TYPE REF TO if_ixml_element,
             r_column1              TYPE REF TO if_ixml_element,
             r_row1                 TYPE REF TO if_ixml_element,
             r_cell1                TYPE REF TO if_ixml_element,
             r_data1                TYPE REF TO if_ixml_element,
             l_value1               TYPE string,
             l_type1                TYPE string,
             l_text1(100)           TYPE c,
             r_styles1              TYPE REF TO if_ixml_element,
             r_style1               TYPE REF TO if_ixml_element,
             r_style2               TYPE REF TO if_ixml_element,
             r_format1              TYPE REF TO if_ixml_element,
             r_border1              TYPE REF TO if_ixml_element,
             num_rows1              TYPE i.

      DATA : l_xml_size1        TYPE i,
             l_rc1              TYPE i.
      "..............................................................................

      ".......................For Send Mail..........................................

      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 : objbin1   LIKE solix OCCURS 10 WITH HEADER LINE.
      DATA : objbin2   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 : tab_lines1 LIKE sy-tabix.
      DATA : l_num(3).
      DATA : subj_date(10) TYPE c.

      "..............................................................................

      SELECT-OPTIONS : s_vfdat FOR lv_vfdat.

      PERFORM get_data.
      PERFORM process_xml_data.
      PERFORM mail.

      *&---------------------------------------------------------------------*
      *&      Form  GET_DATA
      *&---------------------------------------------------------------------*
      *       text
      *----------------------------------------------------------------------*
      *  -->  p1        text
      *  <--  p2        text
      *----------------------------------------------------------------------*
      FORM get_data .

        lv_today = sy-datum.

        CALL FUNCTION 'RP_CALC_DATE_IN_INTERVAL'
          EXPORTING
            date      = lv_today
            days      = '30'
            months    = '00'
            signum    = '+'
            years     = '00'
          IMPORTING
            calc_date = lv_todate.


        s_vfdat-sign   = 'I'.
        s_vfdat-option = 'BT'.
        s_vfdat-low    = lv_today.
        s_vfdat-high   = lv_todate.
        APPEND s_vfdat.

        "For all expire material
        SELECT a~bwart
               a~matnr
               a~werks
               a~lgort
               a~charg
               a~vfdat
               b~xchpf
               b~mhdhb
          FROM mseg AS a INNER JOIN mara AS b
          ON a~matnr = b~matnr INTO CORRESPONDING FIELDS OF TABLE it_mseg
          WHERE a~bwart = '101' AND a~charg <> '' AND a~vfdat LE lv_today AND b~xchpf = 'X' AND b~mhdhb <> ''.


        IF it_mseg IS NOT INITIAL.
          SELECT matnr
                 spras
                 maktx
            FROM makt
            INTO TABLE it_makt
            FOR ALL ENTRIES IN it_mseg
            WHERE matnr = it_mseg-matnr AND spras = sy-langu.

          SELECT matnr
                 werks
                 lgort
                 charg
                 clabs
            FROM mchb
            INTO TABLE it_mchb
            FOR ALL ENTRIES IN it_mseg
            WHERE matnr = it_mseg-matnr AND werks = it_mseg-werks AND lgort = it_mseg-lgort AND charg = it_mseg-charg.
        ENDIF.
        "*************End of all expire material***************

        "For next 30 day expire material
        SELECT a~bwart
               a~matnr
               a~werks
               a~lgort
               a~charg
               a~vfdat
               b~xchpf
               b~mhdhb
          FROM mseg AS a INNER JOIN mara AS b
          ON a~matnr = b~matnr INTO CORRESPONDING FIELDS OF TABLE it_mseg30
          WHERE a~bwart = '101' AND a~charg <> '' AND a~vfdat IN s_vfdat AND b~xchpf = 'X' AND b~mhdhb <> ''.

        IF it_mseg30 IS NOT INITIAL.
          SELECT matnr
                 spras
                 maktx
            FROM makt
            INTO TABLE it_makt30
            FOR ALL ENTRIES IN it_mseg30
            WHERE matnr = it_mseg30-matnr AND spras = sy-langu.

          SELECT matnr
                 werks
                 lgort
                 charg
                 clabs
            FROM mchb
            INTO TABLE it_mchb30
            FOR ALL ENTRIES IN it_mseg30
            WHERE matnr = it_mseg30-matnr AND werks = it_mseg30-werks AND lgort = it_mseg30-lgort AND charg = it_mseg30-charg.
        ENDIF.
        "*************End of next 30 day expire material***************

        SELECT *
          FROM zmail
          INTO TABLE it_mail.

        LOOP AT it_mseg INTO wa_mseg.
          IF wa_mseg-vfdat IS NOT INITIAL.
            wa_final-matnr = wa_mseg-matnr.
            READ TABLE it_makt INTO wa_makt WITH KEY matnr = wa_final-matnr.
            IF sy-subrc = 0.
              wa_final-maktx = wa_makt-maktx.
            ENDIF.
            wa_final-werks = wa_mseg-werks.
            wa_final-lgort = wa_mseg-lgort.
            wa_final-charg = wa_mseg-charg.
            CONCATENATE wa_mseg-vfdat+6(2) '-' wa_mseg-vfdat+4(2) '-' wa_mseg-vfdat+0(5) INTO wa_final-vfdat.
            READ TABLE it_mchb INTO wa_mchb WITH KEY matnr = wa_mseg-matnr werks = wa_mseg-werks lgort = wa_mseg-lgort charg = wa_mseg-charg.
            IF sy-subrc = 0.
              wa_final-labst = wa_mchb-clabs.
            ENDIF.
            IF wa_final-labst IS NOT INITIAL.
              APPEND wa_final TO it_final.
            ENDIF.
          ENDIF.
          CLEAR : wa_final, wa_mseg, wa_makt, wa_mchb.
        ENDLOOP.

        LOOP AT it_mseg30 INTO wa_mseg30.
          IF wa_mseg30-vfdat IS NOT INITIAL.
            wa_final30-matnr = wa_mseg30-matnr.
            READ TABLE it_makt30 INTO wa_makt30 WITH KEY matnr = wa_final30-matnr.
            IF sy-subrc = 0.
              wa_final30-maktx = wa_makt30-maktx.
            ENDIF.
            wa_final30-werks = wa_mseg30-werks.
            wa_final30-lgort = wa_mseg30-lgort.
            wa_final30-charg = wa_mseg30-charg.
            CONCATENATE wa_mseg30-vfdat+6(2) '-' wa_mseg30-vfdat+4(2) '-' wa_mseg30-vfdat+0(5) INTO wa_final30-vfdat.
            READ TABLE it_mchb30 INTO wa_mchb30 WITH KEY matnr = wa_mseg30-matnr werks = wa_mseg30-werks lgort = wa_mseg30-lgort charg = wa_mseg30-charg.
            IF sy-subrc = 0.
              wa_final30-labst = wa_mchb30-clabs.
            ENDIF.
            IF wa_final30-labst IS NOT INITIAL.
              APPEND wa_final30 TO it_final30.
            ENDIF.
          ENDIF.
          CLEAR : wa_final30, wa_mseg30, wa_makt30, wa_mchb30.
        ENDLOOP.

      ENDFORM.                    " GET_DATA

      *&---------------------------------------------------------------------*
      *&      Form  PROCESS_XML_DATA
      *&---------------------------------------------------------------------*
      *       text
      *----------------------------------------------------------------------*
      *  -->  p1        text
      *  <--  p2        text
      *----------------------------------------------------------------------*
      FORM process_xml_data.

        ".............................Attachment 1 Begin............................

      * 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 = 'MATERIAL1'  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 ).

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

      * Sr. No.
        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 = 'Sr. No.'  parent = r_cell ).
        r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Material No.
        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 Number'  parent = r_cell ).
        r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Material 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 = 'Material Description'  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' ).

      * Storage Location
        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 Location'  parent = r_cell ).
        r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Batch
        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 = 'Batch'  parent = r_cell ).
        r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Expired 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 = 'Expired Date'  parent = r_cell ).
        r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Unrestricted 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 = 'Unrestricted Quantity'  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' ).

      * Data Table
        LOOP AT it_final INTO wa_final.

          r_row = l_document->create_simple_element( name = 'Row'  parent = r_table ).

      * Sr. No.
          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 = sy-tabix.
          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 = 'Number' ).                               " Cell format

      * Material No.
          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_final-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' ).                               " Cell format

      * Material 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 = wa_final-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' ).                               " Cell format

      * 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 = wa_final-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' ).                               " Cell format

      * Storage Location
          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_final-lgort.
          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

      * Batch
          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_final-charg.
          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

      * Expired 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_final-vfdat.
          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

      * Unrest. Qty
          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_final-labst.
          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_final.
        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( ).

        ".............................End of Attachment 1...........................

        ".............................Attachment 2 Begin............................



      * Creating a ixml Factory
        l_ixml1 = cl_ixml=>create( ).

      * Creating the DOM Object Model
        l_document1 = l_ixml1->create_document( ).

      * Create Root Node 'Workbook'
        l_element_root1  = l_document1->create_simple_element( name = 'Workbook'  parent = l_document1 ).
        l_element_root1->set_attribute( name = 'xmlns'  value = 'urn:schemas-microsoft-com:office:spreadsheet' ).

        ns_attribute1 = l_document1->create_namespace_decl( name = 'ss'  prefix = 'xmlns'  uri = 'urn:schemas-microsoft-com:office:spreadsheet' ).
        l_element_root1->set_attribute_node( ns_attribute1 ).

        ns_attribute1 = l_document1->create_namespace_decl( name = 'x'  prefix = 'xmlns'  uri = 'urn:schemas-microsoft-com:office:excel' ).
        l_element_root1->set_attribute_node( ns_attribute1 ).

      * Create node for document properties.
        r_element_properties1 = l_document1->create_simple_element( name = 'MATERIAL2'  parent = l_element_root1 ).
        l_value1 = sy-uname.
        l_document1->create_simple_element( name = 'Author'  value = l_value1  parent = r_element_properties1  ).

      * Styles
        r_styles1 = l_document1->create_simple_element( name = 'Styles'  parent = l_element_root1  ).

      * Style for Header
        r_style1  = l_document1->create_simple_element( name = 'Style'   parent = r_styles1  ).
        r_style1->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Header' ).

        r_format1  = l_document1->create_simple_element( name = 'Font'  parent = r_style1  ).
        r_format1->set_attribute_ns( name = 'Bold'  prefix = 'ss'  value = '1' ).

        r_format1  = l_document1->create_simple_element( name = 'Interior' parent = r_style1  ).
        r_format1->set_attribute_ns( name = 'Color'   prefix = 'ss'  value = '#92D050' ).
        r_format1->set_attribute_ns( name = 'Pattern' prefix = 'ss'  value = 'Solid' ).

        r_format1  = l_document1->create_simple_element( name = 'Alignment'  parent = r_style1  ).
        r_format1->set_attribute_ns( name = 'Vertical'  prefix = 'ss'  value = 'Center' ).
        r_format1->set_attribute_ns( name = 'WrapText'  prefix = 'ss'  value = '1' ).

        r_border1  = l_document1->create_simple_element( name = 'Borders'  parent = r_style1 ).
        r_format1  = l_document1->create_simple_element( name = 'Border'   parent = r_border  ).
        r_format1->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
        r_format1->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
        r_format1->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

        r_format1  = l_document1->create_simple_element( name = 'Border'   parent = r_border1  ).
        r_format1->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).
        r_format1->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
        r_format1->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

        r_format1  = l_document1->create_simple_element( name = 'Border'   parent = r_border1  ).
        r_format1->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).
        r_format1->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
        r_format1->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

        r_format1  = l_document1->create_simple_element( name = 'Border'   parent = r_border1  ).
        r_format1->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).
        r_format1->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
        r_format1->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

      * Style for Data
        r_style2  = l_document1->create_simple_element( name = 'Style'   parent = r_styles1  ).
        r_style2->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Data' ).

        r_border1  = l_document1->create_simple_element( name = 'Borders'  parent = r_style2 ).
        r_format1  = l_document1->create_simple_element( name = 'Border'   parent = r_border1  ).
        r_format1->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
        r_format1->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
        r_format1->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

        r_format1  = l_document1->create_simple_element( name = 'Border'   parent = r_border1  ).
        r_format1->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).
        r_format1->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
        r_format1->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

        r_format1  = l_document1->create_simple_element( name = 'Border'   parent = r_border1  ).
        r_format1->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).
        r_format1->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
        r_format1->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

        r_format1  = l_document1->create_simple_element( name = 'Border'   parent = r_border1  ).
        r_format1->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).
        r_format1->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
        r_format1->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).


      * Worksheet
        r_worksheet1 = l_document1->create_simple_element( name = 'Worksheet'  parent = l_element_root1 ).
        r_worksheet1->set_attribute_ns( name = 'Name'  prefix = 'ss'  value = 'Sheet1' ).

      * Table
        r_table1 = l_document1->create_simple_element( name = 'Table'  parent = r_worksheet1 ).
        r_table1->set_attribute_ns( name = 'FullColumns'  prefix = 'x'  value = '1' ).
        r_table1->set_attribute_ns( name = 'FullRows'     prefix = 'x'  value = '1' ).

      * Column Formatting
        r_column1 = l_document1->create_simple_element( name = 'Column'  parent = r_table1 ).
        r_column1->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '40' ).

        r_column1 = l_document1->create_simple_element( name = 'Column'  parent = r_table1 ).
        r_column1->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '100' ).

        r_column1 = l_document1->create_simple_element( name = 'Column'  parent = r_table1 ).
        r_column1->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '140' ).

        r_column1 = l_document1->create_simple_element( name = 'Column'  parent = r_table1 ).
        r_column1->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '40' ).

        r_column1 = l_document1->create_simple_element( name = 'Column'  parent = r_table1 ).
        r_column1->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '90' ).

        r_column1 = l_document1->create_simple_element( name = 'Column'  parent = r_table1 ).
        r_column1->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '50' ).

        r_column1 = l_document1->create_simple_element( name = 'Column'  parent = r_table1 ).
        r_column1->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '55' ).

        r_column1 = l_document1->create_simple_element( name = 'Column'  parent = r_table1 ).
        r_column1->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '65' ).


      * Blank Row
        r_row1 = l_document1->create_simple_element( name = 'Row'  parent = r_table1 ).

      * Column Headers Row
        r_row1 = l_document1->create_simple_element( name = 'Row'  parent = r_table1 ).
        r_row1->set_attribute_ns( name = 'AutoFitHeight'  prefix = 'ss'  value = '1' ).

      * Sr. No.
        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).
        r_data1 = l_document1->create_simple_element( name = 'Data'  value = 'Sr. No.'  parent = r_cell1 ).
        r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Material No.
        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).
        r_data1 = l_document1->create_simple_element( name = 'Data'  value = 'Material Number'  parent = r_cell1 ).
        r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Material Description
        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).
        r_data1 = l_document1->create_simple_element( name = 'Data'  value = 'Material Description'  parent = r_cell1 ).
        r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Plant
        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).
        r_data1 = l_document1->create_simple_element( name = 'Data'  value = 'Plant'  parent = r_cell1 ).
        r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Storage Location
        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).
        r_data1 = l_document1->create_simple_element( name = 'Data'  value = 'Storage Location'  parent = r_cell1 ).
        r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Batch
        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).
        r_data1 = l_document1->create_simple_element( name = 'Data'  value = 'Batch'  parent = r_cell1 ).
        r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Expired Date
        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).
        r_data1 = l_document1->create_simple_element( name = 'Data'  value = 'Expired Date'  parent = r_cell1 ).
        r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Unrestricted Quantity
        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).
        r_data1 = l_document1->create_simple_element( name = 'Data'  value = 'Unrestricted Quantity'  parent = r_cell1 ).
        r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

      * Blank Row after Column Headers
        r_row1 = l_document1->create_simple_element( name = 'Row'  parent = r_table1 ).
        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

        r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
        r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

      * Data Table
        LOOP AT it_final30 INTO wa_final30.

          r_row1 = l_document1->create_simple_element( name = 'Row'  parent = r_table1 ).

      * Sr. No.
          r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
          r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).
          l_value1 = sy-tabix.
          CONDENSE l_value1 NO-GAPS.
          r_data1 = l_document1->create_simple_element( name = 'Data'  value = l_value1   parent = r_cell1 ).           " Data
          r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'Number' ).                               " Cell format

      * Material No.
          r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
          r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).
          l_value1 = wa_final30-matnr.
          r_data1 = l_document1->create_simple_element( name = 'Data'  value = l_value1   parent = r_cell1 ).           " Data
          r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                               " Cell format

      * Material Description
          r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
          r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).
          l_value1 = wa_final30-maktx.
          r_data1 = l_document1->create_simple_element( name = 'Data'  value = l_value1   parent = r_cell1 ).           " Data
          r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                               " Cell format

      * Plant
          r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
          r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).
          l_value1 = wa_final30-werks.
          r_data1 = l_document1->create_simple_element( name = 'Data'  value = l_value1   parent = r_cell1 ).           " Data
          r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                               " Cell format

      * Storage Location
          r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
          r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).
          l_value1 = wa_final30-lgort.
          r_data1 = l_document1->create_simple_element( name = 'Data'  value = l_value1   parent = r_cell1 ).          " Data
          r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                              " Cell format

      * Batch
          r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
          r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).
          l_value1 = wa_final30-charg.
          r_data1 = l_document1->create_simple_element( name = 'Data'  value = l_value1   parent = r_cell1 ).          " Data
          r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                              " Cell format

      * Expired Date
          r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
          r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).
          l_value1 = wa_final30-vfdat.
          r_data1 = l_document1->create_simple_element( name = 'Data'  value = l_value1   parent = r_cell1 ).          " Data
          r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                              " Cell format

      * Unrest. Qty
          r_cell1 = l_document1->create_simple_element( name = 'Cell'  parent = r_row1 ).
          r_cell1->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).
          l_value1 = wa_final30-labst.
          r_data1 = l_document1->create_simple_element( name = 'Data'  value = l_value1   parent = r_cell1 ).          " Data
          r_data1->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                              " Cell format
          CLEAR : wa_final30.
        ENDLOOP.

      * Creating a Stream Factory
        l_streamfactory1 = l_ixml1->create_stream_factory( ).

      * Connect Internal XML Table to Stream Factory
        l_ostream1 = l_streamfactory1->create_ostream_itable( table = l_xml_table2 ).

      * Rendering the Document
        l_renderer1 = l_ixml1->create_renderer( ostream  = l_ostream1  document = l_document1 ).
        l_rc1 = l_renderer1->render( ).

      * Saving the XML Document
        l_xml_size1 = l_ostream1->get_num_written_raw( ).

        ".............................End of Attachment 2...........................

      ENDFORM.                    " PROCESS_XML_DATA



      *&---------------------------------------------------------------------*
      *&      Form  MAIL
      *&---------------------------------------------------------------------*
      *       text
      *----------------------------------------------------------------------*
      *  -->  p1        text
      *  <--  p2        text
      *----------------------------------------------------------------------*
      FORM mail.
        DATA : lv_date TYPE char10.

        lv_date = sy-datum.

      * Mail Subject
        doc_chng-obj_name  = 'Unused_material_for_1_year'.
        CONCATENATE lv_date+6(2) '-' lv_date+4(2) '-' lv_date+0(4) INTO subj_date.
        CONCATENATE 'List of unused materials since 1 year,' subj_date INTO doc_chng-obj_descr SEPARATED BY space.

        "Body of the mail
        IF it_final IS NOT INITIAL AND it_final30 IS NOT INITIAL.
          PERFORM build_body_of_mail
          USING:space,
          'Dear Sir/Madam,',
          '',
          'Kindly find the attachment for the list of materials expired and going to expire in next 30 days.',
          '',
          'Regards,',
          'Leeboy India'.
        ENDIF.

        IF it_final IS NOT INITIAL AND it_final30 IS INITIAL.
          PERFORM build_body_of_mail
          USING:space,
          'Dear Sir/Madam,',
          '',
          'Kindly find the attachment for the list of materials expired and there are no materials going to expire in next 30 days.',
          '',
          'Regards,',
          'Leeboy India'.
        ENDIF.

        IF it_final IS INITIAL AND it_final30 IS NOT INITIAL.
          PERFORM build_body_of_mail
          USING:space,
          'Dear Sir/Madam,',
          '',
          'Kindly find the attachment for the list of materials going to expire in next 30 days, there is no expired material.',
          '',
          'Regards,',
          'Leeboy India'.
        ENDIF.

        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 1
        IF it_final IS NOT INITIAL.

          LOOP AT l_xml_table1 INTO wa_xml.
            CLEAR objbin1.
            objbin1-line = wa_xml-data.
            APPEND objbin1.
          ENDLOOP.

          DESCRIBE TABLE objbin1 LINES tab_lines.
          objhead = 'All expired material'.
          APPEND objhead.

          tab_lines1 = tab_lines.          "For Attachment 2 'tab_lines1' is used.

      * 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.
          CONCATENATE 'All_expired_material_' subj_date INTO objpack-obj_descr SEPARATED BY space.
          objpack-doc_type = 'XLS'.
          objpack-doc_size = tab_lines * 255.
          APPEND objpack.
        ENDIF.

      * Creation of the Document Attachment 2
        IF it_final30 IS NOT INITIAL.

          LOOP AT l_xml_table2 INTO wa_xml.
            CLEAR objbin2.
            objbin2-line = wa_xml-data.
            APPEND objbin2.
          ENDLOOP.

          DESCRIBE TABLE objbin2 LINES tab_lines.
          objhead = 'Goto expire in 30day material'.
          APPEND objhead.


      * Packing List For the E-mail Attachment
          objpack-transf_bin = 'X'.
          objpack-head_start = 1.
          objpack-head_num   = 0.
          objpack-body_start = tab_lines1 + 1.
          objpack-body_num = tab_lines.
          CONCATENATE 'Goto_expire_in_30day_material_' subj_date INTO objpack-obj_descr SEPARATED BY space.
          objpack-doc_type = 'XLS'.
          objpack-doc_size = tab_lines * 255.
          APPEND objpack.
        ENDIF.

        IF objbin1 IS NOT INITIAL.
          LOOP AT objbin1.
            MOVE objbin1-line TO objbin-line.
            APPEND objbin.
          ENDLOOP.
        ENDIF.

        IF objbin2 IS NOT INITIAL.
          LOOP AT objbin2.
            MOVE objbin2-line TO objbin-line.
            APPEND objbin.
          ENDLOOP.
        ENDIF.


      * Target Recipent

        "Receivers List.
        LOOP AT it_mail INTO wa_mail.
          IF wa_mail-to_mailid IS NOT INITIAL.
            TRANSLATE wa_mail-to_mailid TO LOWER CASE.
            reclist-rec_type   = 'U'.  "Internet address
            reclist-receiver   = wa_mail-to_mailid.
            reclist-com_type   = 'INT'.
            reclist-notif_del  = 'X'.
            reclist-notif_ndel = 'X'.
            APPEND reclist.
          ENDIF.
          IF wa_mail-cc_mailid IS NOT INITIAL.
            TRANSLATE wa_mail-cc_mailid TO LOWER CASE.
            reclist-rec_type   = 'U'.  "Internet address
            reclist-receiver   = wa_mail-cc_mailid.
            reclist-com_type   = 'INT'.
            reclist-notif_del  = 'X'.
            reclist-notif_ndel = 'X'.
            reclist-copy       = 'X'.   "CC Mail id
            APPEND reclist.
          ENDIF.
          CLEAR : wa_mail.
        ENDLOOP.

      * 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 = 0 .
      *    MESSAGE i303(me) WITH 'Mail has been Successfully Sent.'.
        ELSE.
          WAIT UP TO 2 SECONDS.
          "This program starts the SAPconnect send process.
          SUBMIT rsconn01 WITH mode = 'INT'
          WITH output = 'X'
          AND RETURN.
        ENDIF.


      ENDFORM.                    " MAIL

      *&---------------------------------------------------------------------*
      *&      Form  BUILD_BODY_OF_MAIL
      *&---------------------------------------------------------------------*
      *       text
      *----------------------------------------------------------------------*
      *      -->P_SPACE  text
      *----------------------------------------------------------------------*
      FORM build_body_of_mail  USING    l_message.

        objtxt-line = l_message.
        APPEND objtxt.
        CLEAR  objtxt.

      ENDFORM.                    " BUILD_BODY_OF_MAIL