Technical Articles

NITIN KUMAR UPADHYAY

July 28, 2021 13 minute read

Sending Spool List into Excel Format through Email Attachment

Requirement :-

I got one requirement where i need to call the SUIM transaction -> Roles by Complex Selection Criteria with multiple variants selections as background job and need to send the output in XLS format to multiple users maintained in distribution list.

There was one more additional requirement where i need to create single excel output with multiple tabs ( indicating each variant of report ).

Solution :

This Solution will help for any report which we want to execute as background job and display the List View in Excel format.

Steps:-

Create one Custom report with Selection screen parameters as Report Name ( For which we need to display the result in excel) , Distribution List ( where we need to send the file) and select option ( containing multiple variants of Report ).
Call the report and export the output in memory.
Get the report Output from memory by calling the FM ” LIST from MEMORY “.
Convert the list in ASCII using FM ” LIST TO ASCII “.
SPLIT the table into header and items according to the requirement.
Create the excel using IXML Factory methods with multiple tabs.
Finally attach the excel and send it to multiple distribution lists.

Step 1 :

*** Image Added from the Test system as Dummy***

Step 2:

* Call report and export output in memory
submit (p_report)
using selection-set wa_vari-low
line-size sy-linsz
exporting list to memory and return.

Step 3:

    clear : list[].
* Get report output from memory
call function 'LIST_FROM_MEMORY'
tables
listobject = list
exceptions
not_found  = 1
others     = 2.

Step 4:

   clear : ascilines[].
* Convert it to ascii
call function 'LIST_TO_ASCI'
tables
listobject         = list
listasci           = ascilines
exceptions
empty_list         = 1
list_index_invalid = 2
others             = 3.

Step 5:

    loop at ascilines .
*   Skip separater lines
check ascilines+0(10) <> '----------' .
clear li_split .
split ascilines at '|' into table li_split .

if lines( li_split ) = 1 or li_split is initial.
if li_split is not initial.
wa_header = li_split[ 1 ].
endif.
append wa_header to gt_header.
clear : wa_header.
else.
if lv_flg <> abap_true.
append lines of li_split to gt_tab_head[].
lv_cnt = 1.
lv_flg = abap_true.
else.
unassign <fs_slip>.
loop at li_split assigning <fs_slip>.
append initial line to gt_items assigning field-symbol(<fs_item>).
<fs_item>-field = <fs_slip>-token.
<fs_item>-indx = lv_cnt.
unassign <fs_item>.
endloop.
lv_cnt = lv_cnt + 1.
endif.
endif.
endloop.

Step 6:

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

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

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

ns_attribute = l_document->create_namespace_decl( name = 'ss'  prefix = 'xmlns'  uri = 'urn:schemas-microsoft-com:office:spreadsheet' ).
l_element_root->set_attribute_node( ns_attribute ).

ns_attribute = l_document->create_namespace_decl( name = 'x'  prefix = 'xmlns'  uri = 'urn:schemas-microsoft-com:office:excel' ).
l_element_root->set_attribute_node( ns_attribute ).

* Create node for document properties.
r_element_properties = l_document->create_simple_element( name = 'SUIM_REPORT'  parent = l_element_root ).
l_value = sy-uname.
l_document->create_simple_element( name = 'Author'  value = l_value  parent = r_element_properties  ).

* Styles
r_styles = l_document->create_simple_element( name = 'Styles'  parent = l_element_root  ).

* Style for Header
r_style  = l_document->create_simple_element( name = 'Style'   parent = r_styles  ).
r_style->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Header' ).

