
*&---------------------------------------------------------------------*
*& 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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |