Excel Sheet Creation from ABAP

TYPE-POOLS ole2.
DATA: wf_cell_from  TYPE ole2_object,
      wf_cell_from1 TYPE ole2_object,
      wf_cell_to    TYPE ole2_object,
      wf_cell_to1   TYPE ole2_object,
      wf_excel      TYPE ole2_object,   " Excel object
      wf_mapl       TYPE ole2_object,   " list of workbooks
      wf_map        TYPE ole2_object,   " workbook
      wf_worksheet  TYPE ole2_object,   " Worksheet
      wf_cell       TYPE ole2_object,   " Cell Range
      wf_cell1      TYPE ole2_object,
      wf_range      TYPE ole2_object,   " Range of cells to be formatted
      wf_range2     TYPE ole2_object,
      wf_column1    TYPE ole2_object.   " Column to be Autofit
 
 
DATA: BEGIN OF t_hex,
      l_tab TYPE x,
      END OF t_hex.
 
DATA: wf_deli(1) TYPE c,            "delimiter
      wf_action TYPE i,
      wf_file TYPE string,
      wf_path TYPE string,
      wf_fullpath TYPE string.
 
 
 
TYPES: t_data1(1500) TYPE c,
       int_ty TYPE TABLE OF t_data1. "line type internal table
 
*All the data was prepared as line type internal tables for faster
*download
 
DATA: int_matl  TYPE int_ty ,
      int_matl1 TYPE int_ty ,
      wa_matl   TYPE t_data1.
 
TYPES: BEGIN OF ty_mara,
       matnr TYPE matnr,
       mtart TYPE mtart,
       matkl TYPE matkl,
       meins TYPE meins,
       END OF ty_mara.
 
DATA: int_mara TYPE STANDARD TABLE OF ty_mara,
      wa_mara TYPE ty_mara.
 
FIELD-SYMBOLS: <fs> .
 
DATA: wc_sheets LIKE sy-index.  "no.of sheets
DATA: it_tabemp TYPE filetable,
      gd_subrcemp TYPE i.
 
CONSTANTS wl_c09(2) TYPE n VALUE 09.
 
CLEAR wc_sheets.
 
 
DEFINE ole_check_error.
  if &1 ne 0.
    message e001(zz) with &1.
    exit.
  endif.
END-OF-DEFINITION.
 
SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_file   LIKE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK block1.
 
 
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
 
  REFRESH: it_tabemp.
 
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title         = 'Select File'
*      default_extension    = 'xls'
      default_file_name    = 'Material Details'
*      with_encoding        =
      file_filter          = '*.xls'
      initial_directory    = 'C:\'
      prompt_on_overwrite  = ' '
    CHANGING
      filename             = wf_file
      path                 = wf_path
      fullpath             = wf_fullpath
      user_action          = wf_action
*      file_encoding        =
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4
          .
  IF sy-subrc <> 0.
*   MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*              WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.
 
  IF wf_action EQ 9.
    MESSAGE 'No File have been Selected' TYPE 'S'.
  ELSE.
    p_file = wf_fullpath.
    PERFORM create_excel.
  ENDIF.
 
*&---------------------------------------------------------------------*
*&      Form  create_excel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM create_excel.
  LOOP AT it_tabemp INTO p_file.
  ENDLOOP.
* START THE EXCEL APPLICATION
  CREATE OBJECT wf_excel 'EXCEL.APPLICATION'.
  PERFORM err_hdl.
 
* PUT EXCEL IN FRONT
  SET PROPERTY OF wf_excel  'VISIBLE' = 1.
  PERFORM err_hdl.
 
* CREATE AN EXCEL WORKBOOK OBJECT
  CALL METHOD OF wf_excel 'WORKBOOKS' = wf_mapl.
  PERFORM err_hdl.
 
  SET PROPERTY OF wf_excel 'SheetsInNewWorkbook' = 3. "no of sheets
  PERFORM err_hdl.
 
  CALL METHOD OF wf_mapl 'ADD' = wf_map.
  PERFORM err_hdl.
 
 
*Assign the Delimiter to field  symbol.
  ASSIGN wf_deli TO <fs> TYPE 'X'.
  t_hex-l_tab = wl_c09.
  <fs> = t_hex-l_tab.
 
  CLEAR int_matl.
  REFRESH int_matl.
  SELECT matnr
       mtart
       matkl
       meins
      FROM mara
      INTO CORRESPONDING FIELDS OF TABLE int_mara.
