Skip to Content
Technical Articles
Author's profile photo Jigang Zhang 张吉刚

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:

  1. using ALV OO with method cl_salv_column_table~set_leading_zero to set column which needs
  2. convert the internal table into XML format, then using cl_document_bcs=>xstring_to_solix   convert into excel file
  3. 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.

But it’ll not show the leading zero for order number if you define refer to system field VBELN/EBELN etc. In an excel file, the leading zero will hide automatically although we send it out with leading zeros.

 

The quick way is to choose this column and set the custom format as 0000000000, then Excel will follow this format and display correctly.

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!

 

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Andrea Borgia
      Andrea Borgia

      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.

      Author's profile photo Paul Hardy
      Paul Hardy

      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.

      Cheersy Cheers

      Paul

       

       

      Author's profile photo Jigang Zhang 张吉刚
      Jigang Zhang 张吉刚
      Blog Post Author
      thanks!  ABAP2XLSX is definitely a great tool like cannon; sometimes modify existed program which needs a small knife.
      Author's profile photo Paul Hardy
      Paul Hardy

      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.

      Cheersy Cheers

      Paul

      Author's profile photo Jigang Zhang 张吉刚
      Jigang Zhang 张吉刚
      Blog Post Author

      @Paul Hardy

      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!