Downloading report output to Excel using OLE

...Previous

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 4th column

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

Set last 3rd row 4th 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.

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