How to send a formatted Excel sheet as an attachment in a mail.

[Former Member]

Hi,

My question is "How to send a foramtted Excel sheet (Developed using ABAP program by using OLE Automation

Controller)" as an Attachment in a mail. I have checked CL_DOCUMENT_BCS->ADD_ATTACHMENT,which Imports a internal table of type SOLI_TAB / SOLIX_TAB and convert into unformatted Excel sheet. ALso

CL_DOCUMENT_BCS->ADD_DOCUMENT_AS_ATTACHMENT (Add Existing Document as an Attachment) however its parameter is an interface (IF_DOCUMENT_BCS). Should I use the same class or does SAP have an alternate class ?

Please give me hint on the above questuin.

Thankx in advance.

[former_member184578]

Hi.,

Check this wiki: [Formatted Excel As attachment in email|http://wiki.sdn.sap.com/wiki/display/Snippets/FormattedExcelasEmailAttachment]

also check this article: http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/7f601a65-0a01-0010-16a5-c369...

hope this helps u.,

Thanks & Regards,

Kiran

[Former Member]

Thanx Kiran. However I'm looking for Object-oriented programming to create Excel & mail. The link provide use Function Module "'SO_NEW_DOCUMENT_ATT_SEND_API1'.

[former_member184578]

Hi.,

You can do the same with CL BCS.

Thanks & Regards,

Kiran

[Former Member]

Hello,

I thnk you should use the CL_BCS , please see the below link

http://wiki.sdn.sap.com/wiki/display/Snippets/SendMailhavingMultipleFilesasAttachmentusingobjectorientedtechnique

Also for the creation of the excel file you can use the below link ( see form build_xls_data )

Antonis

kind regards

[Former Member]

Thanx Konstantinidis Antonis,

That's correct, I have used CL_BCS & CL_DOCUMENT_BCS and the code is working fine ,however the Excel is of unformatted . So could please let me know how to Format Excel (is done by OLE or I_OI_DOCUMENT_PROXY I'm not able to add it as attachment using above classes) & send it using above classes.

[Former Member]

Hi Kevindass

Have you seen ABAP2XLSX? It's a library to help create formatted Excel 2007+ spreadsheets.

There are links to the code in Code Exchange and to useful Blogs in this Wiki:

http://wiki.sdn.sap.com/wiki/display/ABAP/abap2xlsx

Regards

Glen

[Clemenss]

Hi kevindass,

use I_OI_DOCUMENT_PROXY, check [documentation on Desktop Office Integration|http://help.sap.com/printdocu/core/Print46c/en/data/pdf/BCCIOFFI/BCCIOFFI.pdf]. After fillling the excel, close the excel using the method close_document, then save excel in an internal table of type SOLITAB:

CALL METHOD document->save_document_to_table
  EXPORTING no_flush = no_flush
  IMPORTING error = error
    retcode = retcode
  CHANGING document_table = document_table
    document_size = document_size..

Then use CL_BCS to add the internal SOLITAB table as attachment.

Regards

Clemens

[Former Member]

Hi,

you can get a formatted excel attachemnt using cl_bcs_convert=>string_to_solix

U need to pass the Line heading in the first time of the loop ,

from the next cycle of the loop , u need to take the values for the flds in order and concatenate and pass to the method mentioned above .

And remember the codepage to be given for the case of Excel is '4103'.

Like eg ,

Step 1: For header line =>

as ,

CONCATENATE

lv_string

'col1' cl_bcs_convert=>gc_tab "table value

'col2' cl_bcs_convert=>gc_tab "table value

cl_bcs_convert=>gc_crlf "to go to next line

INTO lv_string.

Step 2 : Now pass this to class cl_bcs_convert=>string_to_solix

as,

CALL METHOD cl_bcs_convert=>string_to_solix

EXPORTING

iv_string = lv_string

iv_codepage = '4103'

iv_add_bom = 'X'

IMPORTING

et_solix = l_content "excel content

ev_size = l_size.

CATCH cx_bcs .

ENDTRY.

Step 3 : loop the internal value to pass the values for all the 3 cols, concatenate in the same manner and pass to the above mentioned method for excel conversion.

Then , u can attach the excel content in the mail ( l_content).

Thanks,

Sindhuja

[Former Member]

Hi Ravi,

     can you please post the code. i have the same requirement.

[Former Member]

Hi Kevin,

You can do it using the class "cl_ixml". First, you have to populate your internal table with required values. Then you can process the xml data by looping the internal table. A sample code is given below:

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

• Worksheet

r_worksheet = l_document->create_simple_element( name = 'Worksheet' parent = l_element_root ).

r_worksheet->set_attribute_ns( name = 'Name' prefix = 'ss' value = 'PO Details' ).

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

r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).

