Skip to Content
Personal Insights
Author's profile photo Jigang Zhang 张吉刚

Template of concatenate internal table as excel attachment

There are too many articles that talk about excel attachments, no matter whether ABAP2XLS or using cl_salv_table->to_xml, etc. Sometimes no need to use a gun to shoot a mosquito when the gun & bullet are not ready.

Here is just one small code to quickly send one internal table as an excel file, which will be used as a template for myself. I would like to hide this blog if here provides this functionality. Please ignore this and sorry if it causes duplicated subjects& content…

*&---------------------------------------------------------------------*
*&      Form  send_email_excel
*&---------------------------------------------------------------------*
FORM send_email_excel.

  CONSTANTS: lc_tab  TYPE c VALUE cl_bcs_convert=>gc_tab,
             lc_crlf TYPE c VALUE cl_bcs_convert=>gc_crlf.

  DATA: ld_string TYPE string,
        size TYPE so_obj_len,
        lo_send_request TYPE REF TO cl_bcs,
        lo_document     TYPE REF TO cl_document_bcs,
        lo_sender       TYPE REF TO if_sender_bcs,
        lo_recipient    TYPE REF TO if_recipient_bcs VALUE IS INITIAL,
        lv_file_subject TYPE so_obj_des,
        lr_bcs_exception      TYPE REF TO cx_bcs,
        lv_subject TYPE char50,
        lx_document_bcs TYPE REF TO cx_document_bcs,
        lv_sent_to_all  TYPE os_boolean,
        lt_message_body TYPE bcsy_text,
        wa_message_body LIKE LINE OF lt_message_body,
        binary_content TYPE solix_tab.
  DATA: lt_fields     TYPE STANDARD TABLE OF dfies,
        ls_fields     TYPE dfies.


  check s_smtp[] is not INITIAL.
*---------------------------------------
*Prepare Excel file add as attachment
*---Header Line
*---------------------------------------
  CLEAR ld_string.
 * Get header descriptions by 'DDIF_FIELDINFO_GET'
      CALL FUNCTION 'DDIF_FIELDINFO_GET'
        EXPORTING
          tabname        = lv_tabname
          langu          = sy-langu
        TABLES
          dfies_tab      = lt_fields
        EXCEPTIONS
          not_found      = 1
          internal_error = 2
          OTHERS         = 3.

* or hardcode if descriptions not existed yet
  CONCATENATE 'Customer' lc_tab
              'Delivery' lc_tab
               ...
               Characteristic description' lc_crlf
              INTO ld_string.
*---------------------------------------
*Prepare Excel file add as attachment
*---Item Line
*---------------------------------------
  LOOP AT gt_out INTO gw_out.
    WRITE gw_out-wadat_ist TO v_wadat.
    CONCATENATE ld_string
              gw_out-con_name lc_tab
              ...
              gw_out-con_so_mail lc_crlf
             INTO ld_string.
  ENDLOOP.
*----------------------------
*Convert string to Excel file
*----------------------------
  TRY.
      cl_bcs_convert=>string_to_solix(
        EXPORTING
          iv_string   = ld_string
          iv_codepage = '4103'  "suitable for MS Excel, leave empty
          iv_add_bom  = 'X'     "for other doc types
        IMPORTING
          et_solix  = binary_content
          ev_size   = size ).
    CATCH cx_bcs.
      MESSAGE e445(so).
  ENDTRY.

*Email subject
  lv_subject = 'Email Subject here'.

*Email Body
  REFRESH lt_message_body.
  wa_message_body = 'Email body contents here'.
  APPEND wa_message_body TO lt_message_body.

  "create send request
  TRY.
    lo_send_request = cl_bcs=>create_persistent( ).
  CATCH cx_send_req_bcs.
  ENDTRY.

  "put your text into the document
  lv_file_subject = lv_subject.
  TRY.
  lo_document = cl_document_bcs=>create_document(
  i_type = 'RAW'
  i_text = lt_message_body
  i_subject = lv_subject ).
   CATCH cx_document_bcs INTO lx_document_bcs.
  ENDTRY.

* attached Document name
  lv_subject = 'Attached File name'
  TRY.
      lo_document->add_attachment(
      EXPORTING
      i_attachment_type    = 'XLS'
      i_attachment_subject = lv_subject
      i_attachment_size    = size
      i_att_content_hex    = binary_content ).
    CATCH cx_document_bcs INTO lx_document_bcs.
  ENDTRY.

  TRY.
