ABAP convert internal table to excel (.xlsx) format and Send email or download

Posted by -

Reporting is incomplete without file interfacing. Sometimes the requirement is to send a file via email or download it to a PC. but what to do when the user requests the file in XLSX format?

In today's post, I am sharing how to convert an internal table to .xlsx format and then send it to a PC or email.

One of the quickest ways I found it been done is using SALV classes. Below is the

Code snippet

cl_salv_table=>factory( IMPORTING r_salv_table = DATA(lo_table)
CHANGING t_table = t_vbak " <-Your Internal Table here
).
DATA(lt_fcat) = cl_salv_controller_metadata=>get_lvc_fieldcatalog( r_columns = lo_table->get_columns( )
r_aggregations = lo_table->get_aggregations( )
).
GET REFERENCE OF t_vbak INTO DATA(lo_data).
DATA(lo_result) = cl_salv_ex_util=>factory_result_data_table( r_data = lo_data
t_fieldcatalog = lt_fcat
).
cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform( EXPORTING xml_type = if_salv_bs_xml=>c_type_xlsx
xml_version = cl_salv_bs_a_xml_base=>get_version( )
r_result_data = lo_result
xml_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export
gui_type = if_salv_bs_xml=>c_gui_type_gui
IMPORTING xml = DATA(v_xstring)"<- Xstring output
).
view raw itab_2_xlsx.abap hosted with ❤ by GitHub

Now once you have the xstring with you, simply pass it to relevant libraries to handle the output.

Downloading to PC:

cl_scp_change_db=>xstr_to_xtab( EXPORTING im_xstring = v_xstring
IMPORTING ex_xtab = DATA(t_xtab)
).
cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = xstrlen( v_xstring ) " File length for binary files
filename = 'C:\Users\User\Desktop\VBAK.XLSX' " Name of file
filetype = 'BIN' " File type (ASCII, binary ...)
CHANGING data_tab = t_xtab " Transfer table
EXCEPTIONS OTHERS = 1
).
IF sy-subrc <> 0.
MESSAGE e000(db) WITH 'error'.
ENDIF.
view raw download_xlsx_2_pc.abap hosted with ❤ by GitHub

Sending via Email

TRY.
"Create send request
DATA(lo_send_request) = cl_bcs=>create_persistent( ).
"Create mail body
DATA(lt_body) = VALUE bcsy_text( ( line = 'Mail text' ) ).
"Set up document object
DATA(lo_document) = cl_document_bcs=>create_document( i_type = 'RAW'
i_text = lt_body
i_subject = 'mail subject'
).
"Add attachment
lo_document->add_attachment( i_attachment_type = 'xls'
i_attachment_size = CONV #( xstrlen( v_xstring ) )"<-Your Xstring Here
i_attachment_subject = 'Data'
i_attachment_header = VALUE #( ( line = '&SO_FILENAME=' && 'data.xlsx' ) )
i_att_content_hex = cl_bcs_convert=>xstring_to_solix( v_xstring )"<-Your Xstring Here
).
"Add document to send request
lo_send_request->set_document( lo_document ).
"Set sender
lo_send_request->set_sender( cl_cam_address_bcs=>create_internet_address( 'user@dummy.com' ) ).
"Set Recipient | This method has options to set CC/BCC as well
lo_send_request->add_recipient( cl_cam_address_bcs=>create_internet_address( 'recipient@dummy.com' ) ).
"Send Email
lo_send_request->send( ).
COMMIT WORK.
CATCH cx_send_req_bcs INTO DATA(lx_req_bsc).
"Error handling
CATCH cx_document_bcs INTO DATA(lx_doc_bcs).
"Error handling
CATCH cx_address_bcs INTO DATA(lx_add_bcs).
"Error handling
ENDTRY.
view raw send_xlsx_mail.abap hosted with ❤ by GitHub

It's that simple. Hope you like the post and use it in your day to day work.

Popular posts from this blog

ABAP read excel(.XLSX) file to internal table in ABAP using CL_FDT_XL_SPREADSHEET

Posted by -
You are given a requirement to upload data to a custom DB table where the user provides you with the file path of the excel on his PC and the report loads the data from it to the Custom table. For experienced Abapers, the requirement is quite straightforward, except that the user wants to upload an XLSX file. There are not many options to read data from XLSX/XLS file. The TEXT_CONVERT_XLS_TO_SAP  function module is one of them but is a performance killer and uses OLE. In this post, I wanted to showcase How we can read data from the XLSX files using CL_FDT_SPOREADSHEET class. Let's start with uploading the file. Now that we have the file in xstring table, let's pass it to CL_FDT_XL_SPREADSHEET to get its object reference. From the object reference, we can retrieve each worksheet data. The data is returned in the form of an anonymous data object which can be dereferenced into a field symbol and passed to the target internal table. Below is the co...
Read more

Use CL_BCS library to send email with ABAP

Posted by -
Many a time you need a report for which the output needs to be an email. SO_NEW_DOCUMENT_SEND_API1 is heavily used to achieve the task but let's explore another way of achieving the same using CL_BCS. This post talks about how to use the CL_BCS library provided by SAP to easily send emails. CL_BCS Library The class serves as the interface from BCS to the applications. The methods of the class cater for the send functions. Using CL_BCS, You can: – Attach files to email – Build the HTML/raw of the body. – Set email’s Senders & Receiver – Send an email, etc. Let's see the steps one by one: Initiating CL_BCS: The first thing you need is to initiate a persistent send request. Creating BCS Document: Create the BCS document object which will be sent with the CL_BCS interface to BCS. In this case the Email. SET_DOCUMENT Once we have the document, It needs to be set to the send request. SET_SENDER Assign the sender to the send request. ADD_RECIPIENT Pass ...
Read more