r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '70' ).

r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).

r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '70' ).

r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).

r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '70' ).

r_column = l_document->create_simple_element( name = 'Column' parent = r_table ).

r_column->set_attribute_ns( name = 'Width' prefix = 'ss' value = '70' ).

• 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 = 'EBELN' 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 = 'EBELP' 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 = 'AEDAT' 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 = 'MATNR' parent = r_cell ).

r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).

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

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

• Data Table

LOOP AT it_ekpo INTO wa_ekpo.

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

• EBELN

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_ekpo-ebeln.

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

• EBELP

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_ekpo-ebelp.

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

• AEDAT

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_ekpo-aedat.

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

• MATNR

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_ekpo-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

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

Before sending the mail,

LOOP AT l_xml_table INTO wa_xml.

CLEAR objbin.

objbin-line = wa_xml-data.

APPEND objbin to BINARY_CONTENT.

ENDLOOP.

Here, objbin is of type SOLIX and BINARY_CONTENT is of type SOLIX_TAB.

Now, call the method,

CALL METHOD DOCUMENT->ADD_ATTACHMENT

EXPORTING I_ATTACHMENT_TYPE = 'XLS'

I_ATTACHMENT_SUBJECT = 'My attachment'

I_ATT_CONTENT_HEX = BINARY_CONTENT .

[Former Member]

Hi i am using this code, which is working fine.

But the moment i try to put data

LOOP AT it_ekpo INTO wa_ekpo.

    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_ekpo-ebeln.

    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.

I am getting error in opening the excel when it try to use value = 'Data'.

Regards,

Rohini

[Former Member]

Hi kelvin,

              i have the same requirement. can you please post the code for mail sending with attachment as excel with Header as Bold.

[Former Member]

Hi kelvin,

              i have the same requirement. can you post the code for mail sending with attachment as excel with Header as Bold.

[Former Member]

Srinivas,

              Well the partial code is been posted by Rajesh Chandran. However here we go, the code is divided into t parts using Custom class with to methods.....please take some time out to catch it up...I'm sure you shall . <removed by moderator>

Method-1: To send email as XLS....which would call the other method to format the XLS sheet:

METHOD email_send.

* email_send shows how to send

*   - a simple text provided in an internal table of text lines

*   - and an attached XLS document provided in internal table

*   - to multiple internet email address.

* All activities done via facade CL_BCS!

  CONSTANTS: lc_raw       TYPE so_obj_tp  VALUE 'RAW',

             lc_att_type  TYPE so_obj_tp  VALUE 'XLS',

             lc_true      TYPE os_boolean VALUE 'X',

             lc_false     TYPE os_boolean VALUE ' '.

  DATA: send_request       TYPE REF TO cl_bcs.

  DATA: document           TYPE REF TO cl_document_bcs.

  DATA: sender             TYPE REF TO cl_sapuser_bcs.

  DATA: recipient          TYPE REF TO if_recipient_bcs.

  DATA: bcs_exception      TYPE REF TO cx_bcs.              "#EC NEEDED

  DATA: sent_to_all        TYPE os_boolean.

  DATA: ls_email           TYPE ad_smtpadr,

        lt_attachment_hex  TYPE solix_tab.

  TRY.

* create persistent send request ------------------------*

      send_request = cl_bcs=>create_persistent( ).

* create and set document with attachment from internal table with text---------------*

      document = cl_document_bcs=>create_document(

                      i_type    = lc_raw

                      i_text    = iv_body

                      i_subject = iv_subject ).

      IF NOT iv_attachment IS INITIAL.

