|Home • Trainings • Quiz • Tips • Tutorials • Functional • Cert Q's • Interview Q's • Jobs • Testimonials • Advertise • Contact Us|
Downloading report output to Excel using OLE
By Lokesh Tripathi, Infosys
In the following document we will be
discussing how to download our report output into excel in the same format as in
the SAP report output by using OLE concept.
address an OLE Automation Server (e.g. EXCEL) from ABAP, the server must be
registered with SAP.
keywords allow us to control the applications in the form of an OLE2 Automation
Server from an ABAP program:
The following ABAP key words
control the applications:
command generates an object of the class “class”.
form: CREATE OBJECT obj class.
LANGUAGE langu - determines the language chosen for method and attribute
names of the object class. If no specification is made, English is the default.
This creates the instance for the automation object at
the automation interface.
This command sets the property
prop of the object obj
according to the contents of the
The object obj must be of
Basic form: SET PROPERTY OF
obj prop = fld.
Addition: NO FLUSH- The
addition NO FLUSH continues the collection process; even if the next command is
not an OLE statement.Thus we can set series of properties in a single transport
This command copies the property p
of the object obj to the field f. The object obj must
be of type OLE2_OBJECT.
form: GET PROPERTY OF obj p = f.
Addition: NO FLUSH
The ABAP key word CALL METHOD calls
the method m of the object obj. m can be a literal or a
Basic form: CALL METHOD OF
This will releases the storage
space required for the object obj. The object can then no longer be
Now let understand the concept
of OLE by taking one example.
Suppose we want exactly this
type of output in excels.
Before starting with OLE, include type pool
ole2 in your program.
Note that for each OLE object there has to be a
variable holding handle data for that object. The type-pool “ole2” defines
the handle variable data of the type ole2_object.For all the OLE
automation programs “OLE2INCL” include should be used.
First step is to create OLE object for excel
is the object of type ole2_object.
Now different properties of
the excel object is to be set as per our requirement.
SET PROPERTY OF gh_excel ‘visible’ =
The excel sheet is not visible to the user
while data transfer.
CALL METHOD OF gh_excel 'Workbooks'=
This will call the method of excel ‘Workbooks’ to get the control to
gh_mapl is the object of type ole2_object.
METHOD OF gh_mapl 'Add'= gh_map.
will create a new workbook.
create active sheets of excel.
CALL METHOD OF application 'Worksheets' = sheet
EXPORTING #1 = 1.
METHOD OF sheet 'Activate'.
“Activate the sheet.
PROPERTY OF sheet 'Name' = 'sheet_name'.
Sheet is the object of type ole2_object.
Similarly you can create many number of sheets in excel.
Now fill the data part into excel.
We will first fill the header part in excel.
fill_cell USING 1 7 1 ‘MONTHLY_BILL’.
will print “Monthly_Bill” in the 7th column of the 1st
row of excel.
we had used the form “FILL_CELL”
for filling the data into the cells.
FORM fill_cell USING
i j bold val.
filling data in excel.
CALL METHOD OF gh_excel ‘CELLS’= gh_zl
EXPORTING #1 = i #2 = j.
“Transferring data to row = i and
column = j.
handling automation error.
PROPERTY OF gh_zl ‘VALUE’ = val no flush.
PROPERTY OF gh_zl ‘FONT’ = gh_f no flush.
PROPERTY OF gh_f ‘BOLD’ = bold
i = excel row number.
excel column number.
Bold = 1
then text will print in Bold.
0 then normal text will be
printed in excel.
Val = text you want to
fill all the header details in excel.
fill_cell USING 2 1 1 ‘VODAPHONE’. “Transferring
data to 2nd row and 1st column.
fill_cell USING 2 2 1 ‘COMPANY’.
data to 2nd row and 2nd column.
fill_cell USING 3 2 1 WA_OUTPUT-PEIOD. “Transferring data to 3rd row and 2nd column.
fill_cell USING 4 1 1 ‘S. No’. “Transferring
data to 4th row and 1st column.
fill_cell USING 4 2 1 ‘Customer Name’. “Transferring
data to 4th row and 2nd column.
fill_cell USING 4 3 1 ‘Duration’.
“Transferring data to 4th
row and 3rd column.
fill_cell USING 4 4 1 ‘Cost’. “Transferring
data to 4th row and 4th column.
fill_cell USING 4 5 1 ‘Bill’.
“Transferring data to 4th row and 5th column.
After filling the header part fill all the line item details in excel.
Please send us your feedback/suggestions at webmaster@SAPTechnical.COM
©2006-2007 SAPTechnical.COM. All rights reserved.
product names are trademarks of their respective companies. SAPTechnical.COM
is in no way affiliated with SAP AG.
Graphic Design by Round the Bend Wizards