Downloading report output into Excel using OLE

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 7^th column of the 1^st 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 2^nd row and 1^st column.

PERFORM fill_cell USING 2 2 1 ‘COMPANY’. “Transferring data to 2^nd row and 2^nd 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 4^th 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.

STEP 4:

In this scenario we want some columns to be merged in our excel output.

i.e. (column from A1 to E1, column from B1 to E1, etc..)

Now, for this first we select the range of cells. And then merge the range of cells.

CALL METHOD OF gh_excel ‘RANGE’ = gh_rang NO FLUSH

EXPORTING #1 = ‘A1’

#2 = ‘E1’

CALL METHOD OF gh_range ‘SELECT’ NO FLUSH.

SET PROPERTY OF gh_range ‘MERGE’ = 1 no flush.

STEP 5:

Now format the excel according to the requirement.

· To set the width of the column according to the text.

CALL METHOD OF application 'COLUMNS' = COLUMN. CALL METHOD OF COLUMN 'AUTOFIT'. 

 To delete some line from the excel, then can call this method,

CALL METHOD OF gh_excel 'ROWS' = row                          Exporting #1 = 1. CALL METHOD OF row 'DELETE'.

Free object row.

· To change the format of some cells in excels.

For doing this first set the ranges of the cell we want to format and then change the format of these cells.

For e.g. this will set last 1st row 4^th column

CALL METHOD OF gh_excel 'Cells' = wf_cell_from1
EXPORTING
#1 =1
#2 = 4.

Set last 3^rd row 4^th column.

CALL METHOD OF gh_excel 'Cells' = wf_cell_to1
EXPORTING
#1 = 3
#2 = 4.

Set the range here,
CALL METHOD OF wf_excel 'Range' = wf_cell1
EXPORTING
#1 = wf_cell_from1
#2 = wf_cell_to1.

Now Format the range of cells here
SET PROPERTY OF wf_cell1 'NumberFormat' = ’@’. ”Here we are setting to this format.

· To have grid lines in our excel output.

For this first set the ranges of the column.

CALL METHOD OF gh_excel ‘RANGE’ = gh_range NO FLUSH

EXPORTING #1 = ‘A1’ “starting from column A

#2 = ‘M’. “up to column M

Now set the style of all the border position.

For Left edge border, set the line style to 1.

CALL METHOD OF gh_range ‘Borders’ = gh_borders NO FLUSH

EXPORTING #1 = 7.

SET PROPERTY OF gh_borders 'LineStyle'= 1.

Similarly we can call this method for other positions.

For right edge border, export with 10.

For top edge border, export with 8.

For bottom edge border, export with 9.

For inside vertical border, export with 11.

For inside horizontal border, export with 12.

This will set the grid lines in excel like this,

Suppose we want to change the color and width of any particular column.

CALL METHOD OF gh_excel ‘Cells’ = e_cell

EXPORTING

#1 = 1

#2 = 3

GET PROPERTY OF e_cell 'Interior' = e_color.

SET PROPERTY OF e_color 'ColorIndex' = 35.

SET PROPERTY OF e_cell 'ColumnWidth' = 20.

Codes for different colors.

STEP 6:

At last free all the reserved OLE objects.

 i.e. Free object column.        Free object gh_excel.   Now Save excel spreadsheet to particular filename and quit the excel application.

  CALL METHOD OF sheet 'SaveAs'

               EXPORTING #1 = 'c:\your_excel.xls'     "name of excel

                                   #2 = 1.                                   "file format               

CALL METHOD OF sheet 'QUIT'.              

This closes the Excel document

SET PROPERTY OF application 'visible' = 0. 

It will close visible excel window and data is lost if not saved.

Now our data get saved into excel naming ‘your_excel’.

For the excel downloaded,  
click here.