*        "convert soli to solix - lt_attachment_hex

        CALL METHOD ZCL_ERROR_HANDLING=>FORMAT_EXCEL

          EXPORTING

            IV_ATTACHMENT   = IV_ATTACHMENT

          IMPORTING

            IV_ATTACHMENT_X = lt_attachment_hex.

        CALL METHOD document->add_attachment

          EXPORTING

           i_attachment_type = 'TXT'

**            i_attachment_type    = lc_att_type

            i_attachment_subject = iv_attach_title

            i_att_content_hex    = lt_attachment_hex.

    ENDIF.

*       add document to send request

      CALL METHOD send_request->set_document( document ).

* set sender -------------------------------------------

*     note: this is necessary only if you want to set the sender

*           different from actual user (SY-UNAME). Otherwise sender is

*           set automatically with actual user.

      sender = cl_sapuser_bcs=>create( sy-uname ).

      CALL METHOD send_request->set_sender

        EXPORTING

          i_sender = sender.

      LOOP AT it_recipients INTO ls_email.

* add recipient (e-mail address) -----------------------

        recipient = cl_cam_address_bcs=>create_internet_address(

                                          ls_email ).

* add recipient with its respective attributes to send request

        CALL METHOD send_request->add_recipient

          EXPORTING

            i_recipient = recipient

            i_express   = lc_true.

      ENDLOOP.

* send document ---------------------------------------

      CALL METHOD send_request->send(

        EXPORTING

          i_with_error_screen = lc_false

        RECEIVING

          result              = sent_to_all ).

      IF sent_to_all = lc_true.

        ev_subrc = 0.

      ELSE.

        ev_subrc = 4.

      ENDIF.

*  -----------------------------------------------------------

*  *                     exception handling

*  -----------------------------------------------------------

*  * replace this very rudimentary exception handling

*  * with your own one !!!

*  -----------------------------------------------------------

    CATCH cx_bcs INTO bcs_exception.

      ev_subrc = 4.

      EXIT.

  ENDTRY.

ENDMETHOD.

Method-2: Format XLS sheet

method FORMAT_EXCEL.

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, "#EC NEEDED

      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,

      LV_VALUE              TYPE STRING,

      LV_TYPE               TYPE STRING,

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

DATA: LT_XML_TABLE          TYPE TABLE OF XML_LINE,

      LS_XML                TYPE XML_LINE,

      LV_XML_SIZE           TYPE I,     "#EC NEEDED

      LV_RC                 TYPE I,     "#EC NEEDED

      LV_NUM_ROWS           TYPE I.     "#EC NEEDED

*GET_TABLE_DETAILS

DATA: LT_DATA_TAB           TYPE REF TO DATA,     "#EC NEEDED

      LS_GOTSTATE           TYPE  DDGOTSTATE,     "#EC NEEDED

      LT_DD03P_TAB          TYPE TABLE OF DD03P,

      LS_DD03P              TYPE DD03P.

FIELD-SYMBOLS:

       &lt;LT_DATA_TAB>       TYPE STANDARD TABLE,

       &lt;LS_DATA_LINE>      TYPE ANY,

       <FIELD>             TYPE ANY.

DATA: LT_ATTACH            TYPE SOLIX_TAB,

      LS_ATTACH            TYPE SOLIX,

      LS_ATTACHMENT        TYPE SOLI.

DATA: LT_ATTACH_STR        TYPE ZIF_ATTACHMENT_TAB,

      LS_ATTACH_STR        TYPE ZIF_ATTACHMENT.

CLASS CL_ABAP_CHAR_UTILITIES DEFINITION LOAD.

  CONSTANTS:

             CON_TAB  TYPE C VALUE CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.                  "CR_LF

*Get Tabel details (Read a table from the ABAP Dictionary)

CALL FUNCTION 'DDIF_TABL_GET'

      EXPORTING

        NAME                = 'ZIF_ATTACHMENT'

