Formatted Excel as Attachment using ABAP

[prakash_mali]

Hello Experts,

I am having requirement of creating attachment as excel and send it to external email address. Excel should have different colors depending on data, as well as some cells or columns should not be editable to the receivers. Those columns where user have to enter data I have to give dropdown or propose accepted values. Basically it is like creating RFQ with available data and asking vendor to enter new data related to quotation. I tried with xml but not able to achieve locking cells for editing and proposing values to user while editing for specific columns. Regarding OLE as per the information I got it only works in foreground. Could you please guide me to fulfill this requirement.

[Sandra_Rossi]

You explained the requirement, but do you really don't know anything how to do some parts of it?

The questions "send an email", "add an attachment" and "create Excel with colors in background" were asked "hundreds" of times in the forum so I let you search a little bit. For dropdown, it's the same solution as for the question "colors in background".

[vinita_kasliwal]

HI Prakash

Please use below code to send data to excel and to send email

Try the links below for formatting excel and using data validations

https://blogs.sap.com/2012/03/29/using-ole2-objects-for-create-an-excel-file/

https://blogs.sap.com/2010/07/12/abap2xlsx-generate-your-professional-excel-spreadsheet-from-abap/

Send email code:

    constants: lc_sender type ad_smtpadr value 'noreply@test.com'.


    data: 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.


    try.
        lo_send_request = cl_bcs=>create_persistent( ).


        lo_sender = cl_cam_address_bcs=>create_internet_address( lc_sender ).


        lo_send_request->set_sender( i_sender = lo_sender ).


        lo_recipient = cl_cam_address_bcs=>create_internet_address( iv_email ).


        lo_send_request->add_recipient(
                           i_recipient  = lo_recipient
                           i_express    = abap_true
                           i_copy       = space
                           i_blind_copy = space
                           i_no_forward = space ).


        "Set that we don't need a Return Status E-mail
        lo_send_request->set_status_attributes(
                           i_requested_status = 'E'
                           i_status_mail      = 'E' ).


        lo_send_request->set_send_immediately( abap_true ).


        if it_mailtext is not initial.
          "Build Document
          lo_document = cl_document_bcs=>create_document(
                                           i_type    = 'RAW'
                                           i_text    = it_mailtext
                                           i_subject = iv_subject ).
        endif.


        if it_pdf is not initial.
          "add attachment to document
          lo_document->add_attachment(
              i_attachment_type    = 'PDF'
              i_attachment_subject = conv #( iv_attname )
              i_att_content_hex    =  it_pdf ).
        endif.


        "Add document to send request
        call method lo_send_request->set_document( lo_document ).


        "Send document
        call method lo_send_request->send( ).


        commit work.


      catch cx_send_req_bcs
            cx_address_bcs
            cx_document_bcs.


        raise exception type noemail
    endtry.

Data to excel

Get header:


  CONCATENATE lv_str
              'Obj Type'
              'Document No.'
              'Partner Fct'
              'T.Type'
              'Process TYpe'
              cl_abap_char_utilities=>newline
INTO lv_str
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.




Get Line items 


  LOOP AT lt_cbp_list INTO ls_cbp_list.
 CONCATENATE
lv_str
ls_cbp_list-object_type
ls_cbp_list-object_id
ls_cbp_list-partner_fct
*ls_cbp_list-partner_guid
ls_cbp_list-pd_type
cl_abap_char_utilities=>newline
INTO lv_str
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
  ENDLOOP.


  CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
    EXPORTING
      text     = lv_str
*     MIMETYPE = ' '
*     ENCODING =
    IMPORTING
      buffer   = lv_xstr
    EXCEPTIONS
      failed   = 1
      OTHERS   = 2.
  IF sy-subrc <> 0.
* Implement suitable error handling here
  ENDIF.


  wdr_task=>client_window->client->attach_file_to_response(
    i_filename = 'table.xls'
    i_content =  lv_xstr
    i_mime_type = 'EXCEL' ).

[LeonvNiekerk]

If you see a message from excel, opening this attachment, instead of .xls, .csv can be used to prevent this

[matt]

@ leon_van_niekerk

Part of the requirement is for colouring cells in Excel. .csv won't help.