World of ABAP: Convert ALV to Excel and send it by email

One of the most requested abilities for an report is the possibility to run it as a job and send the output to a given user. However, the spool output of most ALV reports is unusable as is. To solve this problem, I'll show a simple class that takes an internal table, a fieldcatalog and a user email to create a xml excel file from the field catalog with the contents of the internal table and forward it to the email supplied.
I should warn that the excel output isn't something fancy. It's just a simple table. The class can be greatly enhanced.

1 - Create a class:

2 - Create the class atributes:

3 - Create the class methods:

4 - Create the method parameters:

5 - Insert the appropriate code in each method:

method CLOSE.

data: l_xml_line like line of wt_xml.

clear: l_xml_line.

l_xml_line = im_element.
  append l_xml_line to wt_xml.

clear: l_xml_line. free: l_xml_line.

endmethod.
method CLOSE_ROW.

close( im_element = '</Row>' ).

endmethod.
method CLOSE_TABLE.

close( im_element = '</Table>' ).

endmethod.
method CLOSE_WORKSHEET.

close( im_element = '</Worksheet>' ).

endmethod.
method CLOSE_XML_HEADER.

close( im_element = '</Workbook>' ).

endmethod.
method CONVERT_TABLE_TO_EXCEL_XML.

* Excel XML file created according to: http://wiki.sdn.sap.com/wiki/display/Snippets/XML+TAGS+simplifies+Excel+Download

data: l_formated_value(255),
      l_num_columns type i,
      l_comp_idx    type sy-tabix.

field-symbols: <l_data_line>    type any,
               <l_value>        type any,
               <l_fieldcatalog> type any,
               <l_fieldname>    type any,
               <l_table_name>   type any,
               <l_field_name>   type any,
               <l_xml_line>     type any,
               <l_xml>          like line of wt_xml.

clear: l_num_columns, l_formated_value, l_comp_idx, wt_xml, wt_xml[].

describe table it_fieldcatalog lines l_num_columns.
  open_xml_header( ).
    open_worksheet( im_name = 'PO Report' ).
      open_table( ).
        loop at it_data assigning <l_data_line>.
          if sy-tabix = 1.
            open_row( ).
              loop at it_fieldcatalog assigning <l_fieldcatalog>.
                assign component 'SELTEXT_M' of structure <l_fieldcatalog> to <l_value>.
                if not sy-subrc is initial.
                  assign component 'SCRTEXT_M' of structure <l_fieldcatalog> to <l_value>.
                endif.
                if <l_value> is assigned.
                  if <l_value> is initial.
                    assign component 'REF_TABNAME'   of structure <l_fieldcatalog> to <l_table_name>.
                    if <l_table_name> is assigned.
                      assign component 'REF_FIELDNAME' of structure <l_fieldcatalog> to <l_field_name>.
                      if <l_field_name> is assigned.
                        select single scrtext_m into <l_value> from dd03m
                                where tabname    = <l_table_name>
                                  and fieldname  = <l_field_name>
                                  and ddlanguage = sy-langu.
                      endif.
                    endif.
                  endif.
                  insert_cell( exporting im_value = <l_value> ).
                endif.
              endloop.
            close_row( ).
          endif.
          open_row( ).
            if it_fieldcatalog[] is initial.
              clear: l_comp_idx.
              do.
                add 1 to l_comp_idx.
                assign component l_comp_idx of structure <l_data_line> to <l_value>.
                if sy-subrc is initial.
                  write: <l_value> to l_formated_value.
                  shift l_formated_value left deleting leading space.
                  if <l_value> is initial.
                    clear: l_formated_value.
                  endif.
                  insert_cell( exporting im_value = l_formated_value ).
                else.
                  exit.
                endif.
              enddo.
            else.
              loop at it_fieldcatalog assigning <l_fieldcatalog>.
                assign component 'FIELDNAME' of structure <l_fieldcatalog> to <l_fieldname>.
                if sy-subrc is initial.
                  assign component <l_fieldname> of structure <l_data_line> to <l_value>.
                  if sy-subrc is initial.
                    write: <l_value> to l_formated_value.
                    shift l_formated_value left deleting leading space.
                    if <l_value> is initial.
                      clear: l_formated_value.
                    endif.
                    insert_cell( exporting im_value = l_formated_value ).
                  endif.
                endif.
              endloop.
            endif.
          close_row( ).
        endloop.
      close_table( ).
    close_worksheet( ).
  close_xml_header( ).