*     STATE               = 'A'

        LANGU               = SY-LANGU

      IMPORTING

       GOTSTATE            = LS_GOTSTATE

      TABLES

       DD03P_TAB           = LT_DD03P_TAB

      EXCEPTIONS

        ILLEGAL_INPUT       = 1

        OTHERS              = 2

              .

  IF SY-SUBRC &lt;> 0.

    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

  ENDIF.

*Split single cloumn to multiple cloumns

LOOP AT IV_ATTACHMENT INTO LS_ATTACHMENT.

CLEAR LS_ATTACH_STR.

SPLIT LS_ATTACHMENT AT CON_TAB INTO:LS_ATTACH_STR-IF_ID LS_ATTACH_STR-IF_RECEIVING_SY LS_ATTACH_STR-IF_CURR_SEQ_NO

                                    LS_ATTACH_STR-IF_ROW_NO LS_ATTACH_STR-IF_FILE_INDEX_NO LS_ATTACH_STR-IF_ERR_MSG.

APPEND LS_ATTACH_STR TO LT_ATTACH_STR.

1. ENDLOOP.

ASSIGN LT_ATTACH_STR TO &lt;LT_DATA_TAB> .

*ASSIGN IV_ATTACHMENT TO &lt;LT_DATA_TAB> .

*---------------------------------------------- Convert XML to Excel ---------------------------------

* 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 ).                "#EC NOTEXT

  L_ELEMENT_ROOT->SET_ATTRIBUTE( NAME = 'xmlns'  VALUE = 'urn:schemas-microsoft-com:office:spreadsheet' ).  "#EC NOTEXT

  NS_ATTRIBUTE = L_DOCUMENT->CREATE_NAMESPACE_DECL( NAME = 'ss'  PREFIX = 'xmlns'  URI = 'urn:schemas-microsoft-com:office:spreadsheet' ). "#EC NOTEXT

  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' ). "#EC NOTEXT

  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 ). "#EC NOTEXT

  LV_VALUE = SY-UNAME.

  L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Author'  VALUE = LV_VALUE  PARENT = R_ELEMENT_PROPERTIES  ). "#EC NOTEXT

* Styles

  R_STYLES = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Styles'  PARENT = L_ELEMENT_ROOT  ). "#EC NOTEXT

* Style for Header

  R_STYLE  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Style'   PARENT = R_STYLES  ). "#EC NOTEXT

  R_STYLE->SET_ATTRIBUTE_NS( NAME = 'ID'  PREFIX = 'ss'  VALUE = 'Header' ). "#EC NOTEXT

  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Font'  PARENT = R_STYLE  ). "#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Bold'  PREFIX = 'ss'  VALUE = '1' ).          "#EC NOTEXT

  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Interior' PARENT = R_STYLE  )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Color'   PREFIX = 'ss'  VALUE = '#D699FF' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Pattern' PREFIX = 'ss'  VALUE = 'Solid' )."#EC NOTEXT

  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Alignment'  PARENT = R_STYLE  )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Vertical'  PREFIX = 'ss'  VALUE = 'Center' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'WrapText'  PREFIX = 'ss'  VALUE = '1' )."#EC NOTEXT

*BORDER

  R_BORDER  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Borders'  PARENT = R_STYLE )."#EC NOTEXT

  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Border'   PARENT = R_BORDER  )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Position'  PREFIX = 'ss'  VALUE = 'Bottom' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'LineStyle'  PREFIX = 'ss'  VALUE = 'Continuous' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Weight'  PREFIX = 'ss'  VALUE = '1' )."#EC NOTEXT

  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Border'   PARENT = R_BORDER  )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Position'  PREFIX = 'ss'  VALUE = 'Left' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'LineStyle'  PREFIX = 'ss'  VALUE = 'Continuous' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Weight'  PREFIX = 'ss'  VALUE = '1' )."#EC NOTEXT

  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Border'   PARENT = R_BORDER  )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Position'  PREFIX = 'ss'  VALUE = 'Top' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'LineStyle'  PREFIX = 'ss'  VALUE = 'Continuous' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Weight'  PREFIX = 'ss'  VALUE = '1' )."#EC NOTEXT

  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Border'   PARENT = R_BORDER  )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Position'  PREFIX = 'ss'  VALUE = 'Right' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'LineStyle'  PREFIX = 'ss'  VALUE = 'Continuous' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Weight'  PREFIX = 'ss'  VALUE = '1' )."#EC NOTEXT