r_format  = l_document->create_simple_element( name = 'Font'  parent = r_style  ).
r_format->set_attribute_ns( name = 'Bold'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Interior' parent = r_style  ).
r_format->set_attribute_ns( name = 'Color'   prefix = 'ss'  value = '#FFFFFF' ).
r_format->set_attribute_ns( name = 'Pattern' prefix = 'ss'  value = 'Solid' ).

r_format  = l_document->create_simple_element( name = 'Alignment'  parent = r_style  ).
r_format->set_attribute_ns( name = 'Vertical'  prefix = 'ss'  value = 'Center' ).
r_format->set_attribute_ns( name = 'WrapText'  prefix = 'ss'  value = '1' ).

r_border  = l_document->create_simple_element( name = 'Borders'  parent = r_style ).
r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

***
* Style for tablename
r_style2  = l_document->create_simple_element( name = 'Style'   parent = r_styles  ).
r_style2->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Header1' ).

r_format  = l_document->create_simple_element( name = 'Font'  parent = r_style2  ).
r_format->set_attribute_ns( name = 'Bold'  prefix = 'ss'  value = '1' ).
r_format->set_attribute_ns( name = 'Color'  prefix = 'ss'  value = '#FFFFFF'  ).

r_format  = l_document->create_simple_element( name = 'Interior' parent = r_style2  ).
r_format->set_attribute_ns( name = 'Color'   prefix = 'ss'  value = '#4F81BD' ).
r_format->set_attribute_ns( name = 'Pattern' prefix = 'ss'  value = 'Solid' ).

r_format  = l_document->create_simple_element( name = 'Alignment'  parent = r_style2  ).
r_format->set_attribute_ns( name = 'Vertical'  prefix = 'ss'  value = 'Center' ).
r_format->set_attribute_ns( name = 'Horizontal'  prefix = 'ss'  value = 'Center' ).
r_format->set_attribute_ns( name = 'WrapText'  prefix = 'ss'  value = '1' ).
*
r_border  = l_document->create_simple_element( name = 'Borders'  parent = r_style2 ).
r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).
****

*   Style for header2
r_style4  = l_document->create_simple_element( name = 'Style'   parent = r_styles  ).
r_style4->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Header2' ).

r_format  = l_document->create_simple_element( name = 'Font'  parent = r_style4  ).
r_format->set_attribute_ns( name = 'Color'  prefix = 'ss'  value = '#FFFFFF'  ).

r_format  = l_document->create_simple_element( name = 'Interior' parent = r_style4  ).
r_format->set_attribute_ns( name = 'Color'   prefix = 'ss'  value = '#4F81BD' ).
r_format->set_attribute_ns( name = 'Pattern' prefix = 'ss'  value = 'Solid' ).

r_format  = l_document->create_simple_element( name = 'Alignment'  parent = r_style4  ).
r_format->set_attribute_ns( name = 'Vertical'  prefix = 'ss'  value = 'Center' ).
r_format->set_attribute_ns( name = 'Horizontal'  prefix = 'ss'  value = 'Center' ).
r_format->set_attribute_ns( name = 'WrapText'  prefix = 'ss'  value = '1' ).
*
r_border  = l_document->create_simple_element( name = 'Borders'  parent = r_style4 ).
r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).
***********

*Style for main title
r_style3  = l_document->create_simple_element( name = 'Style'   parent = r_styles  ).
r_style3->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Main' ).

r_format  = l_document->create_simple_element( name = 'Font'  parent = r_style3  ).
r_format->set_attribute_ns( name = 'Bold'  prefix = 'ss'  value = '1' ).
r_format->set_attribute_ns( name = 'Color'  prefix = 'ss'  value = '#366092' ).

******************
* Style for Data
r_style1  = l_document->create_simple_element( name = 'Style'   parent = r_styles  ).
r_style1->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Data' ).

r_border  = l_document->create_simple_element( name = 'Borders'  parent = r_style1 ).
r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = l_document->create_simple_element( name = 'Alignment'  parent = r_style1  ).
r_format->set_attribute_ns( name = 'Vertical'  prefix = 'ss'  value = 'Center' ).
r_format->set_attribute_ns( name = 'Horizontal'  prefix = 'ss'  value = 'Left' ).

endif.

***** Tab 1 ****************************
* Worksheet(First tab)
clear : lv_value.
lv_value = lv_var.
r_worksheet = l_document->create_simple_element( name = 'Worksheet'  parent = l_element_root ).
r_worksheet->set_attribute_ns( name = 'Name'  prefix = 'ss'  value = lv_value ).

