Downloading report output to Excel using OLE

By Lokesh Tripathi, Infosys

Introduction

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.

To address an OLE Automation Server (e.g. EXCEL) from ABAP, the server must be registered with SAP.  

ABAP keywords  

ABAP 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:

1)       CREATE OBJECT 

This command generates an object of the class “class”.

Basic form: CREATE OBJECT obj class.

Addition: 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.  

2) SET PROPERTY

This command sets the property prop of the object obj according to the contents of   the field fld. The object obj must be of type OLE2_OBJECT.  

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 operation.  

3) GET PROPERTY

 This command copies the property p of the object obj to the field f. The object obj must be of type OLE2_OBJECT.

 Basic form: GET PROPERTY OF obj p = f.

 Addition: NO FLUSH  

 4) CALL METHOD

 The ABAP key word CALL METHOD calls the method m of the object obj. m can be a literal or a variable.

Basic form: CALL METHOD OF obj m.

Additions:

1.  = f   Stores the return value of the method in the variable f.

2.  EXPORTING p1 = f1... pn = fn 

It passes values of fields to the parameters of the method.

If assignment of parameters is by sequence, p1, p2,... must begin with "#", followed bythe position number of the parameter.

The exporting parameters always come at the end of the statement.

3. NO FLUSH.

5) FREE OBJECT

This will releases the storage space required for the object obj. The object can then no longer be processed.  

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.  

STEP 1:

First step is to create OLE object for excel application.  

CREATE OBJECT gh_excel 'EXCEL.APPLICATION'.  

 gh_excel is the object of type ole2_object.

STEP 2:

Now different properties of the excel object is to be set as per our requirement.

SET PROPERTY OF gh_excel ‘visible’ = 0.

 The excel sheet is not visible to the user while data transfer.

CALL METHOD OF gh_excel 'Workbooks'= gh_mapl.  

This will call the method of excel ‘Workbooks’ to get the control to object gh_mapl.

gh_mapl is the object of type ole2_object.

  CALL METHOD OF gh_mapl 'Add'= gh_map.                   

It will create a new workbook.  

Now create active sheets of excel.

 CALL METHOD OF application 'Worksheets' = sheet

                               EXPORTING #1 = 1.

 CALL METHOD OF sheet 'Activate'.       “Activate the sheet.  

 SET PROPERTY OF sheet 'Name' = 'sheet_name'.  

  Sheet is the object of type ole2_object.
 
Similarly you can create many number of sheets in excel.
 
STEP 3:

Now fill the data part into excel.

We will first fill the header part in excel.

PERFORM fill_cell USING 1 7 1 ‘MONTHLY_BILL’.                   

It will print “Monthly_Bill” in the 7th column of the 1st row of excel.

Here we had used the form “FILL_CELL” for filling the data into the cells.

FORM fill_cell USING    i j bold val.                   “For filling data in excel.

CALL METHOD OF gh_excel ‘CELLS’= gh_zl  NO FLUSH 

      EXPORTING #1 = i #2 = j.                             “Transferring data to row = i and    column   = j.

  PERFORM err_handle.                                    “For handling automation error.

  SET PROPERTY OF gh_zl ‘VALUE’ = val no flush.

  GET PROPERTY OF gh_zl ‘FONT’ = gh_f no flush. 

  SET PROPERTY OF gh_f ‘BOLD’ =  bold no flush. 

ENDFORM.                    " fill_cell

Here    i = excel row number.

J = 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 print .

Similarly fill all the header details in excel.

i.e.

PERFORM fill_cell USING 2 1 1 ‘VODAPHONE’.         “Transferring data to 2nd row and 1st column.

PERFORM fill_cell USING 2 2 1 ‘COMPANY’.                “Transferring data to 2nd row and 2nd column.

PERFORM fill_cell USING 3 2 1 WA_OUTPUT-PEIOD. “Transferring data to 3rd row and 2nd  column.

PERFORM fill_cell USING 4 1 1 ‘S. No’.                       “Transferring data to 4th row and 1st column.

PERFORM fill_cell USING 4 2 1 ‘Customer Name’.  “Transferring data to 4th row and 2nd column.

PERFORM fill_cell USING 4 3 1 ‘Duration’.           “Transferring data to 4th row and 3rd column.

PERFORM fill_cell USING 4 4 1 ‘Cost’.    “Transferring data to 4th row and 4th column.

PERFORM 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.

Click here to continue...

Please send us your feedback/suggestions at webmaster@SAPTechnical.COM 

HomeContribute About Us Privacy Terms Of Use • Disclaimer • SafeCompanies: Advertise on SAPTechnical.COM | Post JobContact Us  

Graphic Design by Round the Bend Wizards

footer image footer image