Source Sapurlus

Tabloyu XML Yardımıyla Excel Olarak Email Atma

Internal tablo excel olarak attachmentlara eklenebilir. Satır renklendirmesi, koşula göre hücre renklendirmesi, hücre çizgileri ekleme gibi excel özellikleri kullanılabilir.

Kaynak: https://wiki.scn.sap.com/wiki/display/Snippets/Formatted+Excel+as+Email+Attachment?original_fqdn=wiki.sdn.sap.com

me->get_data( ).
me->process_xml_data( ).
me->send_mail( ).

METHOD process_xml_data.
  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: lr_attribute          TYPE REF TO if_ixml_attribute,
        lr_element_root       TYPE REF TO if_ixml_element,
        lr_element_properties TYPE REF TO if_ixml_element,
        lr_element            TYPE REF TO if_ixml_element,
        lr_worksheet          TYPE REF TO if_ixml_element,
        lr_table              TYPE REF TO if_ixml_element,
        lr_column             TYPE REF TO if_ixml_element,
        lr_row                TYPE REF TO if_ixml_element,
        lr_cell               TYPE REF TO if_ixml_element,
        lr_data               TYPE REF TO if_ixml_element,
        lr_styles             TYPE REF TO if_ixml_element,
        lr_style              TYPE REF TO if_ixml_element,
        lr_style1             TYPE REF TO if_ixml_element,
        lr_style_y            TYPE REF TO if_ixml_element,
        lr_style_s            TYPE REF TO if_ixml_element,
        lr_style_k            TYPE REF TO if_ixml_element,
        lr_format             TYPE REF TO if_ixml_element,
        lr_format_y           TYPE REF TO if_ixml_element,
        lr_format_k           TYPE REF TO if_ixml_element,
        lr_format_s           TYPE REF TO if_ixml_element,
        lr_border             TYPE REF TO if_ixml_element,
        lv_value              TYPE string,
        lv_xml_size           TYPE i,
        lv_rc                 TYPE i,
        lv_valuec             TYPE char20.

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

* Creating the DOM Object Model
  l_document = l_ixml->create_document( ).

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

  lr_attribute = l_document->create_namespace_decl( name = `ss`  prefix = `xmlns`  uri = `urn:schemas-microsoft-com:office:spreadsheet` ).
  lr_element_root->set_attribute_node( lr_attribute ).

  lr_attribute = l_document->create_namespace_decl( name = `x`  prefix = `xmlns`  uri = `urn:schemas-microsoft-com:office:excel` ).
  lr_element_root->set_attribute_node( lr_attribute ).

* Create node for document properties.
  lr_element_properties = l_document->create_simple_element( name = `AMBAR_REPORT`  parent = lr_element_root ).
  lv_value = sy-uname.
  l_document->create_simple_element( name = `Author`  value = lv_value  parent = lr_element_properties  ).

* Styles
  lr_styles = l_document->create_simple_element( name = `Styles`  parent = lr_element_root  ).

