Skip to Content
Author's profile photo Alvaro Tejada Galindo

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

Assigned Tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Steve Oldner
      Steve Oldner
      Thanks for the code, saw the post on Twitter and followed the url.
      Author's profile photo Alvaro Tejada Galindo
      Alvaro Tejada Galindo
      Blog Post Author
      Steven:

      I'm so glad you like it -:) I now following on Twitter by the way -;)

      Greetings,
      Blag.

      Author's profile photo Christian Jianelli
      Christian Jianelli
      We all know that it's only a basic example of excel reports using ole but this approach (editing cell by cell) is very time consuming. Using ranges and copy and paste operations to  transfer data and format cells makes it too much faster.
      Author's profile photo Alvaro Tejada Galindo
      Alvaro Tejada Galindo
      Blog Post Author
      Hi Christian:

      Sure, cell by cell got serious performance issues...But according to what the suit asked me to do, that was the best way to do it...Also, you're not going to use this example for by say 4K rows, right? -;)

      Greetings,
      Blag.

      Author's profile photo Christian Jianelli
      Christian Jianelli
      Ok Blag. My intention was only post here a warning because i had a requirement in the past and i started with this approach (i had no idea about report´s size in PRD system). It just doesn´t worked (of course not) and i had go back to design fase. 
      Author's profile photo Sivapriya Nandyala
      Sivapriya Nandyala

      Hi Christian,

      Can you please some light on using ranges and copy and paste operations to transfer data and format cells using OLE? Any referennce links will be a great help. I have a requirement where in I have to design a complex layout for invoice, packing list, delivery document in excel and from SAP.

      Any guidance on this to ease the process is highly appreciated.

       

      Regards,

      Sivapriya. N

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      Hola Blag,See you on twitter 😉

      Author's profile photo Former Member
      Former Member
      Hi Blag
      Thanks for posting, we are ABAP learning beginners and I showed your code to my buds and we think it is cool what you can do with SAP.
      Thanks for sharing.
      Author's profile photo Valeriy Zauzolkov
      Valeriy Zauzolkov
      1
      Author's profile photo Alvaro Tejada Galindo
      Alvaro Tejada Galindo
      Blog Post Author
      Valeriy:

      This is most weird comment I ever get...

      Greetings,
      Blag.

      Author's profile photo Naimesh Patel
      Naimesh Patel
      Hello Blag..!

      The same kind of formatted excels can be achieved with help of XML transformation.

      Check this blog: https://weblogs.sdn.sap.com/pub/wlg/13092

      Regards,
      Naimesh Patel

      Author's profile photo Alvaro Tejada Galindo
      Alvaro Tejada Galindo
      Blog Post Author
      Naimesh:

      Already read your blog and post a comment...Really cool stuff...I'm really excited about the fact that after Rich's blog...I posted a blog, and then Michael post a blog and now you are doing your own...It's just great to learn that we had so many options to achieve the same goal. -:)

      Greetings,
      Blag.

      Author's profile photo Tomasz Sawczuk
      Tomasz Sawczuk
      Author's profile photo Jay Raj
      Jay Raj
      Nice one, good to know different approaches to integrate SAP with OLE.
      Author's profile photo Rahul P
      Rahul P
      Hi,

      I am doing a program by integrating ABAP with EXCEL, i need to write the following code in ABAP which is recorded by macro.

      I need to change the Category Type of the AXIS from Default to Text.

      "ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale"

      Thanks,

      Venky