This is the extension of my previous 2 blogs:
Blog 1: Describes on working with XSL Transformation
Download MS Excel with formatting options using XSLT - Part 1
Blog 2: Describes on downloading the Excel created using XSL Transformation into Presentation Server
Download MS Excel with formatting options using XSLT - Part 2
In this blog, we are going to attach the generated Excel to the email and send it to external Email IDs.
As discussed in Blog 2, the Output of the XSL Transformation is an XML String.
Step 1:
Convert the XML string into Internal table
Step 2:
Attach the Internal table contents to the mail and send the mail.
We use the Class 'CL_BCS' to achieve this.
The Code Snippet is given below. This is easily understandable and ready to use as it is (dont forget to change the email id).
REPORT zdemo_excel_xslt.
TYPES: BEGIN OF ty_data,
carrid TYPE s_carr_id,
connid TYPE s_conn_id,
fldate TYPE s_date,
price TYPE s_price,
planetype TYPE s_planetye,
seatsmax TYPE s_seatsmax,
seatsocc TYPE s_seatsocc,
END OF ty_data.
* Data declaration
DATA: gv_fullpath TYPE string,
gv_path TYPE string,
gv_user_action TYPE i,
gv_default_extension TYPE string,
gv_default_file_name TYPE string,
gv_file_filter TYPE string,
gv_filename TYPE string,
gv_xmlstr TYPE string,
gv_initialpath TYPE string.
DATA: gt_xml_table TYPE STANDARD TABLE OF string,
gt_data TYPE STANDARD TABLE OF ty_data,
gt_content TYPE bcsy_text.
PARAMETERS: r_down RADIOBUTTON GROUP gr1,
r_mail RADIOBUTTON GROUP gr1.
* Start of selection
START-OF-SELECTION.
" Populate internal table
PERFORM get_data.
" Call XSL transformation
CALL TRANSFORMATION z_excel_download
SOURCE table = gt_data
var = 'Testing variable'
RESULT XML gv_xmlstr.
IF r_mail IS NOT INITIAL.
PERFORM convert_to_table
USING gv_xmlstr
CHANGING gt_content.
PERFORM send_mail USING gt_content.
ENDIF.
IF r_down IS NOT INITIAL.
APPEND gv_xmlstr TO gt_xml_table.
PERFORM download.
ENDIF.
*&---------------------------------------------------------------------*
*& Form GET_DATA
*&---------------------------------------------------------------------*
*----------------------------------------------------------------------*
FORM get_data .
REFRESH gt_data[].
SELECT carrid
connid
fldate
price
planetype
seatsmax
seatsocc
FROM sflight
INTO TABLE gt_data
UP TO 50 ROWS.
ENDFORM. " GET_DATA
*&---------------------------------------------------------------------*
*& Form DOWNLOAD
*&---------------------------------------------------------------------*
*----------------------------------------------------------------------*
FORM download .
" Determine file type
MOVE '.XLS' TO gv_default_extension.
MOVE 'XLS files (.XLS)|.XLS' TO gv_file_filter.
" Pop up to save file in presentation server
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
default_extension = gv_default_extension
default_file_name = gv_default_file_name
file_filter = gv_file_filter
initial_directory = gv_initialpath
CHANGING
filename = gv_filename
path = gv_path
fullpath = gv_fullpath
user_action = gv_user_action
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
OTHERS = 3.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
" Download the Excel in the desired location
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = gv_fullpath
filetype = 'ASC'
TABLES
data_tab = gt_xml_table
EXCEPTIONS
file_write_error = 1
no_batch = 2
gui_refuse_filetransfer = 3
invalid_type = 4
OTHERS = 5.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDFORM. " DOWNLOAD
*&---------------------------------------------------------------------*
*& Form SEND_MAIL
*&---------------------------------------------------------------------*
* Send Mail
*----------------------------------------------------------------------*
FORM send_mail USING fpt_content TYPE bcsy_text.
TYPES: BEGIN OF ty_receivers,
rec_string TYPE adr6-smtp_addr,
cc TYPE flag,
bcc TYPE flag,
END OF ty_receivers.
DATA: lp_send_request TYPE REF TO cl_bcs,
lp_document TYPE REF TO cl_document_bcs,
lp_cx_bcs_exception TYPE REF TO cx_bcs,
lp_recipient TYPE REF TO if_recipient_bcs.
DATA: lt_receivers TYPE TABLE OF ty_receivers,
lt_receivers_main TYPE TABLE OF ty_receivers,
lt_addr_split TYPE TABLE OF adr6-smtp_addr.
DATA: ls_receivers TYPE ty_receivers,
ls_receivers_main TYPE ty_receivers,
ls_addr_check TYPE sx_address,
ls_addr_valid TYPE sx_address,
ls_receive TYPE ad_smtpadr.
DATA: lv_subject TYPE so_obj_des,
lv_subject_str TYPE string,
lv_sent_result TYPE os_boolean.
DATA: lt_to_address TYPE bcsy_smtpa,
ls_to_address LIKE LINE OF lt_to_address,
lt_cc_address TYPE bcsy_smtpa,
lt_bcc_address TYPE bcsy_smtpa,
lv_head_address TYPE ad_smtpadr.
CLEAR ls_to_address.
REFRESH lt_to_address[].
ls_to_address = 'xxx@xxx.com'. " Replace with Receivers Email address
APPEND ls_to_address TO lt_to_address.
TRY .
** Create Persistent Send Request
lp_send_request = cl_bcs=>create_persistent( ).
IF lp_send_request IS NOT INITIAL.
** Creation of office document
lv_subject = 'Test: Mail with Excel'.
lp_document = cl_document_bcs=>create_document(
i_type = 'XLS'
i_subject = lv_subject
i_text = fpt_content ).
** Add document to send request
CALL METHOD lp_send_request->set_document( lp_document ).
** Set subject string
lv_subject_str = 'Test: Mail with Excel'.
CALL METHOD lp_send_request->set_message_subject
EXPORTING
ip_subject = lv_subject_str.
* Receivers
CLEAR lt_receivers.
lv_head_address = 'xxx@xxx.com'. " Replace with Receivers Email address
IF lv_head_address IS NOT INITIAL.
CLEAR ls_receivers.
ls_receivers-rec_string = lv_head_address.
TRANSLATE ls_receivers-rec_string TO LOWER CASE. "#EC TRANSLANG
APPEND ls_receivers TO lt_receivers.
CLEAR ls_receivers.
ENDIF.
** To Address
LOOP AT lt_to_address INTO ls_receive.
CLEAR ls_receivers.
ls_receivers-rec_string = ls_receive.
TRANSLATE ls_receivers-rec_string TO LOWER CASE. "#EC TRANSLANG
APPEND ls_receivers TO lt_receivers.
CLEAR ls_receivers.
ENDLOOP.
** CC Address
LOOP AT lt_cc_address INTO ls_receive.
CLEAR ls_receivers.
ls_receivers-rec_string = ls_receive.
ls_receivers-cc = 'X'.
TRANSLATE ls_receivers-rec_string TO LOWER CASE. "#EC TRANSLANG
APPEND ls_receivers TO lt_receivers.
CLEAR ls_receivers.
ENDLOOP.
** CC Address
LOOP AT lt_bcc_address INTO ls_receive.
CLEAR ls_receivers.
ls_receivers-rec_string = ls_receive.
ls_receivers-bcc = 'X'.
TRANSLATE ls_receivers-rec_string TO LOWER CASE. "#EC TRANSLANG
APPEND ls_receivers TO lt_receivers.
CLEAR ls_receivers.
ENDLOOP.
** Scenarios where mail id are provided separated by ',' split is done here
LOOP AT lt_receivers INTO ls_receivers.
CLEAR lt_addr_split.
SPLIT ls_receivers-rec_string AT ',' INTO TABLE lt_addr_split.
CHECK lt_addr_split IS NOT INITIAL.
CLEAR ls_receivers_main.
LOOP AT lt_addr_split INTO ls_receivers_main-rec_string.
SHIFT ls_receivers_main-rec_string LEFT DELETING LEADING space.
ls_receivers_main-cc = ls_receivers-cc.
ls_receivers_main-bcc = ls_receivers-bcc.
APPEND ls_receivers_main TO lt_receivers_main.
CLEAR ls_receivers_main.
ENDLOOP.
ENDLOOP.
IF lt_receivers_main IS NOT INITIAL.
SORT lt_receivers_main.
DELETE ADJACENT DUPLICATES FROM lt_receivers_main COMPARING rec_string.
LOOP AT lt_receivers_main INTO ls_receivers_main.
ls_addr_check-type = 'INT'.
ls_addr_check-address = ls_receivers_main-rec_string.
CLEAR ls_addr_valid.
CALL FUNCTION 'SX_INTERNET_ADDRESS_TO_NORMAL'
EXPORTING
address_unstruct = ls_addr_check
IMPORTING
address_normal = ls_addr_valid
EXCEPTIONS
error_address_type = 1
error_address = 2
error_group_address = 3
OTHERS = 4.
IF sy-subrc EQ 0.
ls_receivers_main-rec_string = ls_addr_valid-address.
lp_recipient = cl_cam_address_bcs=>create_internet_address( ls_receivers_main-rec_string ).
** Add receipent to send request
CALL METHOD lp_send_request->add_recipient
EXPORTING
i_recipient = lp_recipient
i_express = ' '
i_copy = ls_receivers_main-cc
i_blind_copy = ls_receivers_main-bcc.
ENDIF.
ENDLOOP.
ENDIF.
** Send mail Immediately
CALL METHOD lp_send_request->set_send_immediately( 'X' ).
** Send Mail
CLEAR lv_sent_result.
CALL METHOD lp_send_request->send(
EXPORTING
i_with_error_screen = 'X'
RECEIVING
result = lv_sent_result ).
IF lv_sent_result IS INITIAL.
" Error Message
ENDIF.
WRITE: 'Mail sent'.
COMMIT WORK.
ENDIF.
CATCH cx_bcs INTO lp_cx_bcs_exception.
" Handle Exception here
EXIT.
ENDTRY.
ENDFORM. " SEND_MAIL
*&---------------------------------------------------------------------*
*& Form CONVERT_TO_TABLE
*&---------------------------------------------------------------------*
* Convert string to table
*----------------------------------------------------------------------*
FORM convert_to_table USING fpv_xmlstr TYPE string
CHANGING fpt_content TYPE bcsy_text.
DATA: lv_str_len TYPE i,
lv_lines TYPE i,
lv_mod TYPE i,
lv_offset TYPE i,
lv_offset1 TYPE i,
lv_length TYPE i,
lv_soli TYPE char255,
lv_xmlstr TYPE string,
lv_exit TYPE char1,
ls_content LIKE LINE OF fpt_content.
lv_str_len = STRLEN( fpv_xmlstr ).
lv_lines = lv_str_len / 255.
lv_mod = lv_str_len MOD 255.
IF lv_mod GT 0.
lv_lines = lv_lines + 1.
ENDIF.
lv_xmlstr = fpv_xmlstr.
lv_length = 255.
CLEAR: lv_offset, lv_offset1, lv_exit.
DO lv_lines TIMES.
lv_offset1 = lv_offset + 255.
IF lv_offset1 GT lv_str_len.
lv_length = lv_str_len - lv_offset.
lv_exit = 'X'.
ENDIF.
lv_soli = lv_xmlstr+lv_offset(lv_length).
ls_content = lv_soli.
APPEND ls_content TO fpt_content.
IF lv_exit IS NOT INITIAL.
EXIT.
ENDIF.
lv_offset = lv_offset + 255.
ENDDO.
ENDFORM. " CONVERT_TO_TABLE