* Style for Header
  lr_style  = l_document->create_simple_element( name = `Style`   parent = lr_styles  ).
  lr_style->set_attribute_ns( name = `ID`  prefix = `ss`  value = `Header` ).

  lr_format  = l_document->create_simple_element( name = `Font`  parent = lr_style  ).
  lr_format->set_attribute_ns( name = `Bold`  prefix = `ss`  value = `1` ).

  lr_format  = l_document->create_simple_element( name = `Interior` parent = lr_style  ).
  lr_format->set_attribute_ns( name = `Color`   prefix = `ss`  value = `#FFCC00` ).
  lr_format->set_attribute_ns( name = `Pattern` prefix = `ss`  value = `Solid` ).

  lr_format  = l_document->create_simple_element( name = `Alignment`  parent = lr_style  ).
  lr_format->set_attribute_ns( name = `Vertical`  prefix = `ss`  value = `Center` ).
  lr_format->set_attribute_ns( name = `WrapText`  prefix = `ss`  value = `1` ).

  lr_border  = l_document->create_simple_element( name = `Borders`  parent = lr_style ).
  lr_format  = l_document->create_simple_element( name = `Border`   parent = lr_border  ).
  lr_format->set_attribute_ns( name = `Position`  prefix = `ss`  value = `Bottom` ).
  lr_format->set_attribute_ns( name = `LineStyle`  prefix = `ss`  value = `Continuous` ).
  lr_format->set_attribute_ns( name = `Weight`  prefix = `ss`  value = `1` ).

  lr_format  = l_document->create_simple_element( name = `Border`   parent = lr_border  ).
  lr_format->set_attribute_ns( name = `Position`  prefix = `ss`  value = `Left` ).
  lr_format->set_attribute_ns( name = `LineStyle`  prefix = `ss`  value = `Continuous` ).
  lr_format->set_attribute_ns( name = `Weight`  prefix = `ss`  value = `1` ).

  lr_format  = l_document->create_simple_element( name = `Border`   parent = lr_border  ).
  lr_format->set_attribute_ns( name = `Position`  prefix = `ss`  value = `Top` ).
  lr_format->set_attribute_ns( name = `LineStyle`  prefix = `ss`  value = `Continuous` ).
  lr_format->set_attribute_ns( name = `Weight`  prefix = `ss`  value = `1` ).

  lr_format  = l_document->create_simple_element( name = `Border`   parent = lr_border  ).
  lr_format->set_attribute_ns( name = `Position`  prefix = `ss`  value = `Right` ).
  lr_format->set_attribute_ns( name = `LineStyle`  prefix = `ss`  value = `Continuous` ).
  lr_format->set_attribute_ns( name = `Weight`  prefix = `ss`  value = `1` ).

* Style for Data
  lr_style1  = l_document->create_simple_element( name = `Style`   parent = lr_styles  ).
  lr_style1->set_attribute_ns( name = `ID`  prefix = `ss`  value = `Data` ).

  lr_border  = l_document->create_simple_element( name = `Borders`  parent = lr_style1 ).
  lr_format  = l_document->create_simple_element( name = `Border`   parent = lr_border  ).
  lr_format->set_attribute_ns( name = `Position`  prefix = `ss`  value = `Bottom` ).
  lr_format->set_attribute_ns( name = `LineStyle`  prefix = `ss`  value = `Continuous` ).
  lr_format->set_attribute_ns( name = `Weight`  prefix = `ss`  value = `1` ).

  lr_format  = l_document->create_simple_element( name = `Border`   parent = lr_border  ).
  lr_format->set_attribute_ns( name = `Position`  prefix = `ss`  value = `Left` ).
  lr_format->set_attribute_ns( name = `LineStyle`  prefix = `ss`  value = `Continuous` ).
  lr_format->set_attribute_ns( name = `Weight`  prefix = `ss`  value = `1` ).

  lr_format  = l_document->create_simple_element( name = `Border`   parent = lr_border  ).
  lr_format->set_attribute_ns( name = `Position`  prefix = `ss`  value = `Top` ).
  lr_format->set_attribute_ns( name = `LineStyle`  prefix = `ss`  value = `Continuous` ).
  lr_format->set_attribute_ns( name = `Weight`  prefix = `ss`  value = `1` ).

  lr_format  = l_document->create_simple_element( name = `Border`   parent = lr_border  ).
  lr_format->set_attribute_ns( name = `Position`  prefix = `ss`  value = `Right` ).
  lr_format->set_attribute_ns( name = `LineStyle`  prefix = `ss`  value = `Continuous` ).
  lr_format->set_attribute_ns( name = `Weight`  prefix = `ss`  value = `1` ).

* Worksheet
  lr_worksheet = l_document->create_simple_element( name = `Worksheet`  parent = lr_element_root ).
  lr_worksheet->set_attribute_ns( name = `Name`  prefix = `ss`  value = `Sheet1` ).

* Table
  lr_table = l_document->create_simple_element( name = `Table`  parent = lr_worksheet ).
  lr_table->set_attribute_ns( name = `FullColumns`  prefix = `x`  value = `1` ).
  lr_table->set_attribute_ns( name = `FullRows`     prefix = `x`  value = `1` ).