* Table
r_table = l_document->create_simple_element( name = 'Table'  parent = r_worksheet ).
r_table->set_attribute_ns( name = 'FullColumns'  prefix = 'x'  value = '1' ).
r_table->set_attribute_ns( name = 'FullRows'     prefix = 'x'  value = '1' ).

* Column Formatting
r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

* Blank Row
r_row = l_document->create_simple_element( name = 'Row'  parent = r_table ).
r_row->set_attribute_ns( name = 'AutoFitHeight'  prefix = 'ss'  value = '0' ).
** Header of the Excel File
loop at lt_header into data(wa_header).
lv_value = wa_header-token.
r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).
r_cell->set_attribute_ns( name = 'MergeAcross'  prefix = 'ss'  value = '3' ).
r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Main' ).
r_data = l_document->create_simple_element( name = 'Data'  value = lv_value  parent = r_cell ).
r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

r_row = l_document->create_simple_element( name = 'Row'  parent = r_table ).
r_row->set_attribute_ns( name = 'AutoFitHeight'  prefix = 'ss'  value = '0' ).

endloop.

clear : lv_value.

* Column Headers Row
r_row = l_document->create_simple_element( name = 'Row'  parent = r_table ).
r_row->set_attribute_ns( name = 'AutoFitHeight'  prefix = 'ss'  value = '1' ).

clear lv_value.
loop at lt_tab_head into data(wa_tab_head).
lv_value = wa_tab_head-token.
*Commodity
r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).
r_data = l_document->create_simple_element( name = 'Data'  value = lv_value  parent = r_cell ).
r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).
endloop.

* Blank Row after Column Headers
r_row = l_document->create_simple_element( name = 'Row'  parent = r_table ).
r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

loop at lt_items into data(wa_items).
if lv_idx <> wa_items-indx.
r_row = l_document->create_simple_element( name = 'Row'  parent = r_table ).
lv_idx = wa_items-indx.
endif.
clear : lv_value.
lv_value = wa_items-field.
r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).
r_data = l_document->create_simple_element( name = 'Data'  value = lv_value   parent = r_cell ).           " Data
r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                               " Cell format
endloop.
clear : wa_items , wa_tab_head, wa_header, lv_idx.

Step 7:

  data :  email      type adr6-smtp_addr.
data: objpack   like sopcklsti1 occurs 2 with header line.
data: objhead   like solisti1 occurs 1 with header line.
data: objbin    like solix occurs 10 with header line.
data: objtxt    like solisti1 occurs 10 with header line.
data: reclist   like somlreci1 occurs 5 with header line.
data: doc_chng  like sodocchgi1.
data: tab_lines like sy-tabix.
data: l_num(3).
data: subj_date(10) type c.
* 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 = l_xml_table ).

* Rendering the Document
l_renderer = l_ixml->create_renderer( ostream  = l_ostream  document = l_document ).
l_rc = l_renderer->render( ).

* Saving the XML Document
l_xml_size = l_ostream->get_num_written_raw( ).

doc_chng-obj_descr = |SUIM User Details on Date : | && sy-datum+6(2) && |.| && sy-datum+4(2) && |.| && sy-datum+0(4).

describe table objtxt lines tab_lines.
read table objtxt index tab_lines.
doc_chng-doc_size = ( tab_lines - 1 ) * 255 + strlen( objtxt ).

* Packing List For the E-mail Body
objpack-head_start = 1.
objpack-head_num   = 0.
objpack-body_start = 1.
objpack-body_num   = tab_lines.
objpack-doc_type   = 'RAW'.
append objpack.

* Creation of the Document Attachment
loop at l_xml_table into wa_xml.
clear objbin.
objbin-line = wa_xml-data.
append objbin.
endloop.

describe table objbin lines tab_lines.
objhead-line = |SUIM User Details on Date : | && sy-datum+6(2) && |.| && sy-datum+4(2) && |.| && sy-datum+0(4).
append objhead.