* Style for Data

  R_STYLE1  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Style'   PARENT = R_STYLES  )."#EC NOTEXT

  R_STYLE1->SET_ATTRIBUTE_NS( NAME = 'ID'  PREFIX = 'ss'  VALUE = 'Data' )."#EC NOTEXT

  R_BORDER  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Borders'  PARENT = R_STYLE1 )."#EC NOTEXT

  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Border'   PARENT = R_BORDER  )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Position'  PREFIX = 'ss'  VALUE = 'Bottom' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'LineStyle'  PREFIX = 'ss'  VALUE = 'Continuous' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Weight'  PREFIX = 'ss'  VALUE = '1' )."#EC NOTEXT

  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Border'   PARENT = R_BORDER  )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Position'  PREFIX = 'ss'  VALUE = 'Left' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'LineStyle'  PREFIX = 'ss'  VALUE = 'Continuous' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Weight'  PREFIX = 'ss'  VALUE = '1' )."#EC NOTEXT

  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Border'   PARENT = R_BORDER  )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Position'  PREFIX = 'ss'  VALUE = 'Top' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'LineStyle'  PREFIX = 'ss'  VALUE = 'Continuous' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Weight'  PREFIX = 'ss'  VALUE = '1' )."#EC NOTEXT

  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Border'   PARENT = R_BORDER  )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Position'  PREFIX = 'ss'  VALUE = 'Right' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'LineStyle'  PREFIX = 'ss'  VALUE = 'Continuous' )."#EC NOTEXT

  R_FORMAT->SET_ATTRIBUTE_NS( NAME = 'Weight'  PREFIX = 'ss'  VALUE = '1' )."#EC NOTEXT

* Worksheet

  R_WORKSHEET = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Worksheet'  PARENT = L_ELEMENT_ROOT )."#EC NOTEXT

  R_WORKSHEET->SET_ATTRIBUTE_NS( NAME = 'Name'  PREFIX = 'ss'  VALUE = 'Sheet1' )."#EC NOTEXT

* Table

  R_TABLE = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Table'  PARENT = R_WORKSHEET )."#EC NOTEXT

  R_TABLE->SET_ATTRIBUTE_NS( NAME = 'FullColumns'  PREFIX = 'x'  VALUE = '1' )."#EC NOTEXT

  R_TABLE->SET_ATTRIBUTE_NS( NAME = 'FullRows'     PREFIX = 'x'  VALUE = '1' )."#EC NOTEXT

* Dynamic Cloumn STYLES & WIDTH (Column Formatting)

LOOP AT LT_DD03P_TAB INTO LS_DD03P WHERE FIELDNAME &lt;> 'MANDT'.

R_COLUMN = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(

NAME = 'Column'

PARENT = R_TABLE )."#EC NOTEXT

  CASE LS_DD03P-INTTYPE.

      WHEN 'I' OR 'N'.

*       General format

      WHEN 'P' OR 'F'.

        LV_VALUE = LS_DD03P-FIELDNAME.

        R_COLUMN->SET_ATTRIBUTE_NS(

                          NAME = 'Style'

                          PREFIX = 'ss'

                          VALUE = LV_VALUE )."#EC NOTEXT

    ENDCASE.

    LV_VALUE = ( LS_DD03P-OUTPUTLEN + 10 ) * 5.

    CONDENSE LV_VALUE NO-GAPS.

    R_COLUMN->SET_ATTRIBUTE_NS(

                      NAME = 'Width'

                      PREFIX = 'ss'

                      VALUE = LV_VALUE )."#EC NOTEXT

*    r_column->set_attribute_ns(

*                      name = 'AutoFitWidth'

*                      PREFIX = 'ss'

*                      value = '1' ).

1. ENDLOOP.

*Skip frst ROW

* Blank Row

  R_ROW = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Row'  PARENT = R_TABLE )."#EC NOTEXT

*Start form Second ROW