* Column Formatting
  lr_column = l_document->create_simple_element( name = `Column`  parent = lr_table ).
  lr_column->set_attribute_ns( name = `Width`  prefix = `ss`  value = `60` ).

  lr_column = l_document->create_simple_element( name = `Column`  parent = lr_table ).
  lr_column->set_attribute_ns( name = `Width`  prefix = `ss`  value = `120` ).

  lr_column = l_document->create_simple_element( name = `Column`  parent = lr_table ).
  lr_column->set_attribute_ns( name = `Width`  prefix = `ss`  value = `60` ).

  lr_column = l_document->create_simple_element( name = `Column`  parent = lr_table ).
  lr_column->set_attribute_ns( name = `Width`  prefix = `ss`  value = `60` ).

* Column Headers Row
  lr_row = l_document->create_simple_element( name = `Row`  parent = lr_table ).
  lr_row->set_attribute_ns( name = `AutoFitHeight`  prefix = `ss`  value = `1` ).

* Tarih.
  lr_cell = l_document->create_simple_element( name = `Cell`  parent = lr_row ).
  lr_cell->set_attribute_ns( name = `StyleID`  prefix = `ss`  value = `Header` ).
  lr_data = l_document->create_simple_element( name = `Data`  value = `Tarih`  parent = lr_cell ).
  lr_data->set_attribute_ns( name = `Type`  prefix = `ss` value = `String` ).

* Açıklama
  lr_cell = l_document->create_simple_element( name = `Cell`  parent = lr_row ).
  lr_cell->set_attribute_ns( name = `StyleID`  prefix = `ss`  value = `Header` ).
  lr_data = l_document->create_simple_element( name = `Data`  value = `Açıklama`  parent = lr_cell ).
  lr_data->set_attribute_ns( name = `Type`  prefix = `ss` value = `String` ).

* Depo
  lr_cell = l_document->create_simple_element( name = `Cell`  parent = lr_row ).
  lr_cell->set_attribute_ns( name = `StyleID`  prefix = `ss`  value = `Header` ).
  lr_data = l_document->create_simple_element( name = `Data`  value = `Depo`  parent = lr_cell ).
  lr_data->set_attribute_ns( name = `Type`  prefix = `ss` value = `String` ).

* Doluluk %
  lr_cell = l_document->create_simple_element( name = `Cell`  parent = lr_row ).
  lr_cell->set_attribute_ns( name = `StyleID`  prefix = `ss`  value = `Header` ).
  lr_data = l_document->create_simple_element( name = `Data`  value = `Doluluk %`  parent = lr_cell ).
  lr_data->set_attribute_ns( name = `Type`  prefix = `ss` value = `String` ).

  lr_style_y  = l_document->create_simple_element( name = `Style`   parent = lr_styles  ).
  lr_style_y->set_attribute_ns( name = `ID`  prefix = `ss`  value = `Data_y` ).
  lr_format_y  = l_document->create_simple_element( name = `Interior` parent = lr_style_y  ).
  lr_format_y->set_attribute_ns( name = `Color`   prefix = `ss`  value = `#92D050` ). "Yeşil
  lr_format_y->set_attribute_ns( name = `Pattern` prefix = `ss`  value = `Solid` ).

  lr_style_s  = l_document->create_simple_element( name = `Style`   parent = lr_styles  ).
  lr_style_s->set_attribute_ns( name = `ID`  prefix = `ss`  value = `Data_s` ).
  lr_format_s  = l_document->create_simple_element( name = `Interior` parent = lr_style_s  ).
  lr_format_s->set_attribute_ns( name = `Color`   prefix = `ss`  value = `#FFFF00` ). "Sarı
  lr_format_s->set_attribute_ns( name = `Pattern` prefix = `ss`  value = `Solid` ).

lr_style_k = l_document->create_simple_element( name = `Style` parent = lr_styles  ).
lr_style_k->set_attribute_ns( name = `ID`  prefix = `ss`  value = `Data_k` ).

lr_format_k = l_document->create_simple_element( name = `Interior` parent = lr_style_k ).

lr_format_k->set_attribute_ns( name = `Color` prefix = `ss`  value = `#FF0000` ). "Kırmızı
lr_format_k->set_attribute_ns( name = `Pattern` prefix = `ss` value = `Solid` ).

* Data Table
  LOOP AT me->mt_100_itab ASSIGNING FIELD-SYMBOL().