* Packing List For the E-mail Attachment
objpack-transf_bin = 'X'.
objpack-head_start = 1.
objpack-head_num   = 0.
objpack-body_start = 1.
objpack-body_num = tab_lines.
objpack-obj_descr = 'SUIM user Details'.
objpack-doc_type = 'XLS'.
objpack-doc_size = tab_lines * 255.
append objpack.

* Target Recipent
clear reclist.
reclist-receiver = p_dist.
reclist-rec_type = 'C'.
*  reclist-express = 'X'.
append reclist.

* Sending the document
call function 'SO_NEW_DOCUMENT_ATT_SEND_API1'
exporting
document_data              = doc_chng
put_in_outbox              = 'X'
commit_work                = 'X'
tables
packing_list               = objpack
object_header              = objhead
contents_txt               = objtxt
contents_hex               = objbin
receivers                  = reclist
exceptions
too_many_receivers         = 1
document_not_sent          = 2
operation_no_authorization = 4
others                     = 99.

if sy-subrc eq 0.
message 'Mail Sucessfully Sent' type 'S'.
endif.

Output :-

*** Image Added from the Test system as Dummy***

Summary :-

With the help of above code , we have successfuly created the excel file with multiple tabs from the report which we have executed in the background . This is the dynamic process which will work for all reports. Keep Learning ……..

Thanks for reading it . Hope this blog has helped you …..

If you have any questions feel free to post in the Q&A section of the Community “ABAP Development”.

13 Comments

You must be Logged on to comment or reply to a post.

Sandra Rossi

July 28, 2021 at 5:49 pm

Thanks. I think developers prefer these other solutions nowadays:

Intercept ALV content of called program: CL_SALV_BS_RUNTIME_INFO
Creating Excel file: abap2xlsx (presented at TechEd a long time ago, and used since then, if authorized by the project/by the client)
Send emails: BCS

It's also shorter and more legible (it's just a template, exceptions are not handled):

"=================
" EXTRACT ALV DATA
"=================
TYPES ty_range_agr_name TYPE RANGE OF agr_name.
FIELD-SYMBOLS <table> TYPE table.
DATA(actgrps) = VALUE ty_range_agr_name( sign = 'I' option = 'CP' ( low = 'Z*' ) ).
cl_salv_bs_runtime_info=>set( display  = abap_false metadata = abap_false data = abap_true ).
SUBMIT rsusr070 WITH actgrps IN actgrps AND RETURN.
cl_salv_bs_runtime_info=>get_data_ref( IMPORTING r_data = DATA(lr_data) ).
ASSIGN lr_data->* TO <table>.

"==================
" CREATE EXCEL FILE
"==================
DATA(workbook) = NEW zcl_excel( ).
DATA(worksheet) = workbook->get_active_worksheet( ).
worksheet->bind_table( ip_table = <table> ).
DATA(xlsx_generator) = CAST zif_excel_writer( NEW zcl_excel_writer_2007( ) ).
DATA(xlsx_data) = xlsx_generator->write_file( workbook ).

