Skip to Content

Normally, we use the Function module WS_EXCEL to download the data into an excel sheet, but sometimes we may require to download the different data in to an excel with more than one sheet, in that case we can make use of the OLE concept to achieve this one. Use the Class cl_gui_frontend_services,In this class use the method clipboard_export

Let’s take an example to create 2 sheets in an Excel, and then take 2 internal tables. Here we need to use the include program ole2incl. In the program we use the type ole2_object for the ole type variables. Use this below 8 lines code to generate an excel with a single sheet

CREATE OBJECT ‘EXCEL.APPLICATION’.

CALL METHOD OF H_EXCEL ‘WORKBOOKS’ = H_MAPL

SET PROPERTY OF H_EXCEL ‘VISIBLE’ = 1.

CALL METHOD OF H_MAPL ‘ADD’ = H_MAP

GET PROPERTY OF H_EXCEL ‘ACTIVESHEET’ = WORKSHEET.

SET PROPERTY OF WORKSHEET ‘NAME’ = ‘SHEET NO 1’.

After this one, call the above mentioned method

CALL METHOD CL_GUI_FRONTED_SERVICES=>CLIPBOARD_EXPORT

IMPORTING

DATA = ITAB1

CHANGING

RC = L_RC

EXCEPTIONS

CNTL_ERROR = 1

ERROR_NO_GUI = 2

NOT_SUPPORTED_BU_GUI = 3

OTHERS = 4.

CALL METHOD OF WORKSHEET ‘PASTE’.

For generating the second sheet

GET PROPERTY OF H_EXCEL ‘SHEETS’ = H_SHEET2 .

CALL METHOD OF H_SHEET2 ‘ADD’ = H_MAP.

SET PROPERTY OF H_MAP ‘NAME’ = GV_SHEET_NAME.

GET PROPERTY OF H_EXCEL ‘ACTIVESHEET’ = ‘Sheet No 2’.

CALL METHOD CL_GUI_FRONTED_SERVICES=>CLIPBOARD_EXPORT

IMPORTING

DATA = ITAB2

CHANGING

RC = L_RC

EXCEPTIONS

CNTL_ERROR = 1

ERROR_NO_GUI = 2

NOT_SUPPORTED_BU_GUI = 3

OTHERS = 4.

CALL METHOD OF WORKSHEET ‘PASTE’.

After this code we need to free the every object which we used in the above to disconnect the link between the Program and the Excel sheet

FREE OBJECT H_EXCEL.

To report this post you need to login first.

4 Comments

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

  1. Mahendra Khandagale
    Hi,

    Thanks for this.. It helps a lot.

    I hv a requirement. The user wants to display multiple rows into one cell. Can you give me some hints.

    Searched on forum but could not get required
    so trying this.
    Thanks in advance.

    Best Regards
    MAhendra

    (0) 
    1. Sudheer Junnuthula Post author
      Hi,
      Multiple rows in a cell is possible, whatever is possible with the excel sheet is possible with these classes, IN the excel sheet, Tools –> macro –> Record a Macro, then do what ever you want in the excel then save the excel, then come back to Tools –> Macro –> Your Macro, there you find the Code, just add the code in your ABAP with the ABAP format

      Regards
      Sudheer

      (0) 

Leave a Reply