*first the headings will be displayed  in the excel sheet
  CONCATENATE 'Material Number'
  'Material type'
  'Material Group'
  'Base Unit of Measure'
  INTO wa_matl
  SEPARATED BY wf_deli.
  APPEND wa_matl TO int_matl.
 
 
  LOOP AT int_mara INTO wa_mara.
    CONCATENATE wa_mara-matnr
                wa_mara-mtart
                wa_mara-matkl
                wa_mara-meins
                INTO wa_matl
                SEPARATED BY wf_deli.
    APPEND wa_matl TO int_matl.
    CLEAR wa_matl.
  ENDLOOP.
 
*Copyng thae same contents to another table to display in
*new sheet
  MOVE int_matl TO int_matl1.
  PERFORM f_material_details
  TABLES int_matl
  USING  1.
 
  PERFORM f_material_details
  TABLES int_matl
  USING  2.
 
 
  GET PROPERTY OF wf_excel 'ActiveSheet' = wf_map.
  GET PROPERTY OF wf_excel 'ActiveWorkbook' = wf_mapl.
 
  CALL FUNCTION 'FLUSH'
    EXCEPTIONS
      cntl_system_error = 1
      cntl_error        = 2
      OTHERS            = 3.
  IF sy-subrc = 0.
 
    CALL METHOD OF wf_map 'SAVEAS'
      EXPORTING #1 = p_file.
  ENDIF.
 
  CALL METHOD OF wf_mapl 'CLOSE'.
  CALL METHOD OF wf_excel 'QUIT'.
 
  FREE OBJECT wf_mapl.
  FREE OBJECT wf_map.
  FREE OBJECT wf_excel.
 
ENDFORM.                    "create_excel
*&---------------------------------------------------------------------*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM err_hdl.
 
  IF sy-subrc <> 0.
    WRITE: / 'OLE ERROR: RETURN CODE ='(i10), sy-subrc.
    STOP.
  ENDIF.
 
ENDFORM.                    "ERR_HDL
 
*-- End of Program
*&---------------------------------------------------------------------*
*&      Form  f_material_details
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM f_material_details
   TABLES lint_matl
  USING l_sheet_no TYPE i.
 
  DATA: lv_lines TYPE i,
        lv_sheet_name(50) TYPE c.
 
  wc_sheets = l_sheet_no.
  CASE l_sheet_no.
    WHEN 1.
      lv_sheet_name = 'Material_sheet1'.
    WHEN 2.
      lv_sheet_name = 'Material_sheet2'.
  ENDCASE.
 
 
*-- activating the worksheet and giving a  name to it
  CALL METHOD OF wf_excel 'WORKSHEETS' = wf_worksheet
    EXPORTING
    #1 = wc_sheets.
  CALL METHOD OF wf_worksheet 'ACTIVATE'.
 
 
 
  SET PROPERTY OF wf_worksheet 'NAME' = lv_sheet_name.
 
*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to
    EXPORTING
    #1 = lv_lines
    #2 = 4.
*--range of cells to be formatted (in this case 1 to 4)
  CALL METHOD OF wf_excel 'Range' = wf_cell
    EXPORTING
    #1 = wf_cell_from
    #2 = wf_cell_to.
 
*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from1
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to1
    EXPORTING
    #1 = lv_lines
    #2 = 1.
  CALL METHOD OF wf_excel 'Range' = wf_cell1  " Cell range for first
 
                                              " column(Material)
    EXPORTING
    #1 = wf_cell_from1
    #2 = wf_cell_to1.
 
  SET PROPERTY OF wf_cell1 'NumberFormat' = '@' . "To disply zeros
  "in Material number
 
 
  DATA l_rc TYPE i.
*DATA download into excel first sheet
  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data         = lint_matl[]
    CHANGING
      rc           = l_rc
    EXCEPTIONS
      cntl_error   = 1
      error_no_gui = 2
      OTHERS       = 4.
  CALL METHOD OF wf_worksheet 'Paste'.
 
  CALL METHOD OF wf_excel 'Columns' = wf_column1.
  CALL METHOD OF wf_column1 'Autofit'.
  FREE OBJECT wf_column1.
 
ENDFORM.                    " f_material_details

Reference link:

https://archive.sap.com/discussions/thread/1260102

https://blogs.sap.com/2012/03/29/using-ole2-objects-for-create-an-excel-file/