Skip to Content

Excel OLE and ABAP – Create fancy reports

February 5, 2009 | 2,457 Views |
 abapalvaro tejadagalindo3beginner
Follow

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

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  2. 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.
    (0) 
    1. Alvaro Tejada Galindo
      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.

      (0) 
      1. 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. 
        (0) 
  4. 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.
    (0) 
    1. Alvaro Tejada Galindo
      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.

      (0) 
  9. Former Member
    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

    (0) 

Leave a Reply