A recent discussion amongst the ABAP developers at my site dealt with the topic of how to arrange for a customized ABAP program running as a background job to have its ALV table output sent to an internet email address. During this discussion I learned the easy way to do this, requiring no ABAP programming at all, which simply requires the designation of a Spool recipient when scheduling the background job via transaction SM36:
After pursuing this further I soon found that for programs producing multiple ALV tables of output, each ALV table becomes its own independent output spool request, and the process noted above to have the output sent to a spool list recipient will cause only the last of these multiple output spools to be sent in an email. I began to look for a way to send them all, and this led me to ABAP2XLSX.
ABAP2XLSX
ABAP2XLSX is an open source facility enabling the creation of Excel spreadsheets from within ABAP programs.
◈ XLSX is a file extension for an open XML spreadsheet file format used by Microsoft Excel. Microsoft introduced the open XML standard to Microsoft Office in 2007 after a push from business users who saw the value in an open file format for transferring data between applications and other areas of working with the files.
The name ABAP2XLSX perfectly describes what it does, and after recently reading Paul Hardy’s excellent account of it in his book ABAP to the Future, I began exploring it further to see how it might enable converting ALV table output into an Excel spreadsheet.
I had found that it already was available in the SAP environment at my site. It is provided primarily as a collection of ABAP global classes and interfaces, but it has a multitude of simple report demonstration programs following the naming convention ZDEMO_EXCEL*. Of these, I had found ZDEMO_EXCEL3 provided a good example of how to a) create an Excel spreadsheet from a table used to supply content for an ALV report, and b) send the spreadsheet to an email recipient.
After browsing the code of ZDEMO_EXCEL3 I found that it, and indeed virtually all these demo programs, facilitates sending the table contents via email by using the Business Communication Services, a feature SAP has made available since release 6.40. At this point I began to experiment with a copy of the code to reduce it to its essential elements to facilitate my requirement of sending the spool requests of multiple ALV reports via email. The sequence of steps outlined below shows how I went from knowing nothing about ABAP2XLSX to having it satisfy my requirement to send the output of multiple ALV reports to an internet email address.
Getting started
The first thing we need is a minimal ABAP program that will produce a single ALV report. The SFLIGHT demonstration table will provide the rows for this and an initial selection screen provides a parameter to allow the user to specify only the number of rows to be provided in the report. Here is the starting code:
report.
types : row_counter type n length 02.
parameters : rowcount type row_counter.
start-of-selection.
perform display_flight_rows using rowcount.
form display_flight_rows using row_count
type row_counter.
data : flight_stack type standard table of sflight
, alv_report type ref to cl_salv_table
.
try.
call method cl_salv_table=>factory
importing
r_salv_table = alv_report
changing
t_table = flight_stack.
catch cx_salv_msg.
return.
endtry.
select *
into table flight_stack
from sflight
up to row_count rows.
alv_report->display( ).
endform.
Activating and executing this program in foreground will present an ALV report with however many rows from table SFLIGHT the user had specified on the initial selection screen. Pressing Back, Exit or Cancel on this ALV report will return to the initial selection screen.
Including another ALV report in the program
Now that we have a program producing a single ALV report, let’s expand this to one that will provide two ALV reports, adding a report to show rows from the SCARR table. Here is the updated code:
report.
types : row_counter type n length 02.
parameters : rowcount type row_counter.
start-of-selection.
perform display_flight_rows using rowcount.
perform display_carrier_rows using rowcount.
form display_flight_rows using row_count
type row_counter.
data : flight_stack type standard table of sflight
, alv_report type ref to cl_salv_table
.
try.
call method cl_salv_table=>factory
importing
r_salv_table = alv_report
changing
t_table = flight_stack.
catch cx_salv_msg.
return.
endtry.
select *
into table flight_stack
from sflight
up to row_count rows.
alv_report->display( ).
endform.
form display_carrier_rows using row_count
type row_counter.
data : carrier_stack type standard table of scarr
, alv_report type ref to cl_salv_table
.
try.
call method cl_salv_table=>factory
importing
r_salv_table = alv_report
changing
t_table = carrier_stack.
catch cx_salv_msg.
return.
endtry.
select *
into table carrier_stack
from scarr
up to row_count rows.
alv_report->display( ).
endform.
The difference between this and the first version of the program is that we now have a new subroutine display_carrier_rows appearing at the end, modelled after the subroutine display_flight_rows, but defined to retrieve and present rows from the SCARR table. Also, we’ve included a perform statement in the start-of-selection event block to invoke this new subroutine after invoking the subroutine display_flight_rows.
Activating and executing this program in foreground again will present an ALV report with however many rows from table SFLIGHT the user had specified on the initial selection screen, but pressing Back, Exit or Cancel will present the second ALV report, the one with rows from the SCARR table. Pressing Back, Exit or Cancel from this second ALV report will return to the initial selection screen.
Including yet another ALV report in the program
Now that we have a program producing two ALV reports, let’s expand this to one that will provide three ALV reports, adding a report to show rows from the SBOOK table. Here is the updated code:
report.
types : row_counter type n length 02.
parameters : rowcount type row_counter.
start-of-selection.
perform display_flight_rows using rowcount.
perform display_carrier_rows using rowcount.
perform display_booking_rows using rowcount.
form display_flight_rows using row_count
type row_counter.
data : flight_stack type standard table of sflight
, alv_report type ref to cl_salv_table
.
try.
call method cl_salv_table=>factory
importing
r_salv_table = alv_report
changing
t_table = flight_stack.
catch cx_salv_msg.
return.
endtry.
select *
into table flight_stack
from sflight
up to row_count rows.
alv_report->display( ).
endform.
form display_carrier_rows using row_count
type row_counter.
data : carrier_stack type standard table of scarr
, alv_report type ref to cl_salv_table
.
try.
call method cl_salv_table=>factory
importing
r_salv_table = alv_report
changing
t_table = carrier_stack.
catch cx_salv_msg.
return.
endtry.
select *
into table carrier_stack
from scarr
up to row_count rows.
alv_report->display( ).
endform.
form display_booking_rows using row_count
type row_counter.
data : booking_stack type standard table of sbook
, alv_report type ref to cl_salv_table
.
try.
call method cl_salv_table=>factory
importing
r_salv_table = alv_report
changing
t_table = booking_stack.
catch cx_salv_msg.
return.
endtry.
select *
into table booking_stack
from sbook
up to row_count rows.
alv_report->display( ).
endform.
The difference between this and the second version of the program is that we now have a new subroutine display_booking_rows appearing at the end, also modelled after the subroutine display_flight_rows,but defined to retrieve and present rows from the SBOOK table. Also, we’ve included a perform statement in the start-of-selection event block to invoke this new subroutine after invoking the subroutine display_booking_rows.
Activating and executing this program in foreground again will present an ALV report with however many rows from table SFLIGHT the user had specified on the initial selection screen, then pressing Back, Exit or Cancel will present the second ALV report, the one with rows from the SCARR table, then pressing Back, Exit or Cancel will present the third ALV report, the one with rows from the SBOOK table. Pressing Back, Exit or Cancel from this third ALV report will return to the initial selection screen.
Running in background
At this point we have a simple program capable of displaying three separate ALV reports in succession when run in foreground. If we were to run this in background we would get three separate spool requests. Try it and see:
◈ Designate to run the program immediately in background.
◈ Invoke transaction SM37, then press Execute to get the Job Overview screen.
◈ Select the job and press the Spool icon, which presents the Step List Overview.
When a job has multiple associated spool requests, then the All Spool Requests icon should also appear on this display to the right of the Spool icon:
Placing the cursor on the job step entry and pressing this All Spool Lists button will show all spool requests associated with the job step:
Notice that there are three spool requests associated with this execution. Notice also that the spool request identifiers (Spool no.) do not necessarily appear in their numeric sequence. Had we defined this as a scheduled job and indicated a Spool List Recipient, then the recipient would receive only the last spool request created by this program – the one with the highest spool number.
Using ABAP2XLSX to capture and email as Excel spreadsheet content of multiple ALV reports
Now, using the features of ABAP2XLSX, we’re going to supercharge this simple program and provide it with the capability the collect all the ALV report output into a single Excel spreadsheet and to send the spreadsheet to an internet email address. In our case, we’ll create a single Excel spreadsheet which will contain three separate worksheets, with each worksheet containing the content of one of the ALV reports, and then send this Excel spreadsheet as an email attachment to our own email address.
Including the capability to capture ALV report content in an Excel spreadsheet
We’ll do this in two stages. The first is to provide the capability to capture the content from each of our ALV reports as a separate worksheet of an Excel spreadsheet. To do this we now introduce the use of the capabilities provided by ABAP2XLSX. We need a data statement to declare a variable to be used as a reference to an instance of class zcl_excel:
data : excel type ref to zcl_excel.
Place this immediately ahead of the parameters statement defining rowcount. A syntax check should still pass. Next, we need a new subroutine that can accept both a standard table of ALV report content and a description for it, and to use these to create a new Excel worksheet. Here is the code to do this:
form copy_table_to_excel_worksheet using source_stack
type standard table
source_description
type string
raising zcx_excel.
constants : first_column type char1 value 'A'
.
data : worksheet type ref to zcl_excel_worksheet
, worksheet_title
type zexcel_sheet_title
, table_settings type zexcel_s_table_settings
.
table_settings-table_style = zcl_excel_table=>builtinstyle_medium2.
table_settings-show_row_stripes
= abap_true.
table_settings-nofilters = abap_true.
table_settings-top_left_column
= first_column.
table_settings-top_left_row = 01.
if excel is not bound.
create object excel.
worksheet = excel->get_active_worksheet( ).
else.
worksheet = excel->add_new_worksheet( ).
endif.
worksheet_title = source_description.
worksheet->set_title( worksheet_title ).
worksheet->bind_table(
ip_table = source_stack
is_table_settings = table_settings
).
endform.
Place this new subroutine at the end of the program. A syntax check should still pass. Finally, we need to invoke this new subroutine from each of our three ALV reporting subroutines, so place each of these statements as the final statements in subroutines of display_flight_rows, display_carrier_rows and display_booking_rows, respectively:
perform copy_table_to_excel_worksheet using flight_stack ‘Flights’.
perform copy_table_to_excel_worksheet using carrier_stack ‘Carriers’.
perform copy_table_to_excel_worksheet using booking_stack ‘Bookings’.
At this point a syntax check still will pass, but an Extended Syntax Check will issue errors indicating that we are not handling exception zcx_excel in any of the three ALV reporting subroutines invoking the new subroutine, which indicates that it can raise this exception. Accordingly, add the statement
raising zcx_excel
to the signature of each of the form definitions for the ALV reporting subroutines. Rerun the Extended Syntax Check, including the checkmark to select Programming Guidelines, and now the errors are gone but we get a warning that field excel is declared globally. Yes, we know this, so let’s apply the ##NEEDED pragrma to its definition to indicate that we wish not to see this warning anymore:
data : excel type ref to zcl_excel ##NEEDED.
Now an extended syntax check including the checkmark to select Programming Guidelines gives us a clean bill of health.
Including the capability to email an Excel spreadsheet
The program now builds an Excel spreadsheet containing three worksheets but does nothing with it. Let’s include some code to have this Excel spreadsheet mailed to our own email address. Here is the new subroutine to do this, which was modelled after the subroutine send_mail of class lcl_ouput defined in object ZDEMO_EXCEL_OUTPUTOPT_INCL:
form send_excel_via_email using recipient type email_recipient.
constants : excel_file_type
type string value '.xlsx'
, file_name_parameter
type string value '&SO_FILENAME='
.
data : excel_writer type ref to zif_excel_writer
, excel_as_xstring
type xstring
, excel_as_xstring_bytecount
type i
, excel_as_solix_stack
type solix_tab
, mail_send_request
type ref to cl_bcs
, mail_message type ref to cl_document_bcs
, any_bcs_exception
type ref to cx_bcs
, diagnostic type string
, mail_title type so_obj_des
, mail_text_stack
type soli_tab
, mail_text_entry
like line
of mail_text_stack
, mail_attachment_subject
type sood-objdes
, mail_attachment_bytecount
type sood-objlen
, mail_attachment_header_stack
type soli_tab
, mail_attachment_header_entry
like line of mail_attachment_header_stack
, internet_email_recipient
type ref to if_recipient_bcs
, successful_send
type abap_bool
, file_name type string
.
" Much of the code here was lifted from method send_mail of
" class lcl_ouput, defined in object ZDEMO_EXCEL_OUTPUTOPT_INCL:
concatenate sy-repid " this report name
sy-datum " current date
sy-uzeit " current time
excel_file_type " excel file extension
into file_name.
mail_title = file_name.
mail_attachment_subject = file_name.
mail_text_entry = 'See attachment'.
append mail_text_entry
to mail_text_stack.
concatenate file_name_parameter
file_name
into mail_attachment_header_entry.
append mail_attachment_header_entry
to mail_attachment_header_stack.
create object excel_writer type zcl_excel_writer_2007.
excel_as_xstring = excel_writer->write_file( excel ).
excel_as_solix_stack = cl_bcs_convert=>xstring_to_solix( iv_xstring = excel_as_xstring ).
excel_as_xstring_bytecount = xstrlen( excel_as_xstring ).
mail_attachment_bytecount = excel_as_xstring_bytecount.
try.
mail_message = cl_document_bcs=>create_document(
i_type = 'RAW' "#EC NOTEXT
i_text = mail_text_stack
i_subject = mail_title
).
mail_message->add_attachment(
i_attachment_type = 'XLS' "#EC NOTEXT
i_attachment_subject = mail_attachment_subject
i_attachment_size = mail_attachment_bytecount
i_att_content_hex = excel_as_solix_stack
i_attachment_header = mail_attachment_header_stack
).
mail_send_request = cl_bcs=>create_persistent( ).
mail_send_request->set_document( mail_message ).
internet_email_recipient = cl_cam_address_bcs=>create_internet_address( recipient ).
mail_send_request->add_recipient( internet_email_recipient ).
successful_send = mail_send_request->send( ).
commit work.
if successful_send eq abap_false.
message i500(sbcoms) with recipient.
else.
message s022(so).
message 'Document ready to be sent - Check SOST' type 'I'.
endif.
catch cx_bcs into any_bcs_exception.
diagnostic = any_bcs_exception->if_message~get_text( ).
message diagnostic type 'I'.
endtry.
endform.
Place this at the end of the program. Then adjust the code between the report statement and the start-of-selection statement to include:
◈ a types statement defining an email_recipient
◈ a parameters statement defining a recipient parameter using the new email_recipient type
◈ an initialization event block to automatically populate this new parameters field
Afterward, the first few lines code should look like this:
report.
types : row_counter type n length 02.
types : email_recipient
type adr6-smtp_addr.
data : excel type ref to zcl_excel ##NEEDED.
parameters : rowcount type row_counter.
parameters : recipien type email_recipient.
initialization.
select single smtp_addr
into recipien
from adr6 ##WARN_OK
inner join
usr21 on usr21~persnumber eq adr6~persnumber
where usr21~bname eq sy-uname.
start-of-selection.
A syntax check should still pass. Finally, include a new perform statement at the end of the start-of-selection event to invoke the new subroutine:
perform send_excel_via_email using recipien.
A syntax check should still pass and an extended syntax check including the checkmark to select Programming Guidelines should find no violations. The final image should look like this:
report.
types : row_counter type n length 02.
types : email_recipient
type adr6-smtp_addr.
data : excel type ref to zcl_excel ##NEEDED.
parameters : rowcount type row_counter.
parameters : recipien type email_recipient.
initialization.
select single smtp_addr
into recipien
from adr6 ##WARN_OK
inner join
usr21 on usr21~persnumber eq adr6~persnumber
where usr21~bname eq sy-uname.
start-of-selection.
perform display_flight_rows using rowcount.
perform display_carrier_rows using rowcount.
perform display_booking_rows using rowcount.
perform send_excel_via_email using recipien.
form display_flight_rows using row_count
type row_counter
raising zcx_excel.
data : flight_stack type standard table of sflight
, alv_report type ref to cl_salv_table
.
try.
call method cl_salv_table=>factory
importing
r_salv_table = alv_report
changing
t_table = flight_stack.
catch cx_salv_msg.
return.
endtry.
select *
into table flight_stack
from sflight
up to row_count rows.
alv_report->display( ).
perform copy_table_to_excel_worksheet using flight_stack 'Flights'.
endform.
form display_carrier_rows using row_count
type row_counter
raising zcx_excel.
data : carrier_stack type standard table of scarr
, alv_report type ref to cl_salv_table
.
try.
call method cl_salv_table=>factory
importing
r_salv_table = alv_report
changing
t_table = carrier_stack.
catch cx_salv_msg.
return.
endtry.
select *
into table carrier_stack
from scarr
up to row_count rows.
alv_report->display( ).
perform copy_table_to_excel_worksheet using carrier_stack 'Carriers'.
endform.
form display_booking_rows using row_count
type row_counter
raising zcx_excel.
data : booking_stack type standard table of sbook
, alv_report type ref to cl_salv_table
.
try.
call method cl_salv_table=>factory
importing
r_salv_table = alv_report
changing
t_table = booking_stack.
catch cx_salv_msg.
return.
endtry.
select *
into table booking_stack
from sbook
up to row_count rows.
alv_report->display( ).
perform copy_table_to_excel_worksheet using booking_stack 'Bookings'.
endform.
form copy_table_to_excel_worksheet using source_stack
type standard table
source_description
type string
raising zcx_excel.
constants : first_column type char1 value 'A'
.
data : worksheet type ref to zcl_excel_worksheet
, worksheet_title
type zexcel_sheet_title
, table_settings type zexcel_s_table_settings
.
table_settings-table_style = zcl_excel_table=>builtinstyle_medium2.
table_settings-show_row_stripes
= abap_true.
table_settings-nofilters = abap_true.
table_settings-top_left_column
= first_column.
table_settings-top_left_row = 01.
if excel is not bound.
create object excel.
worksheet = excel->get_active_worksheet( ).
else.
worksheet = excel->add_new_worksheet( ).
endif.
worksheet_title = source_description.
worksheet->set_title( worksheet_title ).
worksheet->bind_table(
ip_table = source_stack
is_table_settings = table_settings
).
endform.
form send_excel_via_email using recipient type email_recipient.
constants : excel_file_type
type string value '.xlsx'
, file_name_parameter
type string value '&SO_FILENAME='
.
data : excel_writer type ref to zif_excel_writer
, excel_as_xstring
type xstring
, excel_as_xstring_bytecount
type i
, excel_as_solix_stack
type solix_tab
, mail_send_request
type ref to cl_bcs
, mail_message type ref to cl_document_bcs
, any_bcs_exception
type ref to cx_bcs
, diagnostic type string
, mail_title type so_obj_des
, mail_text_stack
type soli_tab
, mail_text_entry
like line
of mail_text_stack
, mail_attachment_subject
type sood-objdes
, mail_attachment_bytecount
type sood-objlen
, mail_attachment_header_stack
type soli_tab
, mail_attachment_header_entry
like line of mail_attachment_header_stack
, internet_email_recipient
type ref to if_recipient_bcs
, successful_send
type abap_bool
, file_name type string
.
" Much of the code here was lifted from method send_mail of
" class lcl_ouput, defined in object ZDEMO_EXCEL_OUTPUTOPT_INCL:
concatenate sy-repid " this report name
sy-datum " current date
sy-uzeit " current time
excel_file_type " excel file extension
into file_name.
mail_title = file_name.
mail_attachment_subject = file_name.
mail_text_entry = 'See attachment'.
append mail_text_entry
to mail_text_stack.
concatenate file_name_parameter
file_name
into mail_attachment_header_entry.
append mail_attachment_header_entry
to mail_attachment_header_stack.
create object excel_writer type zcl_excel_writer_2007.
excel_as_xstring = excel_writer->write_file( excel ).
excel_as_solix_stack = cl_bcs_convert=>xstring_to_solix( iv_xstring = excel_as_xstring ).
excel_as_xstring_bytecount = xstrlen( excel_as_xstring ).
mail_attachment_bytecount = excel_as_xstring_bytecount.
try.
mail_message = cl_document_bcs=>create_document(
i_type = 'RAW' "#EC NOTEXT
i_text = mail_text_stack
i_subject = mail_title
).
mail_message->add_attachment(
i_attachment_type = 'XLS' "#EC NOTEXT
i_attachment_subject = mail_attachment_subject
i_attachment_size = mail_attachment_bytecount
i_att_content_hex = excel_as_solix_stack
i_attachment_header = mail_attachment_header_stack
).
mail_send_request = cl_bcs=>create_persistent( ).
mail_send_request->set_document( mail_message ).
internet_email_recipient = cl_cam_address_bcs=>create_internet_address( recipient ).
mail_send_request->add_recipient( internet_email_recipient ).
successful_send = mail_send_request->send( ).
commit work.
if successful_send eq abap_false.
message i500(sbcoms) with recipient.
else.
message s022(so).
message 'Document ready to be sent - Check SOST' type 'I'.
endif.
catch cx_bcs into any_bcs_exception.
diagnostic = any_bcs_exception->if_message~get_text( ).
message diagnostic type 'I'.
endtry.
endform.
Now execute the program and you should see that it not only produces the 3 ALV reports but also results in an Excel spreadsheet created and mailed to the recipient email address, both in foreground and in background executions.
Insuring the email gets dispatched
Some SAP environments have a periodic job scheduled to regularly sweep output destined for transport via internet email. To be certain the email created by this program is dispatched, invoke transaction SOST, which will show all pending send requests. If you see your new email message in the queue in a waiting status, select the row(s) to be dispatched, then from the menu select the command: Send Request > Start Send Process for Selection. The selected row(s) disappear from the queue and moments later a corresponding email message will appear in your internet email inbox.
Really very informative and creative contents. This concept is a good way to enhance the knowledge.thanks for sharing. please keep it up.sap-pp training
ReplyDeleteThis is extremely helpful info!! Very good work. Everything is very interesting to learn and easy to understood. Thank you for giving information.sap pp abap training
ReplyDelete