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

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.

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.