* Column Headers Row

  R_ROW = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Row'  PARENT = R_TABLE )."#EC NOTEXT

  R_ROW->SET_ATTRIBUTE_NS( NAME = 'AutoFitHeight'  PREFIX = 'ss'  VALUE = '1' )."#EC NOTEXT

*First Cloumn Serial number

* Sr. No.

  R_CELL = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Cell'  PARENT = R_ROW )."#EC NOTEXT

  R_CELL->SET_ATTRIBUTE_NS( NAME = 'StyleID'  PREFIX = 'ss'  VALUE = 'Header' )."#EC NOTEXT

  R_DATA = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Data'  VALUE = 'Sr. No.'  PARENT = R_CELL )."#EC NOTEXT

  R_DATA->SET_ATTRIBUTE_NS( NAME = 'Type'  PREFIX = 'ss' VALUE = 'String' )."#EC NOTEXT

*Fill hedaer TEXT name

  LOOP AT LT_DD03P_TAB INTO LS_DD03P WHERE FIELDNAME &lt;> 'MANDT'.

    R_CELL = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(

              NAME = 'Cell'

              PARENT = R_ROW )."#EC NOTEXT

    R_CELL->SET_ATTRIBUTE_NS( NAME = 'StyleID'  PREFIX = 'ss'  VALUE = 'Header' )."#EC NOTEXT

    LV_VALUE = LS_DD03P-DDTEXT.    "fieldname, scrtext_m etc.

    R_DATA = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(

              NAME = 'Data'

              VALUE = LV_VALUE

              PARENT = R_CELL )."#EC NOTEXT

    R_DATA->SET_ATTRIBUTE_NS(

                      NAME = 'Type'

                      PREFIX = 'ss'

                      VALUE = 'String' )."#EC NOTEXT

  ENDLOOP.

* Blank Row after Column Headers

  R_ROW = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(

             NAME = 'Row'

             PARENT = R_TABLE )."#EC NOTEXT

  LOOP AT LT_DD03P_TAB INTO LS_DD03P WHERE FIELDNAME &lt;> 'MANDT'.

    R_CELL = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(

               NAME = 'Cell'

               PARENT = R_ROW )."#EC NOTEXT

    R_CELL->SET_ATTRIBUTE_NS( NAME = 'StyleID'  PREFIX = 'ss'  VALUE = 'Data' )."#EC NOTEXT

  ENDLOOP.

*ITEM

  LOOP AT &lt;LT_DATA_TAB> ASSIGNING &lt;LS_DATA_LINE>.

    R_ROW = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(

    NAME = 'Row'

    PARENT = R_TABLE )."#EC NOTEXT

* Sr. No.

    R_CELL = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Cell'  PARENT = R_ROW )."#EC NOTEXT

    R_CELL->SET_ATTRIBUTE_NS( NAME = 'StyleID'  PREFIX = 'ss'  VALUE = 'Data' )."#EC NOTEXT

    LV_VALUE = SY-TABIX.

    CONDENSE LV_VALUE NO-GAPS.

    R_DATA = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Data'  VALUE = LV_VALUE   PARENT = R_CELL ).    "#EC NOTEXT       " Data

    R_DATA->SET_ATTRIBUTE_NS( NAME = 'Type'  PREFIX = 'ss'  VALUE = 'Number' ).              "#EC NOTEXT                 " Cell format

    LOOP AT LT_DD03P_TAB INTO LS_DD03P WHERE FIELDNAME &lt;> 'MANDT' .

      ASSIGN COMPONENT LS_DD03P-FIELDNAME OF STRUCTURE &lt;LS_DATA_LINE> TO &lt;FIELD>.

      CHECK SY-SUBRC IS INITIAL.

      R_CELL = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(

                  NAME = 'Cell'

                  PARENT = R_ROW )."#EC NOTEXT

      R_CELL->SET_ATTRIBUTE_NS( NAME = 'StyleID'  PREFIX = 'ss'  VALUE = 'Data' )."#EC NOTEXT

      LV_VALUE = &lt;FIELD>.

      CONDENSE LV_VALUE NO-GAPS.

      CASE LS_DD03P-INTTYPE.

      WHEN 'I' OR 'P' OR 'F' OR 'N'.

        LV_TYPE = 'Number'.             "#EC NOTEXT

        LV_VALUE = &lt;FIELD>.

        CONDENSE LV_VALUE NO-GAPS.

      WHEN 'D' OR 'T'.

        LV_TYPE = 'String'.                     "#EC NOTEXT

        MOVE &lt;FIELD> TO LV_TEXT.               "WRITE &lt;FIELD> TO LV_TEXT.

        LV_VALUE = LV_TEXT.

      WHEN OTHERS.

