Technical Articles
Send XLSX file with multiple tabs in zip format via E-mail
Objective
To send ‘.XLSX’ file type with multiple sheets in single excel file as attachment via E-mail
Context
Using OLE logic, we can achieve this requirement and the attachment will be an ‘.XLS’ file. But our client system has some trust center setting enabled in their system which exempts them from opening .XLS file. So, we have to send the file in .XLSX file format for them to open. Although, we have abap2xlsx to deal with such requirements, this blog tries to achieve this without using abap2xlsx, and the attachment will be sent in ZIP format with one single .XLSX file as attachment in E-mail.
Sample Code
*&---------------------------------------------------------------------*
*& Report ZDEMO_XLSX
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zdemo_xlsx.
TYPES:
BEGIN OF ty_s_excel_content,
row_no TYPE i,
col_no TYPE i,
value TYPE string,
END OF ty_s_excel_content .
TYPES:
ty_t_excel_content TYPE STANDARD TABLE OF ty_s_excel_content .
DATA ls_excel_content TYPE ty_s_excel_content.
DATA lt_excel_content_h TYPE ty_t_excel_content.
DATA lt_excel_content_a TYPE ty_t_excel_content.
DATA lv_xstring TYPE xstring.
DATA lv_filename TYPE string.
DATA zip TYPE xstring.
DATA lt_att_hex TYPE solix_tab.
DATA lc_zipper TYPE REF TO cl_abap_zip.
DATA lv_size TYPE i.
DATA lv_main_text TYPE bcsy_text.
DATA: send_request TYPE REF TO cl_bcs,
document TYPE REF TO cl_document_bcs,
recipient TYPE REF TO if_recipient_bcs,
bcs_exception TYPE REF TO cx_bcs,
mailto TYPE ad_smtpadr,
sent_to_all TYPE os_boolean.
SELECTION-SCREEN BEGIN OF BLOCK b1.
PARAMETERS: p_email TYPE ad_smtpadr.
SELECTION-SCREEN END OF BLOCK b1.
START-OF-SELECTION.
*&-----Get data to send in excel-----------------
SELECT opbel, fikey FROM dfkkko "2 columns
INTO TABLE @DATA(lt_dfkkko)
UP TO 20 ROWS.
IF sy-subrc IS INITIAL.
SORT lt_dfkkko BY opbel.
ENDIF.
SELECT opbel, budat, blart FROM dfkkop "3 columns
INTO TABLE @DATA(lt_dfkkop)
UP TO 20 ROWS.
IF sy-subrc IS INITIAL.
SORT lt_dfkkop BY opbel.
ENDIF.
*&-----Convert Itab into excel sheet structure-----------
ls_excel_content-row_no = 1.
ls_excel_content-col_no = 1.
ls_excel_content-value = 'OPBEL'.
APPEND ls_excel_content TO lt_excel_content_a.
ls_excel_content-row_no = 1.
ls_excel_content-col_no = 2.
ls_excel_content-value = 'FIKEY'.
APPEND ls_excel_content TO lt_excel_content_a.
LOOP AT lt_dfkkko ASSIGNING FIELD-SYMBOL(<fs_dfkkko>).
ls_excel_content-row_no = sy-tabix + 1.
ls_excel_content-col_no = '1'.
ls_excel_content-value = <fs_dfkkko>-opbel.
APPEND ls_excel_content TO lt_excel_content_a.
ls_excel_content-col_no = '2'.
ls_excel_content-value = <fs_dfkkko>-fikey.
APPEND ls_excel_content TO lt_excel_content_a.
ENDLOOP.
ls_excel_content-row_no = 1.
ls_excel_content-col_no = 1.
ls_excel_content-value = 'OPBEL'.
APPEND ls_excel_content TO lt_excel_content_h.
ls_excel_content-row_no = 1.
ls_excel_content-col_no = 2.
ls_excel_content-value = 'BUDAT'.
APPEND ls_excel_content TO lt_excel_content_h.
ls_excel_content-row_no = 1.
ls_excel_content-col_no = 3.
ls_excel_content-value = 'BLART'.
APPEND ls_excel_content TO lt_excel_content_h.
LOOP AT lt_dfkkop ASSIGNING FIELD-SYMBOL(<fs_dfkkop>).
ls_excel_content-row_no = sy-tabix + 1.
ls_excel_content-col_no = '1'.
ls_excel_content-value = <fs_dfkkop>-opbel.
APPEND ls_excel_content TO lt_excel_content_h.
ls_excel_content-col_no = '2'.
ls_excel_content-value = <fs_dfkkop>-budat.
APPEND ls_excel_content TO lt_excel_content_h.
ls_excel_content-col_no = '3'.
ls_excel_content-value = <fs_dfkkop>-blart.
APPEND ls_excel_content TO lt_excel_content_h.
ENDLOOP.
*&-----Create XLSX file with the new Itab structures-----------
DATA(lo_excel) = NEW cl_cmcb_excel_2007( ).
DATA(lo_dwnld) = NEW cl_cmcb_download_org_hierarchy( ).
*&------Add DFKKKO data to excel--------------------
lo_excel->add_sheet( i_sheetname = 'FICA HEADER' ).
LOOP AT lt_excel_content_a INTO ls_excel_content.
IF ls_excel_content-row_no EQ 1. "For Header Record
lo_excel->set_cell( i_data = ls_excel_content-value
i_row_index = ls_excel_content-row_no
i_col_index = ls_excel_content-col_no
i_cellstyle = cl_srt_wsp_excel_2007=>c_cellstyle_header
i_sheetname = 'FICA HEADER' ).
ELSE.
lo_excel->set_cell( i_data = ls_excel_content-value "For Data records
i_row_index = ls_excel_content-row_no
i_col_index = ls_excel_content-col_no
i_cellstyle = cl_srt_wsp_excel_2007=>c_cellstyle_normal
i_sheetname = 'FICA HEADER' ).
ENDIF.
ENDLOOP.
*&--------Add DFKKOP data to excel---------------------
lo_excel->add_sheet( i_sheetname = 'FICA DOC' ).
LOOP AT lt_excel_content_h INTO ls_excel_content.
IF ls_excel_content-row_no EQ 1. "For Header Record
lo_excel->set_cell( i_data = ls_excel_content-value
i_row_index = ls_excel_content-row_no
i_col_index = ls_excel_content-col_no
i_cellstyle = cl_srt_wsp_excel_2007=>c_cellstyle_header
i_sheetname = 'FICA DOC' ).
ELSE.
lo_excel->set_cell( i_data = ls_excel_content-value "For Data records
i_row_index = ls_excel_content-row_no
i_col_index = ls_excel_content-col_no
i_cellstyle = cl_srt_wsp_excel_2007=>c_cellstyle_normal
i_sheetname = 'FICA DOC' ).
ENDIF.
ENDLOOP.
END-OF-SELECTION.
*&-------Transform to Xstring for zipping----------
lv_xstring = lo_excel->transform( ).
*&--------Zip the file--------------------------------
CONCATENATE 'FICA_DETAILS' '.XLSX' INTO lv_filename.
CREATE OBJECT lc_zipper.
"add file to zip
CALL METHOD lc_zipper->add
EXPORTING
name = lv_filename
content = lv_xstring. "e_xstring. "#EC FB_RC
"save zip
CALL METHOD lc_zipper->save
RECEIVING
zip = zip.
*&--- Convert Xstring into Binary ---
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = zip
IMPORTING
output_length = lv_size
TABLES
binary_tab = lt_att_hex.
*&----Send email-----------------------
TRY.
send_request = cl_bcs=>create_persistent( ).
APPEND 'PFA, The report output' TO lv_main_text.
document = cl_document_bcs=>create_document(
i_type = 'RAW'
i_text = lv_main_text
i_subject = 'SAMPLE REPORT' ).
*&---– Add the spread sheet as attachment to document object
document->add_attachment(
i_attachment_type = 'ZIP'
i_attachment_subject = 'SAMPLE REPORT'
i_attachment_size = CONV #( lv_size )
i_att_content_hex = lt_att_hex ).
*&---– Add document object to send request
send_request->set_document( document ).
*– Add recipient (e-mail address)
recipient = cl_cam_address_bcs=>create_internet_address( p_email ).
*– Add recipient object to send request
send_request->add_recipient( recipient ).
*– Set send immediately flag
send_request->set_send_immediately( 'X' ).
*– send document
sent_to_all = send_request->send( i_with_error_screen = 'X' ).
COMMIT WORK.
IF sent_to_all IS INITIAL.
MESSAGE i500(sbcoms) WITH mailto.
ELSE.
MESSAGE s022(so).
ENDIF.
*– exception handling
CATCH cx_bcs INTO bcs_exception.
MESSAGE i865(so) WITH bcs_exception->error_type.
ENDTRY.
Output
The below excel will be created in a ZIP file with filename as ‘REPORT’. After unzipping the file FICA_DETAILS.xlsx can be opened as below with multiple sheets.
The above report works for excel sheets with different structure as well.
Hope this helps!
Thanks
It is positive that abap2xlsx is slowly getting some recognition (ping Paul Hardy ) but I should also mention that this scenario was already covered in a previous blogpost by James E. McDonough
Also, why do you use cl_abap_zip instead of zcl_excel_writer_2007? You can take the "excel" object and write it directly.
Hi Andrea,
I couldn't find the class zcl_excel_writer_2007 in my SAP environment and this also another way around for sending an XLSX file in email without using abap2xlsx.
Well, at least CL_CMCB_EXCEL_2007 is something new (seems to be part of Cash Management China).
P.S.
As far as I could see, it doesn't use OLE.
This is never going to stop, is it? I mean never. I am currently writing the first of many ABAP2XLSX blogs and clearly, I need to get a move on.