Send XLSX file as mail attachment via ABAP

Question

I have to create an email and attach an XLSX file. I looked at the BCS_EXAMPLE_7 program.

I have transformed the content with the following method:

  TRY.
      cl_bcs_convert=>string_to_solix(
      EXPORTING
        iv_string   = lv_content
        iv_codepage = '4103'  
        iv_add_bom  = 'X'
      IMPORTING
        et_solix  = pt_binary_content
        ev_size   = pv_size ).
    CATCH cx_bcs.
      ls_return-type    = text-023.
      ls_return-message = text-024.
      APPEND ls_return TO pt_return.
  ENDTRY.

  
      CONCATENATE lv_save_file_name '_' sy-datum '.xlsx' INTO lv_save_file_name.
      lv_attachment_subject  = lv_save_file_name.
      CONCATENATE '&SO_FILENAME=' lv_attachment_subject INTO ls_attachment_header.
      APPEND ls_attachment_header TO lt_attachment_header.

  
      lo_document->add_attachment( i_attachment_type    = 'XLS' 
                                   i_attachment_subject = lv_attachment_subject
                                   i_attachment_size    = pv_size
                                   i_att_content_hex    = pt_binary_content
                                   i_attachment_header  = lt_attachment_header ).

The email is sent correctly but when I open the attachment I see the error

Cannot open the file because the file extension is incorrect

Could you help me? thanks

Answer 1

Possible problems:

First problem: as correctly said by Sandra you may have invalid content of your lv_content variable, which doesn't correspond to correct XLSX structure.

Second problem: which you already solved, as seen from your coding, BCS classes do not support 4-character extensions.

Here is the sample how to build and send correct XLSX file via mail:

SELECT * UP TO 100 ROWS
  FROM spfli
  INTO TABLE @DATA(lt_spfli).

cl_salv_table=>factory( IMPORTING r_salv_table = DATA(lr_table)
                        CHANGING t_table = lt_spfli ).

DATA: lr_xldimension TYPE REF TO if_ixml_node,
      lr_xlworksheet TYPE REF TO if_ixml_element.

DATA(lv_xlsx) = lr_table->to_xml( if_salv_bs_xml=>c_type_xlsx ).
DATA(lr_zip) = NEW cl_abap_zip( ).
lr_zip->load( lv_xlsx ).
lr_zip->get( EXPORTING name = 'xl/worksheets/sheet1.xml' IMPORTING content = DATA(lv_file) ).

DATA(lr_file) = NEW cl_xml_document( ).
lr_file->parse_xstring( lv_file ).
* Row elements are under SheetData
DATA(lr_xlnode) = lr_file->find_node( 'sheetData' ).
DATA(lr_xlrows) = lr_xlnode->get_children( ).
* Create new element in the XML file
lr_xlworksheet ?= lr_file->find_node( 'worksheet' ).
DATA(lr_xlsheetpr)   = cl_ixml=>create( )->create_document( )->create_element( name = 'sheetPr' ).
DATA(lr_xloutlinepr) = cl_ixml=>create( )->create_document( )->create_element( name = 'outlinePr' ).
lr_xlsheetpr->if_ixml_node~append_child( lr_xloutlinepr ).
lr_xloutlinepr->set_attribute( name = 'summaryBelow' value = 'false' ).
lr_xldimension ?= lr_file->find_node( 'dimension' ).
lr_xlworksheet->if_ixml_node~insert_child( new_child = lr_xlsheetpr ref_child = lr_xldimension ).
* Create xstring and move it to XLSX
lr_file->render_2_xstring( IMPORTING stream = lv_file ).
lr_zip->delete( EXPORTING name = 'xl/worksheets/sheet1.xml' ).
lr_zip->add( EXPORTING name = 'xl/worksheets/sheet1.xml' content = lv_file ).
lv_xlsx = lr_zip->save( ).

DATA lv_size     TYPE i.
DATA lt_bintab TYPE solix_tab.

* Convert to binary
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
  EXPORTING
    buffer        = lv_xlsx
  IMPORTING
    output_length = lv_size
  TABLES
    binary_tab    = lt_bintab.

DATA main_text      TYPE bcsy_text.
* create persistent send request
DATA(send_request) = cl_bcs=>create_persistent( ).
* create document object from internal table with text
APPEND 'Valid Excel file' TO main_text.
DATA(document) = cl_document_bcs=>create_document( i_type = 'RAW' i_text = main_text i_subject = 'Test Created for stella' ).

DATA lt_att_head TYPE soli_tab.
APPEND '<(>&< )>SO_FILENAME=MySheet.xlsx' TO lt_att_head.
* add the spread sheet as attachment to document object
document->add_attachment(
  i_attachment_type    = 'xls'
  i_attachment_subject = 'MySheet'
  i_attachment_size    = CONV so_obj_len( lv_size )
  i_attachment_header  = lt_att_head
  i_att_content_hex    = lt_bintab ).

send_request->set_document( document ).
DATA(recipient) = cl_cam_address_bcs=>create_internet_address( 'some_recipient@mail.com' ).
send_request->add_recipient( recipient ).
DATA(sent_to_all) = send_request->send( i_with_error_screen = 'X' ).

COMMIT WORK.

Answer 2

You could also try to open the file with a text editor (eg. NotePad++), maybe this gives a hint of the actual content. But I guess that something went wrong generating the binary table. Maybe you are using the wrong file size or code page.

Answer 3

It appears that whatever you have in lv_content isn't actually a valid excel file. You can not just take arbitrary data, give it the extension .xlsx and expect MS Excel to know what to do with it.

Unfortunately, creating valid MS Office files is anything but trivial. It's a format which is theoretically open and based on XML (actually a zip archive containing multiple XML files), but in practice the specification is over a 5000(!) pages long.

Fortunately, there is a library for that. abap2xlsx is an open source (Apache License) library which provides an easy API to create (and read) valid XLSX files in ABAP.

Answer 4

That's a normal behavior of Excel, unrelated to ABAP, when the file name has extension .xlsx but doesn't contain data in format corresponding to XLSX. Excel does the same kind of checks for other extensions. If you need more information about these checks, please search the Web.

As I see that your program creates the attachment based on text converted into UTF-16LE code page (SAP code page 4103), I guess that you created the Excel data in format CSV, tab-separated values or even the old Excel XMLSS/XML 2003 format.

In that case, the extension .xlsx is not valid, to avoid the message, use the adequate extension, respectively .csv, .txt or .xml.

If you really need the extension .xlsx for some reason, then you must create the data in XLSX format. You may use the free API abap2xlsx. If you need further assistance about how to use abap2xlsx, please ask a new question (unrelated to email).

NB: maybe you were told to use the extension .xlsx although there is no real need to use it (each format has its own features, but simple unformatted values can be achieved with all formats), in that case you may propose to use a simple format like CSV or tab-separated values.

NB: you may also have the opposite case that Excel sniffs that the file contains data in format corresponding to XLSX, but the file name doesn't have the extension .xlsx, and the same for all other formats, but I can't say what is the exact Excel reaction to each case.