*          LV_VALUE = &lt;field>.    "Without conversion exit

        MOVE &lt;FIELD> TO LV_TEXT.               " WRITE &lt;FIELD> TO LV_TEXT.

        SHIFT LV_TEXT LEFT DELETING LEADING SPACE.

        LV_VALUE = LV_TEXT.

        LV_TYPE = 'String'.                 "#EC NOTEXT

    ENDCASE.

      R_DATA = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(

                 NAME = 'Data'

                 VALUE = LV_VALUE

                 PARENT = R_CELL )."#EC NOTEXT

*     Cell format

      R_DATA->SET_ATTRIBUTE_NS(

                        NAME = 'Type'

                        PREFIX = 'ss'

                        VALUE = LV_TYPE )."#EC NOTEXT

    ENDLOOP.

  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 = LT_XML_TABLE ).

* Rendering the Document

  L_RENDERER = L_IXML->CREATE_RENDERER( OSTREAM  = L_OSTREAM  DOCUMENT = L_DOCUMENT ).

  LV_RC = L_RENDERER->RENDER( ).

* Saving the XML Document

  LV_XML_SIZE = L_OSTREAM->GET_NUM_WRITTEN_RAW( ).

*---------------------------------------------- Convert XML to Excel ---------------------------------

REFRESH LT_ATTACH.

LOOP AT LT_XML_TABLE INTO  LS_XML.

        CLEAR LS_ATTACH.

        LS_ATTACH = LS_XML.

        APPEND LS_ATTACH TO LT_ATTACH.

      ENDLOOP.

IV_ATTACHMENT_X = LT_ATTACH.

endmethod.

Message was edited by: Thomas Zloch

[ThomasZloch]

If this is your own code, you could think about adding it as a document to the ABAP spaces, or to the SCN ABAP wiki.

Thomas

[Former Member]

Thomas,

               Thank you. I'll do it

[Former Member]

Hi kelvin,

         if possible post the code which you developed for the above requirement. i am new to abap.

        also don't know  the oops abap.<removed by moderator>.

Message was edited by: Thomas Zloch

[Former Member]

Ha ha....well this all I could do for you.....U have to figure it out on your own Srinivas.....

[Former Member]

Hi kelvin,

         Finally  i have done the above requirement .   it is working fine. check the below code <removed by moderator>.

              i tried a lot for this.

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

Message was edited by: Thomas Zloch

[Former Member]

Hii Srinivas, i used this code that u have given above, as it is and only replaced the email address with mine. But no mail was sent. Do i need to add more code to this? please tell me.

[former_member183990]

Hi Anushka,

Did you checked whether your user id is assigned with an email id and please check SCOT setting and RZ10 setting is done proper with the help of your basis person.

And for instance try doing this go to SM04, at top there is an option provided by Standard SAP to send mail, try sending mail through standard and then if it works then please check the code else if it doesn't works then we have to check the Basis settings

Regards

S.Janagar

[Former Member]

Hi Srinivas,

I really want to thank you very much, i got lot of relief , I like to ask you from where do you get all the properties like font , color borders etc, ....  where did u find the values for these things,

and I have one more thing I need to extract data from XML file  in to internal table and then update the database tables.can you please suggest me easy way

i tried with function module

CALL FUNCTION 'SMUM_XML_PARSE'

     EXPORTING

      xml_input = g_xmldata

     TABLES

      xml_table = g_t_xml_info

      return    = g_t_return

     EXCEPTIONS

       OTHERS    = 0.

but this function module is giving out errors , eventhough the XML file is correct format .

any better methods u can suggest.