This post displays a program code that exports data from internal table into an excel document using two different methods namely, OLE Objects and function module GUI_DOWNLOAD.
Initial screen of this is below.
With GUI_DOWNLOAD, all field data of the internal table will be in ONLY ONE cell of the generated excel file. This option is faster than OLE since all data fields were placed into one excel cell.
But with OLE Objects, you can specify in what excel cell the specific data field of the internal will be placed after the export process. You can also specify font style, font colors, font weight, cell borders, and etc.
The disadvantage of this option is this will take time to export data especially dealing with large number of data rows. This is because of the processing of putting specific data field value into a specific cell number. The styles mentioned also will affect the performance of export process.
Create a sample program and paste the following codes below.
REPORT ZPROGRAMTEST_BERT.
*&———————————————————————*
*& INCLUDES
*&———————————————————————*
INCLUDE ole2incl. “for OLE export data to excel
*&———————————————————————*
*& DATA DECLARATIONS
*&———————————————————————*
DATA: “internal table and workarea
i_mara TYPE STANDARD TABLE OF mara,
x_mara TYPE mara,
“variables
v_file TYPE string,
v_filename TYPE string,
v_path TYPE string,
v_fullpath TYPE string,
v_row TYPE i,
“excel objects
o_excel TYPE ole2_object, “ excel object
o_mapl TYPE ole2_object, “ list of workbooks
o_map TYPE ole2_object, “ workbook
o_zl TYPE ole2_object. “ cell
*&———————————————————————*
*& START-OF-SELECTION.
*&———————————————————————*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME.
PARAMETERS: p_file TYPE rlgrap-filename.
ULINE.
PARAMETERS: rb_guidl RADIOBUTTON GROUP g1,
rb_oledl RADIOBUTTON GROUP g1.
SELECTION-SCREEN END OF BLOCK b1.
*&———————————————————————*
*& AT SELECTION-SCREEN
*&———————————————————————*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
“call the dialog for directory
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
default_extension = ’xls’
default_file_name = ’Exported_File’
initial_directory = ’c:\temp\’
CHANGING
filename = v_filename
path = v_path
fullpath = v_fullpath.
MOVE v_fullpath TO p_file.
*&———————————————————————*
*& START-OF-SELECTION.
*&———————————————————————*
START-OF-SELECTION.
PERFORM f_get_data.
*&———————————————————————*
*& END-OF-SELECTION.
*&———————————————————————*
END-OF-SELECTION.
PERFORM f_export_data.
*&———————————————————————*
*& FORM f_get_data.
*&———————————————————————*
*& Retrieve data from table
*&———————————————————————*
FORM f_get_data.
REFRESH: i_mara.
SELECT *
FROM mara
INTO TABLE i_mara
UP TO 20 ROWS.
IF sy-subrc EQ 0.
ENDIF.
ENDFORM. “ f_get_data
*&———————————————————————*
*& FORM f_export_data.
*&———————————————————————*
*& Export data depending on what was selected in the screen
*&———————————————————————*
FORM f_export_data.
IF rb_guidl IS NOT INITIAL.
PERFORM f_export_gui_download.
ELSEIF rb_oledl IS NOT INITIAL.
PERFORM f_export_ole_download.
ENDIF.
ENDFORM. “ f_export_data
*&———————————————————————*
*& FORM f_export_gui_download.
*&———————————————————————*
*& Export data using function module GUI_DOWNLOAD
*&———————————————————————*
FORM f_export_gui_download.
CALL FUNCTION ’GUI_DOWNLOAD’
EXPORTING
filename = v_fullpath
TABLES
data_tab = i_mara
EXCEPTIONS
FILE_WRITE_ERROR = 1
NO_BATCH = 2
GUI_REFUSE_FILETRANSFER = 3
INVALID_TYPE = 4
NO_AUTHORITY = 5
UNKNOWN_ERROR = 6
HEADER_NOT_ALLOWED = 7
SEPARATOR_NOT_ALLOWED = 8
FILESIZE_NOT_ALLOWED = 9
HEADER_TOO_LONG = 10
DP_ERROR_CREATE = 11
DP_ERROR_SEND = 12
DP_ERROR_WRITE = 13
UNKNOWN_DP_ERROR = 14
ACCESS_DENIED = 15
DP_OUT_OF_MEMORY = 16
DISK_FULL = 17
DP_TIMEOUT = 18
FILE_NOT_FOUND = 19
DATAPROVIDER_EXCEPTION = 20
CONTROL_FLUSH_ERROR = 21
OTHERS = 22.
IF sy-subrc <> 0.
* Implement suitable error handling here
ELSE.
WRITE: ’Export data to excel using GUI_DOWNLOAD successful.’.
ENDIF.
ENDFORM. “ f_export_gui_download
*&———————————————————————*
*& FORM f_export_ole_download.
*&———————————————————————*
*& Export data using OLE EXCEL
*&———————————————————————*
FORM f_export_ole_download.
“create excel document
PERFORM f_notification USING ’Creating excel document…’.
CREATE OBJECT o_excel ’EXCEL.APPLICATION’.
PERFORM f_check_ole_err.
“set visibility to background
SET PROPERTY OF o_excel ’Visible’ = 0.
“get list of workbooks, initially empty
CALL METHOD OF o_excel ’Workbooks’ = o_mapl.
PERFORM f_check_ole_err.
“add a new workbook
call method of o_mapl ’Add’ = o_map.
PERFORM f_check_ole_err.
“fill data to header of the excel file
CLEAR: v_row.
PERFORM f_fill_header.
“download data to excel
PERFORM f_notification USING ’Downloading data to excel…’.
PERFORM f_download_full.
“save the excel file
CALL METHOD OF o_map ’SAVEAS’ EXPORTING #1 = p_file.
PERFORM f_check_ole_err.
CALL METHOD OF o_map ’CLOSE’.
PERFORM f_check_ole_err.
“quit excel application
CALL METHOD OF o_excel ’QUIT’.
PERFORM f_check_ole_err.
“successful message
WRITE: ’Export data to excel using OLE successful.’.
“free the objects
FREE OBJECT: o_excel,
o_mapl,
o_map,
o_zl.
ENDFORM. “ f_export_ole_download
*———————————————————————-*
* FORM f_notification.
* –> inform user on what is going on using SAPGUI_PROGRESS_INDICATOR
*———————————————————————-*
FORM f_notification USING pi_message TYPE string.
CALL FUNCTION ’SAPGUI_PROGRESS_INDICATOR’
EXPORTING
text = pi_message.
ENDFORM. “ f_notification
*———————————————————————-*
* FORM f_check_ole_err.
* –> check if OLE processing if there is an error occured
*———————————————————————-*
form f_check_ole_err.
if sy-subrc NE 0.
MESSAGE ’OLE-Automation Error.’ TYPE ’E' DISPLAY LIKE ’S’.
STOP.
endif.
endform. “ f_check_ole_err
*———————————————————————-*
* FORM f_fill_header.
* –> set value to header for the excel file
*———————————————————————-*
FORM f_fill_header.
v_row = v_row + 1.
PERFORM f_fill_cell USING v_row 1 ’Material’.
PERFORM f_fill_cell USING v_row 2 ’Created On’.
PERFORM f_fill_cell USING v_row 3 ’Created by’.
PERFORM f_fill_cell USING v_row 4 ’Last Change’.
PERFORM f_fill_cell USING v_row 5 ’Changed by’.
ENDFORM. “ f_fill_header
*———————————————————————-*
* FORM f_fill_cell.
* –> fill data to excell cell
*———————————————————————-*
form f_fill_cell USING pi_row TYPE i
pi_col TYPE i
pi_val TYPE ANY.
CALL METHOD OF o_excel ’Cells’ = o_zl
EXPORTING
#1 = pi_row
#2 = pi_col.
SET PROPERTY OF o_zl ’Value’ = pi_val.
endform. “ f_fill_cell
*———————————————————————-*
* FORM f_download_full.
* –> Fill excel cell with data from internal table
*———————————————————————-*
FORM f_download_full.
LOOP AT i_mara INTO x_mara.
v_row = v_row + 1.
PERFORM f_fill_cell USING v_row 1 x_mara-matnr.
PERFORM f_fill_cell USING v_row 2 x_mara-ersda.
PERFORM f_fill_cell USING v_row 3 x_mara-ernam.
PERFORM f_fill_cell USING v_row 4 x_mara-laeda.
PERFORM f_fill_cell USING v_row 5 x_mara-aenam.
ENDLOOP.
ENDFORM.