Export data into Excel using OLE OBJECTS vs. GUI_DOWNLOAD

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.