*&---------------------------------------------------------------------*
*& Report  ZHCM_ABAP_TO_EXCEL
*&
*&---------------------------------------------------------------------*
*&
*& Original code credit: Alvaro Tejada Galindo, from Lima - Perú       *
*&---------------------------------------------------------------------*

REPORT  ZHCM_ABAP_TO_EXCEL.
INCLUDE ole2incl.

*&----------------------------------------------------*
*&   TYPES                                            *
*&----------------------------------------------------*
TYPES: BEGIN OF ty_spfli,
       carrid TYPE spfli-carrid,
       connid TYPE spfli-connid,
       countryfr TYPE spfli-countryfr,
       cityfrom TYPE spfli-cityfrom,
       airpfrom TYPE spfli-airpfrom,
       countryto TYPE spfli-countryto,
       cityto TYPE spfli-cityto,
       airpto TYPE spfli-airpto,
       END OF ty_spfli.

TYPES: BEGIN OF ty_titles,
       title(20) TYPE c,
       field(20) TYPE c,
       END OF ty_titles.

*&----------------------------------------------------*
*&   INTERNAL TABLES                                  *
*&----------------------------------------------------*
DATA: t_spfli TYPE STANDARD TABLE OF ty_spfli,
      t_titles TYPE STANDARD TABLE OF ty_titles.

*&----------------------------------------------------*
*&   FIELD-SYMBOLS                                    *
*&----------------------------------------------------*
FIELD-SYMBOLS: <fs_spfli> LIKE LINE OF t_spfli,
               <fs_titles> LIKE LINE OF t_titles,
               <fs> TYPE ANY.

*&----------------------------------------------------*
*&   VARIABLES                                        *
*&----------------------------------------------------*
DATA: w_tabix TYPE sy-tabix,
      w_titles TYPE sy-tabix,
      w_line TYPE sy-tabix,
      w_field TYPE string,
      filename TYPE string,
      path TYPE string,
      fullpath TYPE string.

DATA: data_titles TYPE REF TO data.

DATA: e_sheet TYPE ole2_object,
      e_activesheet TYPE ole2_object,
      e_newsheet TYPE ole2_object,
      e_appl TYPE ole2_object,
      e_work TYPE ole2_object,
      e_cell TYPE ole2_object,
      e_color TYPE ole2_object,
      e_bold TYPE ole2_object.

*&----------------------------------------------------*
*&   SELECTION-SCREEN                                 *
*&----------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1.
PARAMETERS: p_file TYPE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK b1.

*&----------------------------------------------------*
*&  START-OF-SELECTION                                *
*&----------------------------------------------------*
START-OF-SELECTION.
  PERFORM get_titles.
  PERFORM get_data.
  PERFORM create_excel.

*&----------------------------------------------------*
*& AT SELECTION-SCREEN                                *
*&----------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title      = 'Select archive'
      default_extension = 'xls'
      file_filter       = '*.xls'
    CHANGING
      filename          = filename
      path              = path
      fullpath          = fullpath.

  IF sy-subrc EQ 0.
    p_file = fullpath.
  ENDIF.

*&----------------------------------------------------*
*&      Form  get_titles                              *
*&----------------------------------------------------*
FORM get_titles.

  CREATE DATA data_titles TYPE ty_titles.
  ASSIGN data_titles->* TO <fs_titles>.

  <fs_titles>-title = 'CARRIED ID'.
  <fs_titles>-field = 'CARRID'.
  APPEND <fs_titles> TO t_titles.
  <fs_titles>-title = 'CONNECTION ID'.
  <fs_titles>-field = 'CONNID'.
  APPEND <fs_titles> TO t_titles.
  <fs_titles>-title = 'COUNTRY FROM'.
  <fs_titles>-field = 'COUNTRYFR'.
  APPEND <fs_titles> TO t_titles.
  <fs_titles>-title = 'AIRPORT FROM'.
  <fs_titles>-field = 'AIRPFROM'.
  APPEND <fs_titles> TO t_titles.
  <fs_titles>-title = 'COUNTRY TO'.
  <fs_titles>-field = 'COUNTRYTO'.
  APPEND <fs_titles> TO t_titles.
  <fs_titles>-title = 'CITY TO'.
  <fs_titles>-field = 'CITYTO'.
  APPEND <fs_titles> TO t_titles.
  <fs_titles>-title = 'AIRPORT TO'.
  <fs_titles>-field = 'AIRPTO'.
  APPEND <fs_titles> TO t_titles.

ENDFORM.                    "get_titles

*&----------------------------------------------------*
*&      Form  get_data                                *
*&----------------------------------------------------*
FORM get_data.

  SELECT carrid connid countryfr cityfrom
         airpfrom countryto cityto airpto
  INTO TABLE t_spfli
  FROM spfli
  WHERE carrid EQ 'LH'.

ENDFORM.                    " get_data

*&----------------------------------------------------*
*&      Form  create_excel                            *
*&----------------------------------------------------*
FORM create_excel.

  w_line = 1.

  CREATE OBJECT e_appl 'EXCEL.APPLICATION'.
  SET PROPERTY OF e_appl 'VISIBLE' = 1.

  CALL METHOD OF e_appl 'WORKBOOKS' = e_work.
  CALL METHOD OF e_work 'Add' = e_work.

  GET PROPERTY OF e_appl 'ActiveSheet' = e_activesheet.
  SET PROPERTY OF e_activesheet 'Name' = 'Flights'.

  LOOP AT t_spfli ASSIGNING <fs_spfli>.
    w_tabix = sy-tabix.
    w_line = w_line + 1.

    LOOP AT t_titles ASSIGNING <fs_titles>.
      w_titles = sy-tabix.
      CALL METHOD OF e_appl 'Cells' = e_cell
        EXPORTING
          #1 = 1
          #2 = w_titles.
      SET PROPERTY OF e_cell 'Value' =  <fs_titles>-title.
      GET PROPERTY OF e_cell 'Interior' = e_color.
      SET PROPERTY OF e_color 'ColorIndex' = 35.

      GET PROPERTY OF e_cell 'Font' = e_bold.
      SET PROPERTY OF e_bold 'Bold' = 1.

      CALL METHOD OF e_appl 'Cells' = e_cell
        EXPORTING
          #1 = w_line
          #2 = w_titles.

      CONCATENATE '<fs_spfli>-' <fs_titles>-field
      INTO w_field.
      ASSIGN (w_field) TO <fs>.

      SET PROPERTY OF e_cell 'Value' = <fs>.
      GET PROPERTY OF e_cell 'Interior' = e_color.
      SET PROPERTY OF e_cell 'ColumnWidth' = 20.
      SET PROPERTY OF e_color 'ColorIndex' = 0.
      GET PROPERTY OF e_cell 'Font' = e_bold.
      SET PROPERTY OF e_bold 'Bold' = 0.
    ENDLOOP.
  ENDLOOP.

  CALL METHOD OF e_work 'SAVEAS'
    EXPORTING
      #1 = p_file.

  CALL METHOD OF e_work 'close'.
  CALL METHOD OF e_appl 'QUIT'.
  FREE OBJECT e_appl.

ENDFORM.                    " create_excel