Keep leading zero at ALV OO and send excel file as email attachment
It’s a very common requirement to remove the leading zero at ALV list output or send out an excel file as an email attachment. There’re many documents that describe how to achieve that. Recently, I get the requirement is to keep the leading zero for an order number, I thought it’ll be quite straight forward then it cost me several hours to achieve that~
The easiest way could be:
- using ALV OO with method cl_salv_column_table~set_leading_zero to set column which needs
- convert the internal table into XML format, then using cl_document_bcs=>xstring_to_solix convert into excel file
- using cl_document_bcs=>create_document and method ADD_ATTACHMENT to send out.
*Convert ALV list to excel format lv_xml_type = if_salv_bs_xml=>c_type_mhtml. lv_xml = gr_table->to_xml( xml_type = lv_xml_type ). TRY. size = xstrlen( lv_xml ). *Send ALV converted excel file attachment binary_content = cl_document_bcs=>xstring_to_solix( ip_xstring = lv_xml ). * -------- create persistent send request ------------------------ send_request = cl_bcs=>create_persistent( ). * -------- create and set document with attachment --------------- * create document object from the internal table with text document = cl_document_bcs=>create_document( i_type = 'RAW' i_text = main_text i_subject = 'ZBKLG_Report' ). "#EC NOTEXT * add the spreadsheet as an attachment to document object document->add_attachment( i_attachment_type = 'XLS' "#EC NOTEXT i_attachment_subject = lv_sub "'ExampleSpreadSheet' i_attachment_size = size i_att_content_hex = binary_content ). * add document object to send request send_request->set_document( document ). * --------- add recipient (e-mail address) ----------------------- * create recipient object LOOP AT s_email. recipient = cl_cam_address_bcs=>create_internet_address( s_email-low ). ld_email = s_email-low. * add recipient object to send request send_request->add_recipient( recipient ). ENDLOOP. * ---------- send document --------------------------------------- sent_to_all = send_request->send( i_with_error_screen = 'X' ). COMMIT WORK. IF sy-subrc <> 0. MOVE text-e05 TO l_msg. REPLACE '&1' WITH p_email INTO l_msg. MESSAGE l_msg TYPE 'E' DISPLAY LIKE 'E'. ELSE. MOVE text-s02 TO l_msg. REPLACE '&1' WITH ld_email INTO l_msg. MESSAGE l_msg TYPE 'I' DISPLAY LIKE 'I'. ENDIF. * ------------ exception handling ---------------------------------- * replace this rudimentary exception handling with your own one !!! CATCH cx_bcs INTO bcs_exception. MESSAGE i865(so) WITH bcs_exception->error_type. ENDTRY.
Of course, the user will not accept that as they need extra work even if it cost only 10 seconds: P The final approach is very simple, DO NOT USE SYSTEM DATA ELEMENT, using CHAR10 will do!
That's quite compact code, looks nice, thanks!
Out of curiosity, how does this approach compare to abap2xlsx? I've recently come across a demo program which generated multitab files. The more options out there, the better.
I would also advocate that anyone reading this look up ABAP2XLSX. It can convert a SALV or CL_GUI_GRID instance into a spreadsheet.
Clearly even after all these years no-one has heard of it, otherwise there would not be a blog every single month trying to re-invent some aspect of ABAP2XLSX.
The usual response is "oh that is open source and we are forbidden to use open source" which is nonsense as any company which actually DID have a "no open source" policy would forbid its employees from using internet browsers or using mobile phones, both of which are stuffed full of open source code.
I have used ABAP2XLSX to email spreadsheets with multiple tabs to specified users via a background job since 2012 - and it is SO EASY.
That is also an excuse used to avoid usage of ABAP2XLSX - the requirement is tiny, why do I need to install a gigantic framework with ten billion lines of code? In the UK we call that "using a sledgehammer to crack a nut".
The counter argument is that it only takes a few minutes to install and then you can use it for the small requirement and then later anything you want.
I found ten years ago when I first installed ABAP2XLSX and changed one ALV report so it could be emailed in the background, and it had multiple sheets, and conditional formatting, and you did not have to change the print settings and so on, that within a few years the requirements had come in to change every single Z ALV report in the system such that it could be emailed in the background as a spreadsheet. You can even put hyperlinks in it so that if the user clicks on a (say) sales order number in the spreadsheet SAP opens up VA03.
If someone works for us for five years and then goes to another company running SAP they always tell me that the thing they miss the most is being able to turn ALV reports into spreadsheets in batch mode. Otherwise they have to look up the spool request and download that to a spreadsheet and then spend all morning getting rid of blank rows and columns.
I can't agree with you more. I'm always put it off and think skip this by this time~
You make me conquer it today. I install this now, thanks to your sharing!