"=================
" SEND EMAIL
"=================
DATA(email) = cl_document_bcs=>create_document( 
                  i_subject = 'Roles to be checked' i_type = 'TXT'
                  i_text = VALUE #( ( line = |Attached is the list of roles that you should ...| ) ) ).
email->add_attachment( i_attachment_type = 'XLS' i_attachment_subject = 'Attachment' 
                  i_attachment_size = |{ xstrlen( xlsx_data ) }|
                  i_att_content_hex = cl_bcs_convert=>xstring_to_solix( xlsx_data )
                  i_attachment_header = VALUE #( ( line = |&SO_FILENAME=List_of_roles.xlsx| ) ) ).
DATA(bcs) = cl_bcs=>create_persistent( ).
bcs->set_document( email ).
bcs->add_recipient( i_recipient = cl_cam_address_bcs=>create_internet_address( 'john.doe@sap.com' ) ).
bcs->set_send_immediately( abap_true ). " prefer avoiding that, batch it instead
bcs->send( ).
COMMIT WORK.

Carlos Vivo

July 25, 2023 at 11:51 am

Furthermore, if you want to retrieve the data respecting the layout of the ALV you must use metadata and convert table (otherwise you'll get all the fields from the output table despite of being listed):

DATA: lt_html  TYPE STANDARD TABLE OF w3html,
      list_tab TYPE TABLE OF abaplist.
DATA: receivers  TYPE somlreci1.
DATA: entries    TYPE sodlienti1.
DATA: gt_receivers  TYPE STANDARD TABLE OF somlreci1.
DATA: gt_entries TYPE STANDARD TABLE OF sodlienti1,
      list_xls   TYPE truxs_t_text_data.
DATA recipient      TYPE REF TO if_recipient_bcs.
DATA recipient_mail TYPE adr6-smtp_addr.
DATA ls_metadata1 TYPE cl_salv_bs_runtime_info=>s_type_metadata.
DATA: gw_dyn_fcat  TYPE lvc_s_fcat,
      gt_dyn_fcat  TYPE lvc_t_fcat,
      gt_dyn_table TYPE REF TO data.


INITIALIZATION.

  title1 = 'Source of list'.
  title2 = 'Destination'.

START-OF-SELECTION.

  CALL FUNCTION 'LIST_FREE_MEMORY'.



  "=================
  " EXTRACT ALV DATA
  "=================
  TYPES ty_range_agr_name TYPE RANGE OF agr_name.
  FIELD-SYMBOLS <table> TYPE table.
  FIELD-SYMBOLS <table2> TYPE table.
  DATA(actgrps) = VALUE ty_range_agr_name( sign = 'I' option = 'CP' ( low = 'Z*' ) ).
  cl_salv_bs_runtime_info=>set( display  = abap_false metadata = abap_true data = abap_true ).

  SUBMIT (prog) USING SELECTION-SET variant AND RETURN.
  ls_metadata1 = cl_salv_bs_runtime_info=>get_metadata( ).
  cl_salv_bs_runtime_info=>get_data_ref( IMPORTING r_data = DATA(lr_data) ).
  ASSIGN lr_data->* TO <table>.

  DATA(gv_pos) = 0.
  LOOP AT ls_metadata1-t_fcat INTO DATA(fcat) WHERE no_out EQ space.
    ADD 1 TO gv_pos.

    MOVE-CORRESPONDING fcat TO gw_dyn_fcat.
    APPEND gw_dyn_fcat TO gt_dyn_fcat.
    CLEAR gw_dyn_fcat.
  ENDLOOP.

  cl_alv_table_create=>create_dynamic_table(
      EXPORTING
        i_style_table             = 'X'                 " Add Style Table
        it_fieldcatalog           =  gt_dyn_fcat                 " Field Catalog
      IMPORTING
        ep_table                  =  gt_dyn_table                " Pointer to Dynamic Data Table
      EXCEPTIONS
        generate_subpool_dir_full = 1                " At Most 36 Subroutine Pools Can Be Generated Temporarily
        OTHERS                    = 2
    ).

  ASSIGN gt_dyn_table->* TO <table2>.
  MOVE-CORRESPONDING <table> TO <table2>.

  "==================
  " CREATE EXCEL FILE
  "==================
  DATA(workbook) = NEW zcl_excel( ).
  DATA(worksheet) = workbook->get_active_worksheet( ).
  worksheet->bind_table( ip_table = <table2> ).
  DATA(xlsx_generator) = CAST zif_excel_writer( NEW zcl_excel_writer_2007( ) ).
  DATA(xlsx_data) = xlsx_generator->write_file( workbook ).

Sandra Rossi

July 25, 2023 at 12:54 pm

Thanks. Well seen concerning the retrieval of the field catalog (T_FCAT component of the structure returned by cl_salv_bs_runtime_info=>get_metadata( ).)

I'd use Zcl_alv_table_create=>create_dynamic_table rather than cl_alv_table_create=>create_dynamic_table, which is presented in a dedicated blog post "alternative..."

Too bad that the converters of abap2xlsx (ZCL_EXCEL_CONVERTER, also via BIND_ALV) don't support METADATA of CL_SALV_BS_RUNTIME_INFO (it currently supports only ALV grid, SALV table, WDA ALV...)

Jelena Perfiljeva

July 28, 2021 at 7:09 pm

This would've been a fantastic blog about 10 years ago if not more.

Don't mean to hate on your content but I'm genuinely curious why in 2021 an ABAP blog features using an FM for the emails when BCS class was covered on SCN back in 2006. It's not just this blog, we see similarly outdated solutions pop up periodically, so I'm really wondering why and where all this is coming from? Is there no curiosity to find newer methods? Not enough official information from SAP? Bad information passed down from "seniors" to new generations? Something else? I feel we need to get to the bottom of this phenomena to find how to improve this.

Matthew Billingham

July 29, 2021 at 7:12 am

I think it's very old training materials a lot of the time.

Andrea Borgia

July 29, 2021 at 7:26 am

There is more than enough good quality material but training doesn't happen without a driving force: either the person is internally motivated to keep learning or the company culture makes sure that people keep up to scratch.

I have found out that most of what my seniors told me at the beginning was already obsolete when I started with SAP, for example.

Jelena Perfiljeva

July 29, 2021 at 4:15 pm

Sadly, "I heard this from a senior" seems to be a common explanation. 🙁

Sandra Rossi

July 29, 2021 at 5:09 pm

I think that it's mostly due to SAP not providing any clear ABAP API to do things, so people have to do the best they can, using anything possible, and people all came with their own solutions. Now we see the result in the forum (and in customer code). If you provide a clean place since the beginning, people will try to keep it clean.

It's interesting to see in how many years abap2xlsx will become the main answer to the Excel questions... Of course, it's possible only if people continue to answer "use abap2xlsx", so that there are more abap2xlsx answers. Many people first look at blog posts, and abap2xlsx is largely subnumerary, so it doesn't help.

Probably the community could help by providing new free API and put effort in their promotion.

Jelena Perfiljeva

July 29, 2021 at 6:33 pm

Great point. SAP CTO's blog says "technology developers have increasingly composed software by working with libraries and the like" and it's true for every single language except for ABAP. Where are our libraries, exactly? We have to reinvent basic stuff like converting date formats. Even when APIs exist, they are not well documented and scattered all over the place. So it ends up as "word of mouth" and we all see how well this is going.

Jelena Perfiljeva

July 29, 2021 at 6:39 pm

This reminded me of this good old blog by Christian Drumm : Hello SAP, where are my APIs?

Where indeed.

Devid Rosenberg

July 29, 2021 at 8:47 am

Thank you for your explanation, it was very helpful for me! 👍

Paul Hardy

July 31, 2021 at 1:44 am

As Jelena has correctly pointed out there is a new blog like this one every month on the SAP Community site giving the exact same solution for creating an Excel spreadsheet from ABAP and/or emailing the result.

This has been the case for the last ten years at least, the same thing, again and again, every single month, sometimes multiple times per month, year after year. The irony is that the solution was and is ABAP2XLSX which has been around all that time.

People often give the excuse "Oh, we have a no-open source policy" despite driving a car and using a smartphone and using the internet, all of which crawl with open source code.

Then there is the "ABAP2XLSX is far too complicated" which is nonsense.

However I am going to be generous and presume people just have not heard of it. I am putting the subject back into my next edition of "ABAP to the Future" in the futile hope this will help spread the word.

Just ten minutes ago I did a "pull request" to the ABAP2XLSX project to add some extra functionality into one of it's classes.

Andrea Borgia

July 31, 2021 at 4:57 pm

Even worse: there are many instances where simply dumping an internal table to XLSX is perfectly acceptable and that requires no additional components, SALV is all you need (with apologies to The Beatles)

Technical Articles

Sending Spool List into Excel Format through Email Attachment

Requirement :-

Solution :

Steps:-

Step 1 :

Step 2:

Step 3:

Step 4:

Step 5:

Step 6:

Output :-

Summary :-

Assigned Tags