* Add attachment
* Pass the document to send request
      lo_send_request->set_document( lo_document ).

      "Create sender
      TRY.
          lo_sender = cl_sapuser_bcs=>create( sy-uname ).
        CATCH cx_address_bcs.
      ENDTRY.

      "Set sender
      lo_send_request->set_sender( lo_sender ).

      "set receiver
      IF s_smtp[] IS NOT INITIAL.
        LOOP AT s_smtp.
          CONDENSE s_smtp-low.
          lo_recipient =
            cl_cam_address_bcs=>create_internet_address( s_smtp-low ).
          lo_send_request->add_recipient(
          EXPORTING
          i_recipient = lo_recipient
*          i_copy = 'X'
          ).
        ENDLOOP.
      ENDIF.

*Set recipient
      lo_send_request->add_recipient(
      EXPORTING
      i_recipient = lo_recipient
      i_express = abap_true
      ).

* Send email
      lo_send_request->send(
      EXPORTING
      i_with_error_screen = abap_true
      RECEIVING
      result = lv_sent_to_all ).

      IF sy-subrc EQ 0.
        LOOP AT s_smtp.
          MESSAGE i398(00) WITH 'Email Sent to' s_smtp-low '       '
                                text-s10.
        ENDLOOP.
      ELSE.
        MESSAGE i398(00) WITH 'Send Email failed.' text-s10.
      ENDIF.

* Commit Work to send the email
*    COMMIT WORK.

    CATCH cx_bcs INTO lr_bcs_exception.
      MESSAGE i865(so) WITH lr_bcs_exception->error_type.

  ENDTRY.
ENDFORM.

Assigned Tags

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

      Well, actually this is a CSV file, not "real" Excel.

      Also, when I feel reaaaaaalllly lazy, I ask IF_SALV_BS_XML to roll out a proper XLSX file for me and be on my way.

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

      Andrea Borgia

      You're right : D Thanks for the comments.

      I should refer to this one for real Excel.

       

      Author's profile photo Sandra Rossi
      Sandra Rossi

      Note that giving a file name an extension (.xls) which doesn't correspond to the file format (text file with tab-separated fields) leads to a security popup when opening the file in Excel. For a text file with tab-separated fields, Excel expects the extension .txt.

      It's nice to see that you have selected code page 4103 and BOM, which are the right ones for Excel to avoid issues with "mojibake"/garbled characters.

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

      Thanks for the reminder. You're absolutely right, I should prepare one lazy template for XLSX.

      Author's profile photo Andrea Borgia
      Andrea Borgia

      Here you are, courtesy of Jagdish Patil

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

      I refer to this one and create one for version < 7.5

        METHOD create_xlsx_from_itab.
      
          DATA: lt_data TYPE REF TO data,
                salv_table type REF TO cl_salv_table,
                lt_fcat TYPE LVC_T_FCAT.
          FIELD-SYMBOLS: <tab> TYPE STANDARD TABLE.
      
      * Old syntax
          GET REFERENCE OF it_data INTO lt_data.
      * New Syntax
      *data(lt_data) = ref #( it_data ).
      
          IF it_fieldcat IS INITIAL.
      
            ASSIGN lt_data->* TO <tab>.
            TRY.
                cl_salv_table=>factory(
                EXPORTING
                  list_display = abap_false
                IMPORTING
                  r_salv_table = salv_table
                CHANGING
                  t_table      = <tab> ).
      
                lt_fcat = cl_salv_controller_metadata=>get_lvc_fieldcatalog(
                                         r_columns      = salv_table->get_columns( )
                                         r_aggregations = salv_table->get_aggregations( ) ).
              CATCH cx_salv_msg.
                RETURN.
            ENDTRY.
      
          ELSE.
            lt_fcat = it_fieldcat.
          ENDIF.
      
          cl_salv_bs_lex=>export_from_result_data_table(
            EXPORTING
              is_format            = if_salv_bs_lex_format=>mc_format_xlsx
              ir_result_data_table =  cl_salv_ex_util=>factory_result_data_table(
                                                      r_data                      = lt_data
                                                      s_layout                    = is_layout
                                                      t_fieldcatalog              = lt_fcat
                                                      t_sort                      = it_sort
                                                      t_filter                    = it_filt
                                                      t_hyperlinks                = it_hyperlinks )
            IMPORTING
              er_result_file       = r_xstring ).
        ENDMETHOD.
      ZTEST_OO_EXCEL
      
        "fill table with data
        select * from spfli into corresponding fields of table gt_spfli.
      
        "call creation of xls
      CALL METHOD zcl_excel_tools=>create_xlsx_from_itab
        CHANGING
          it_data       = gt_spfli
        receiving
          r_xstring     = g_xstring.
      
      Author's profile photo Paul Hardy
      Paul Hardy

      There was an OSS (SAP Support) note to say how to do this. In 2012.

      But anyway, let us leave that aside. The important thing is this that the second ABAP/EXCEL blog this month. Usually, over the last ten years, there has only been one a month. Now we are getting one every few days.

      Hooray! Maybe we can get to the stage where we have multiple ones every day!