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.