data: l_xml_line type ref to data.
  create data l_xml_line like line of et_xml.
  assign l_xml_line->* to <l_xml_line>.
  if sy-subrc is initial.
    loop at wt_xml assigning <l_xml>.
      <l_xml_line> = <l_xml>.
      insert <l_xml_line> into table et_xml.
      clear: <l_xml_line>.
    endloop.
  endif.

clear: l_num_columns, l_formated_value, l_comp_idx, wt_xml, wt_xml[].
  free:  l_num_columns, l_formated_value, l_comp_idx, wt_xml, wt_xml[].

endmethod.
method INSERT_CELL.

data: l_data(128).

clear: l_data.

open( im_element = '<cell>').
    concatenate '<data ss:Type="String">' im_value into l_data.
    open( im_element = l_data ).
    close( im_element = '</Data>' ).
  close( im_element = '</Cell>' ).

clear: l_data. free: l_data.

endmethod.
method OPEN.

data: l_xml_line like line of wt_xml.

clear: l_xml_line.

l_xml_line = im_element.
  append l_xml_line to wt_xml.

clear: l_xml_line. free: l_xml_line.

endmethod.
method OPEN_ROW.

open( im_element = '<row>' ).

endmethod.
method OPEN_TABLE.

open( im_element = '<table>' ).

endmethod.
method OPEN_WORKSHEET.

data: l_data(128).

clear: l_data.

l_data = '<worksheet ss:Name="&">'.
  replace '&' with im_name into l_data.
  open( im_element = l_data ).

clear: l_data. free: l_data.

endmethod.
method OPEN_XML_HEADER.

data: l_xml_line like line of wt_xml.

clear: l_xml_line.

l_xml_line = '<?xml version="1.0" encoding="utf-8" ?>'.
  append l_xml_line to wt_xml.
  l_xml_line = '<?mso-application progid="Excel.Sheet"?>'.
  append l_xml_line to wt_xml.
  l_xml_line = '<workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"'.
  append l_xml_line to wt_xml.
  l_xml_line = 'xmlns:o="urn:schemas-microsoft-com:office:office"'.
  append l_xml_line to wt_xml.
  l_xml_line = 'xmlns:x="urn:schemas-microsoft-com:office:excel"'.
  append l_xml_line to wt_xml.
  l_xml_line = 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'.
  append l_xml_line to wt_xml.
  l_xml_line = 'xmlns:html="http://www.w3.org/TR/REC-html40">'.
  append l_xml_line to wt_xml.

clear: l_xml_line. free: l_xml_line.

endmethod.
method SEND_TABLE_AS_EXCEL_BY_EMAIL.

* mail sent as described here: http://forums.sdn.sap.com/thread.jspa?threadID=227475&start=30&tstart=0

data: lt_binary_content type solix_tab,
      lt_text           type bcsy_text,
      lt_xml            type srm_t_solisti1,
      l_sender          type ref to if_sender_bcs,
      l_recipient       type ref to if_recipient_bcs,
      l_send_request    type ref to cl_bcs,
      l_document        type ref to cl_document_bcs,
      l_xml_string      type string,
      l_xl_content      type xstring,
      l_subject         type so_obj_des,
      l_len             type sood-objlen,
      l_length          type i,
      l_atta_sub        type sood-objdes,
      begin of l_mail_address,
        ad_smtpadr type ad_smtpadr,
      end of l_mail_address.