lr_row = l_document->create_simple_element( name = `Row` parent = lr_table ).

"Tarih
lr_cell = l_document->create_simple_element( name = `Cell` parent = lr_row ).
lr_cell->set_attribute_ns( name = `StyleID`  prefix = `ss`  value = `Data` ).

CONCATENATE sy-datum+6(2) `.` sy-datum+4(2) `.` sy-datum(4) INTO lv_value.

lr_data = l_document->create_simple_element( name = `Data` value = lv_value parent = lr_cell ).
lr_data>set_attribute_ns( name = `Type`  prefix = `ss`  value = `String` ).

"Açıklama
lr_cell = l_document>create_simple_element( name = `Cell`  parent = lr_row ).
lr_cell->set_attribute_ns( name = `StyleID`  prefix = `ss`  value = `Data` ).
lv_value = -tanim.
lr_data = l_document->create_simple_element( name = `Data` value = lv_value   parent = lr_cell ).
lr_data->set_attribute_ns( name = `Type`  prefix = `ss`  value = `String` ).
"Depo
lr_cell = l_document->create_simple_element( name = `Cell` parent = lr_row ).
lr_cell->set_attribute_ns( name = `StyleID` prefix = `ss` value = `Data` ).
lv_value = -ambar.
lr_data = l_document->create_simple_element( name = `Data` value = lv_value parent = lr_cell ).

lr_data->set_attribute_ns( name = `Type` prefix = `ss` value = `String` ).

"Doluluk %
lr_cell = l_document->create_simple_element( name = `Cell` parent = lr_row ).
    IF -doluluk LT `90.000`. "Yeşil
lr_cell->set_attribute_ns( name = `StyleID` prefix = `ss` value = `Data_y` ).
    ELSEIF -doluluk LT `100.000`. "Sarı
lr_cell->set_attribute_ns( name = `StyleID` prefix = `ss` value = `Data_s` ).
    ELSEIF -doluluk GE `100.000`. "Kırmızı
lr_cell->set_attribute_ns( name = `StyleID` prefix = `ss` value = `Data_k` ).
    ENDIF.

    WRITE -doluluk TO lv_valuec.
    lv_value = lv_valuec.
lr_data = l_document->create_simple_element( name = `Data` value = lv_value parent = lr_cell ).

lr_data->set_attribute_ns( name = `Type` prefix = `ss` value = `String` ).

  ENDLOOP.
  UNASSIGN .

* 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 = gt_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( ).

ENDMETHOD.

METHOD send_mail.
    TYPES: BEGIN OF ty_email,
             smtp_addr TYPE ad_smtpadr,
           END OF ty_email.

    DATA: lt_objpack  TYPE STANDARD TABLE OF sopcklsti1,
          lt_objhead  TYPE TABLE OF solisti1,
          lt_objbin   TYPE TABLE OF solix,
          lt_imessage TYPE STANDARD TABLE OF solisti1,
          lt_reclist  TYPE STANDARD TABLE OF somlreci1,
          ls_objbin   LIKE LINE OF lt_objbin,
          ls_imessage LIKE LINE OF lt_imessage,
          ls_reclist  TYPE somlreci1,
          ls_doc_chng TYPE sodocchgi1,
          ls_orders   TYPE sfc_poco,
          ls_objpack  TYPE sopcklsti1,
          ls_objhead  TYPE solisti1.

    DATA: lt_bname TYPE TABLE OF zpp_t_0415,
          lt_email TYPE TABLE OF ty_email,
          ls_email LIKE LINE OF lt_email,
          lv_lines_bin     TYPE i,
          lv_message_lines TYPE i,
          lv_tarih(10),
          lv_kapasite(20),
          lv_mevdrm(20),
          lv_mevton(20),
          lv_ort_vb(20),
          lv_doluluk(20),
          lv_mev_vb(20).

    CHECK me->mt_100_itab IS NOT INITIAL.

    "Alıcılar
    SELECT * FROM zpp_t_0415 INTO TABLE lt_bname.

    IF NOT lt_bname[] IS INITIAL.
      SELECT smtp_addr FROM adr6
        INNER JOIN usr21 ON usr21~persnumber = adr6~persnumber
                        AND usr21~addrnumber = adr6~addrnumber
        INTO TABLE lt_email
        FOR ALL ENTRIES IN lt_bname
        WHERE bname EQ lt_bname-bname.
    ENDIF.

    LOOP AT lt_email INTO ls_email.
      CLEAR ls_reclist.
      ls_reclist-receiver = ls_email-smtp_addr.
      ls_reclist-rec_type = `U`.
      APPEND ls_reclist TO lt_reclist.
    ENDLOOP.

    CHECK lt_reclist[] IS NOT INITIAL.

    "The text for body of the mail
    CLEAR ls_imessage.
    ls_imessage-line = `Sayın Yetkili,`.
    APPEND ls_imessage TO lt_imessage.

    CLEAR ls_imessage.
    ls_imessage-line = ``.
    APPEND ls_imessage TO lt_imessage.

    CLEAR ls_imessage.
    CONCATENATE `Günlük Doluluk Oranları Raporunu`
                `ekteki excel dosyasında bulabilirsiniz`
