World of ABAP

Thursday, October 31, 2013

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.

Friday, October 25, 2013

Talking ABAP

Just for fun, you can make sap talk to the user or, for more serious work, you can give hearing aids to alert the users for some event or to help them be more aware of what is happening.

report ytest_lq00.

class lcl_speech definition.

public section.

methods: constructor,
           speak importing i_text type string.

private section.

data: w_voice type ole2_object.

endclass.
class lcl_speech implementation.

method constructor.

data: begin of l_data,
            ole   type ole2_object,
            voice type ole2_object,
          end of l_data.

clear: w_voice. free: w_voice..
      create object w_voice 'SAPI.SpVoice'.

endmethod.

method speak.

data: l_ole type ole2_object.

clear: l_ole. free: l_ole.
    if not w_voice is initial.
      call method of w_voice 'Speak' = l_ole exporting #1 = i_text.
      free object l_ole.
    endif.
    clear: l_ole. free: l_ole.

endmethod.

endclass.

start-of-selection.

data: l_speech type ref to lcl_speech.

clear: l_speech. free: l_speech.
  if not l_speech is bound.
    create object l_speech.
  endif.
  if l_speech is bound.
    l_speech->speak( 'Hello!' ).
    l_speech->speak( 'Let''''s speak a little bit.' ).
    l_speech->speak( 'Goodbye!' ).
    clear: l_speech. free: l_speech.
  endif.

As a final thought, I should say that, this is a simple example. As most of the times, you have plenty of room to improve.
Don't forget that this is an instanciation of a windows object. It will only work with SAP Gui for windows.

Remark: depending on the security settings of your SAP GUI configuration, a popup can appear asking for authorization to instanciate a windows object.

Thursday, October 24, 2013

Batch Input with Objects

Some times, we get such a requirement that forces us to use batch input for creation/changing of some SAP object/document. Usually, the resulting code isn't that elegant. In this post, I'll show how to use a ABAP Class to process call transaction and allow to future improvements that afects every program that uses it, without the need to change those same programs.
Those improvements could be:

Create Batch input in DB for latter processing
Create Enhanced Logs
Start Workflows
Etc

1 - Create an ABAP Class in transaction SE24, select an apropriate package:

2 - Create the following attributes:

3 - Create these methods:

4 - Pick the relevant code for the each method:

method CONSTRUCTOR.

clear: w_options, w_transaction, wt_bdcdata, wt_bdcdata[], wt_messages, wt_messages[].
  w_options-dismode  = im_dismode.
  w_options-updmode  = im_updmode.
  w_options-defsize  = im_defsize.
  w_options-racommit = im_racommit.
  w_transaction      = im_tcode.

endmethod.
method SET_CURSOR.

data: l_bdcdata like line of wt_bdcdata.

clear: l_bdcdata.
  l_bdcdata-fnam = 'BDC_CURSOR'.
  l_bdcdata-fval = im_field.
  append l_bdcdata to wt_bdcdata.
  clear: l_bdcdata.

endmethod.
method SET_FIELD.

data: l_bdcdata like line of wt_bdcdata.

clear: l_bdcdata.
  l_bdcdata-fnam = im_field.
  l_bdcdata-fval = im_value.
  append l_bdcdata to wt_bdcdata.
  clear: l_bdcdata.

endmethod.
method SET_OKCODE.

data: l_bdcdata like line of wt_bdcdata.

clear: l_bdcdata.
  l_bdcdata-fnam = 'BDC_OKCODE'.
  l_bdcdata-fval = im_okcode.
  append l_bdcdata to wt_bdcdata.
  clear: l_bdcdata.

endmethod.
method SET_SCREEN.

data: l_bdcdata like line of wt_bdcdata.

clear: l_bdcdata.
  l_bdcdata-program  = im_program.
  l_bdcdata-dynpro   = im_screen.
  l_bdcdata-dynbegin = 'X'.
  append l_bdcdata to wt_bdcdata.
  clear: l_bdcdata.

endmethod.
method START.

field-symbols: <l_message> like line of wt_messages.

call transaction w_transaction using wt_bdcdata options from w_options messages into wt_messages.
  if sy-subrc is initial.
    loop at wt_messages assigning <l_message> where msgtyp ca 'AXE'.
      exit.
    endloop.
    if sy-subrc is initial.
      re_success = ' '.
    else.
      re_success = 'X'.
    endif.
  else.
    re_success = ' '.
  endif.

endmethod.

5 - Save and activate the class.

6 - To help generate the code, the following program can be used:

*&---------------------------------------------------------------------*
*& Report  Y_CREATE_BATCH_INPUT
*&---------------------------------------------------------------------*
report zds_create_batch_input.

constants: c_data(132)    value 'data: & type ref to zdsbc_batch_input.',
           c_clear(132)   value '  clear: &.',
           c_free(132)    value '  free: &.',
           c_create1(132) value '  create object &1 exporting im_tcode    = ''&2''.',
           c_create2(132) value '*                            im_dismode  = ''N''',
           c_create3(132) value '*                            im_updmode  = ''S''',
           c_create4(132) value '*                            im_defsize  = ''X''',
           c_create5(132) value '*                            im_racommit = ''X''',
           c_screen(132)  value '  &1->set_screen( exporting im_program = ''&2'' im_screen = ''&3'' ).',
           c_okcode(132)  value '  &1->set_okcode( exporting im_okcode  = ''&2'' ).',
           c_cursor(132)  value '  &1->set_cursor( exporting im_field   = ''&2'' ).',
           c_field(132)   value '  &1->set_field(  exporting im_field   = ''&2'' im_value = ''&3'' ).',
           c_run1(132)    value '  if &->start( ) = ''X''.',
           c_run2(132)    value '*   Success!!!',
           c_run3(132)    value '  else.',
           c_run4(132)    value '*   Error!!!',
           c_run5(132)    value '  endif.',
           c_info(132)    value '* messages in &->wt_messages[].'.

data: begin of wt_data occurs 0,
        line(132),
      end of wt_data.

data: wt_bdcdata type table of bdcdata.

parameters: p_record type apqi-groupid obligatory,
            p_object type char30       obligatory lower case.

start-of-selection.

data: l_qid    type apqi-qid,
      l_value  like line of wt_data,
      l_length type i.

field-symbols: <l_bdcdata> like line of wt_bdcdata.

select single qid into l_qid from apqi where datatyp = '%BDC'
                                           and mandant = sy-mandt
                                           and groupid = p_record.
  if sy-subrc is initial.
    call function 'BDC_OBJECT_READ'
      exporting
        queue_id         = l_qid
      tables
        dynprotab        = wt_bdcdata
      exceptions
        not_found        = 1
        system_failure   = 2
        invalid_datatype = 3
        others           = 4.
    if sy-subrc is initial.
      l_length = strlen( p_object ). l_value = c_data.  replace '&' with p_object(l_length) into l_value. append l_value to wt_data.
      clear: l_value. append l_value to wt_data.
      l_length = strlen( p_object ). l_value = c_clear. replace '&' with p_object(l_length) into l_value. append l_value to wt_data.
      l_length = strlen( p_object ). l_value = c_free.  replace '&' with p_object(l_length) into l_value. append l_value to wt_data.
      clear: l_value. append l_value to wt_data.
      loop at wt_bdcdata assigning <l_bdcdata>.
        if <l_bdcdata>-dynbegin = 'T'.
          l_value = c_create1.
          l_length = strlen( p_object ). replace '&1' with p_object(l_length)         into l_value.
          l_length = strlen( <l_bdcdata>-fnam ). replace '&2' with <l_bdcdata>-fnam(l_length) into l_value.
          append l_value to wt_data.
          l_value = c_create2. append l_value to wt_data.
          l_value = c_create3. append l_value to wt_data.
          l_value = c_create4. append l_value to wt_data.
          l_value = c_create5. append l_value to wt_data.
        elseif <l_bdcdata>-dynbegin = 'X'.
          l_value = c_screen.
          l_length = strlen( p_object ). replace '&1' with p_object(l_length)            into l_value.
          l_length = strlen( <l_bdcdata>-program ). replace '&2' with <l_bdcdata>-program(l_length) into l_value.
          l_length = strlen( <l_bdcdata>-dynpro ). replace '&3' with <l_bdcdata>-dynpro(l_length)  into l_value.
          append l_value to wt_data.
        elseif <l_bdcdata>-fnam = 'BDC_OKCODE'.
          l_value = c_okcode.
          l_length = strlen( p_object ). replace '&1' with p_object(l_length)            into l_value.
          l_length = strlen( <l_bdcdata>-fval ). replace '&2' with <l_bdcdata>-fval(l_length)    into l_value.
          append l_value to wt_data.
        elseif <l_bdcdata>-fnam = 'BDC_CURSOR'.
          l_value = c_cursor.
          l_length = strlen( p_object ). replace '&1' with p_object(l_length)            into l_value.
          l_length = strlen( <l_bdcdata>-fval ). replace '&2' with <l_bdcdata>-fval(l_length)    into l_value.
          append l_value to wt_data.
        elseif <l_bdcdata>-fnam <> 'BDC_SUBSCR'.
          shift <l_bdcdata>-fval left deleting leading space.
          l_value = c_field.
          l_length = strlen( p_object ). replace '&1' with p_object(l_length)            into l_value.
          l_length = strlen( <l_bdcdata>-fnam ). replace '&2' with <l_bdcdata>-fnam(l_length)    into l_value.
          l_length = strlen( <l_bdcdata>-fval ). replace '&3' with <l_bdcdata>-fval(l_length)    into l_value.
          append l_value to wt_data.
        endif.
      endloop.
      l_value = c_run1.
      l_length = strlen( p_object ). replace '&' with p_object(l_length) into l_value. append l_value to wt_data.
      l_value = c_run2. append l_value to wt_data.
      l_value = c_run3. append l_value to wt_data.
      l_value = c_run4. append l_value to wt_data.
      l_value = c_run5. append l_value to wt_data.
      l_value = c_info.
      l_length = strlen( p_object ). replace '&' with p_object(l_length) into l_value. append l_value to wt_data.
      clear: l_value. append l_value to wt_data.
      l_length = strlen( p_object ). l_value = c_clear. replace '&' with p_object(l_length) into l_value. append l_value to wt_data.
      l_length = strlen( p_object ). l_value = c_free.  replace '&' with p_object(l_length) into l_value. append l_value to wt_data.
      editor-call for wt_data display-mode.
    endif.
  else.

endif.

The input for this program is the SHDB record and the program variable name.

7 - Example:

SHDB Record:

8 - Program Input

9 - Generated Code:

data: l_batch_input type ref to zdsbc_batch_input.

clear: l_batch_input.
  free: l_batch_input.

create object l_batch_input exporting im_tcode    = 'VL_COMPLETE'.
*                            im_dismode  = 'N'
*                            im_updmode  = 'S'
*                            im_defsize  = 'X'
*                            im_racommit = 'X'
  l_batch_input->set_screen( exporting im_program = 'SHP_DELIVERY_COMPLETE' im_screen = '1000' ).
  l_batch_input->set_cursor( exporting im_field   = 'S_ERDAT-HIGH' ).
  l_batch_input->set_okcode( exporting im_okcode  = '=ONLI' ).
  l_batch_input->set_field(  exporting im_field   = 'S_VBELN-LOW' im_value = '2102001998' ).
  l_batch_input->set_field(  exporting im_field   = 'S_ERDAT-LOW' im_value = '01.07.2013' ).
  l_batch_input->set_field(  exporting im_field   = 'S_ERDAT-HIGH' im_value = '31.07.2013' ).
  l_batch_input->set_field(  exporting im_field   = 'P_AEDAT' im_value = 'X' ).
  l_batch_input->set_screen( exporting im_program = 'SAPMSSY0' im_screen = '0120' ).
  l_batch_input->set_cursor( exporting im_field   = '04/03' ).
  l_batch_input->set_okcode( exporting im_okcode  = '=&ALL' ).
  l_batch_input->set_screen( exporting im_program = 'SAPMSSY0' im_screen = '0120' ).
  l_batch_input->set_cursor( exporting im_field   = '04/03' ).
  l_batch_input->set_okcode( exporting im_okcode  = '=COMP' ).
  l_batch_input->set_screen( exporting im_program = 'SAPLSPO1' im_screen = '0500' ).
  l_batch_input->set_okcode( exporting im_okcode  = '=OPT1' ).
  if l_batch_input->start( ) = 'X'.
*   Success!!!
  else.
*   Error!!!
  endif.
* messages in l_batch_input->wt_messages[].

clear: l_batch_input.
  free: l_batch_input.

Final remarks:
As you can see, the code is more understable and simplier to use.
The SHDB record name, must be unique, since it is selected by name.
With the generated code, you must add the coding for variable selection and remove unecessary inputs. The program only creates code for the relevant batch input lines.