field-symbols: <l_xml> like line of lt_xml.

if im_filename is initial.
    raise NO_FILENAME.
  endif.
  if im_subject is initial.
    raise NO_SUBJECT.
  endif.
  if it_mail_addresses[] is initial.
    raise NO_RECEIVERS.
  endif.

zcl_excel=>convert_table_to_excel_xml( exporting it_data         = it_data
                                                   it_fieldcatalog = it_fieldcatalog
                                         importing et_xml          = lt_xml ).
  if not sy-subrc is initial.
    raise ERROR_GENERATING_XML.
  else.
    if lt_xml[] is initial.
      raise NO_XML.
    endif.

loop at lt_xml assigning <l_xml>.
      if sy-tabix = 1.
        l_xml_string = <l_xml>.
      else.
        concatenate l_xml_string <l_xml> into l_xml_string separated by cl_abap_char_utilities=>cr_lf.
      endif.
    endloop.
    call function 'SCMS_STRING_TO_XSTRING'
      exporting
        text     = l_xml_string
        mimetype = 'text/xml; charset=utf-8'
      importing
        buffer   = l_xl_content.
    concatenate cl_abap_char_utilities=>byte_order_mark_utf8 l_xl_content into l_xl_content in byte mode.
    call function 'SCMS_XSTRING_TO_BINARY'
      exporting
        buffer        = l_xl_content
      importing
        output_length = l_length
      tables
        binary_tab    = lt_binary_content.
    move: l_length to l_len .
    try.
      clear: l_send_request. free: l_send_request.

l_send_request = cl_bcs=>create_persistent( ).
      clear l_document .
      append 'Description' TO lt_text.
      l_subject = im_subject.
      l_document = cl_document_bcs=>create_document( i_type = 'HTM' i_text = lt_text i_subject = l_subject ).
      l_atta_sub = im_filename.
      l_document->add_attachment( exporting  i_attachment_type    = 'xls'
                                             i_attachment_subject = l_atta_sub
                                             i_attachment_size    = l_len
                                             i_att_content_hex    = lt_binary_content ).
      l_send_request->set_document( l_document ).

CLEAR l_sender .
      l_sender = cl_cam_address_bcs=>create_internet_address( 'po_report@sap.colepccl' ).
      l_send_request->set_sender( exporting i_sender = l_sender ).
      loop at it_mail_addresses into l_mail_address.
        l_recipient = cl_cam_address_bcs=>create_internet_address( l_mail_address-ad_smtpadr ).
        l_send_request->add_recipient( exporting i_recipient = l_recipient
                                                 i_express   = 'X' ).
      endloop.
      l_send_request->set_status_attributes( exporting i_requested_status = 'E'
                                                       i_status_mail      = 'E' ).
      l_send_request->set_send_immediately( 'X' ).
      if l_send_request->send( i_with_error_screen = ' ' ) = 'X'.
        commit work.
      endif.
      catch cx_bcs.
        raise ERROR_SENDING_MAIL.
        exit.
    endtry.
  endif.

clear: lt_binary_content, lt_binary_content[], lt_text,  lt_text[],   lt_xml,     lt_xml[],
         l_send_request,    l_xl_content,        l_sender, l_recipient, l_document, l_xml_string,
         l_subject, l_len,  l_mail_address,      l_length, l_atta_sub.
  free:  lt_binary_content, lt_binary_content[], lt_text,  lt_text[],   lt_xml,     lt_xml[],
         l_send_request,    l_xl_content,        l_sender, l_recipient, l_document, l_xml_string,
         l_subject, l_len,  l_mail_address,      l_length, l_atta_sub.

endmethod.

World of ABAP

Thursday, October 31, 2013

Convert ALV to Excel and send it by email

No comments:

Post a Comment

Total Pageviews