INTO ls_imessage-line SEPARATED BY space.
    APPEND ls_imessage TO lt_imessage.

    "No of lines for body of mail
    DESCRIBE TABLE lt_imessage LINES lv_message_lines.

    READ TABLE lt_imessage INTO ls_imessage INDEX lv_message_lines.

    "document information
    ls_doc_chng-obj_name = `Excel`.
    ls_doc_chng-obj_langu    = sy-langu.
    ls_doc_chng-obj_descr = `Günlük Doluluk Oranları`.
    ls_doc_chng-sensitivty = `F`.
    "Calculating total size of doc
    ls_doc_chng-doc_size = ( lv_message_lines - 1 ) * 255 + strlen( ls_imessage-line ).

    "Creation of the Document Attachment
    LOOP AT gt_xml_table INTO DATA(ls_xml).
      CLEAR ls_objbin.
      ls_objbin-line = ls_xml-data.
      APPEND ls_objbin TO lt_objbin.
    ENDLOOP.

    DESCRIBE TABLE lt_objbin LINES lv_lines_bin. "No of lines for excel data

    ls_objhead = `Doluluk Oranları`.
    APPEND ls_objhead TO lt_objhead.

    " pack the data as RAW
    CLEAR ls_objpack-transf_bin. "Obj. to be transported not in binary form
    ls_objpack-head_start = 1.
    ls_objpack-head_num = 0.
    ls_objpack-body_start = 1.
    ls_objpack-body_num = lv_message_lines.
    ls_objpack-doc_type = `RAW`.
    APPEND ls_objpack TO lt_objpack.

    "Pack the data as excel
    ls_objpack-transf_bin = `X`.
    ls_objpack-head_start = 1.
    ls_objpack-head_num   = 0.
    ls_objpack-body_start = 1.
    ls_objpack-body_num   = lv_lines_bin.
    ls_objpack-doc_type   = `XLS`.
    ls_objpack-obj_name   = `Doluluk`.
    ls_objpack-obj_langu  = sy-langu.

    "Attachment name
    CONCATENATE `Doluluk_` sy-datum INTO ls_objpack-obj_descr.
    ls_objpack-doc_size = lv_lines_bin * 255.
    APPEND ls_objpack TO lt_objpack.

    "Sending mail
    CALL FUNCTION `SO_NEW_DOCUMENT_ATT_SEND_API1`
      EXPORTING
        document_data                    = ls_doc_chng
        put_in_outbox                    = `X`
        commit_work                      = `X`
      TABLES
        packing_list                     = lt_objpack
        object_header                    = lt_objhead
        contents_txt                     = lt_imessage
        contents_hex                     = lt_objbin
        receivers                        = lt_reclist
      EXCEPTIONS
        too_many_receivers               = 1
        document_not_sent                = 2
        document_type_not_exist          = 3
        operation_no_authorization       = 4
        parameter_error                  = 5
        x_error                          = 6
        enqueue_error                    = 7
        others                           = 8.

    IF sy-subrc EQ 0.
      MESSAGE s398(00) WITH ` Email Gönderildi!`.
    ENDIF.
  ENDMETHOD.