Excel OLE and ABAP – Create fancy reports

The other day the Suit asked to do an Excel report. Piece of cake I think…Well…That wasn’t true…He wanted titles, background colors, bold font and some structure on the file.

We all know that when we download an Excel report usign ABAP, no matter what we do, we’re going to always have the same boring structure. No colors and no fancy stuff.

Of course, I think about Excel OLE…But haven’t work with it, so I browsed SCN to try to find something good to learn. I land to Manipulate Excel with OLE & ABAP written by my good friend Rich Heilman .

The blog is really great but lack of some important things that I needed for my report…The file needed to be available of the system and he wasn’t any colors of font enhancements…Sure, he was just learning how to use the tools at that time, so more than a critic, I think it’s really cool that he shared his knowledge while he was still learning.

Anyway…I keep browsing the forums and found all the needed info to finish my report. After that I decided to build a small and simple example, showing what you can do with Excel OLE and ABAP.

 

 

&—-

---

*& Report  ZDUMMY_SANDBOX                             *

&—-

---

REPORT zdummy_sandbox.

&—-

---

• INCLUDES                                            *

&—-

---

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:  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 archivo’

      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  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