Performance in OLE concept

[Former Member]

Hi All,

I am using ole concept to download data from internal table to excel because i want to fill few records with colors and few with bold etc.

But this ole concept is downloading to excel line by line and taking much time.

How can i improve the performance in this concept or is there any other concept please let me know.

i am using fallowng logic to download to excel.

******************************************************

*Generating the Excel report in the foreground

******************************************************

• start Excel

create object h_excel 'EXCEL.APPLICATION'.

set property of h_excel 'Visible' = 1.

• get list of workbooks, initially empty

call method of h_excel 'Workbooks' = h_mapl.

perform err_hdl.

• add a new workbook

call method of h_mapl 'Add' = h_map.

perform err_hdl.

• output column headings to active Excel sheet

perform fill_cell using 1 1 1 000 'Job Name'(001).

perform fill_cell using 1 2 1 000 'Variant'(002).

perform fill_cell using 1 3 1 000 'Description'(003).

perform fill_cell using 1 4 1 000 'Run Date'(004).

perform fill_cell using 1 5 1 000 'Run Time'(005).

perform fill_cell using 1 6 1 000 'Duration'(006).

perform fill_cell using 1 7 1 000 'Spool'(007).

perform fill_cell using 1 8 1 000 'Records Upl'(008).

perform fill_cell using 1 9 1 000 'Error'(009).

perform fill_cell using 1 10 1 000 'Total'(010).

perform fill_cell using 1 11 1 000 'Action'(011).

perform fill_cell using 1 12 1 000 'ReRun'(012).

loop at t_final.

• copy datato active EXCEL sheet

h = sy-tabix + 1.

perform fill_cell using h 1 0 000 t_final-jobname.

perform fill_cell using h 2 0 000 t_final-variant.

perform fill_cell using h 3 0 000 t_final-description.

perform fill_cell using h 4 0 000 t_final-strtdate.

perform fill_cell using h 5 0 000 t_final-strttime.

perform fill_cell using h 6 0 000 t_final-duration.

perform fill_cell using h 7 0 000 t_final-listident.

perform fill_cell using h 8 0 000 t_final-rec_upl.

perform fill_cell using h 9 0 000 t_final-rec_err.

perform fill_cell using h 10 0 000 t_final-rec_tot.

if t_final-comment eq 'job did not run'.

perform fill_cell using h 11 0 200 t_final-comment.

elseif t_final-rec_err eq 0.

perform fill_cell using h 11 0 000 t_final-comment.

else.

perform fill_cell using h 11 0 200 t_final-comment.

endif.

perform fill_cell using h 12 0 000 t_final-rerun.

endloop.

• CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL.

call method of h_excel 'Worksheets' = h_mapl." EXPORTIN G #1 = 2.

perform err_hdl.

• add a new workbook

call method of h_mapl 'Add' = h_map exporting #1 = 2.

perform err_hdl.

• tell user what is going on

set property of h_map 'NAME' = 'COPY'.

loop at t_final.

• copy flights to active EXCEL sheet

h = sy-tabix + 1.

perform fill_cell using h 1 0 000 t_final-jobname.

perform fill_cell using h 2 0 000 t_final-variant.

perform fill_cell using h 3 0 000 t_final-description.

perform fill_cell using h 4 0 000 t_final-strtdate.

perform fill_cell using h 5 0 000 t_final-strttime.

perform fill_cell using h 6 0 000 t_final-duration.

perform fill_cell using h 7 0 000 t_final-listident.

perform fill_cell using h 8 0 000 t_final-rec_upl.

perform fill_cell using h 9 0 000 t_final-rec_err.

perform fill_cell using h 10 0 000 t_final-rec_tot.

if t_final-comment eq 'job did not run'.

perform fill_cell using h 11 0 200 t_final-comment.

elseif t_final-rec_err eq 0.

perform fill_cell using h 11 0 000 t_final-comment.

else.

perform fill_cell using h 11 0 200 t_final-comment.

endif.

perform fill_cell using h 12 0 000 t_final-rerun.

endloop.

free object h_excel.

perform err_hdl.

&----

---

*& Form ERR_HDL

&----

---

• outputs OLE error if any *

----

---

form err_hdl.

if sy-subrc <> 0.

write: / 'Batch Job Automation Carried Out Succesfully'.

stop.

endif.

endform. " ERR_HDL .

----

---

• FORM FILL_CELL *

----

---

• sets cell at coordinates i,j to value val boldtype bold *

----

---

form fill_cell using i j bold col val.

call method of h_excel 'Cells' = h_zl exporting #1 = i #2 = j.

perform err_hdl.

set property of h_zl 'Value' = val .

perform err_hdl.

get property of h_zl 'Font' = h_f.

perform err_hdl.

set property of h_f 'Bold' = bold .

perform err_hdl.

set property of h_f 'Color' = col.

perform err_hdl.

endform. "FILL_CELL

data: h_excel type ole2_object, " Excel object

h_mapl type ole2_object, " list of workbooks

h_map type ole2_object, " workbook

[Former Member]

Hi subhani,

1. Yes, you are right. OLE will always be slow.

Moreover, if you put data in excel (thru OLE),

row by row, then it will be more slow.

2. Another technique is to use the FM - XXL_FULL_API

Sample program is XXLTTEST

3. It gives headings in yellow coour etc.

U can check the sample program. It will serve your purpose 85%.

Rest 15% options may be there in the FM itself. (I have not explored this FM)

regards,

amit m.

[former_member189059]

Try this code, it uses OLE concept by sending data to the clipboard and then pasting it in excel

*&---------------------------------------------------------------------*
*& Report  ZKRIS_OLE4_SDN_DL_CLIPBOARD
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

report  zkris_ole4_sdn_dl_clipboard.

include ole2incl.
.
data: w_cell1 type ole2_object,
w_cell2 type ole2_object.

*--- Ole data Declarations
data: h_excel type ole2_object, " Excel object
h_mapl type ole2_object, " list of workbooks
h_map type ole2_object, " workbook
h_zl type ole2_object, " cell
h_f type ole2_object, " font
gs_interior type ole2_object, " Pattern
worksheet type ole2_object,
h_cell type ole2_object,
h_cell1 type ole2_object,
range type ole2_object,
h_sheet2 type ole2_object,
h_sheet3 type ole2_object,
gs_font type ole2_object,
flg_stop(1) type c.

*********************************************************************
** Internal table Declaration
*********************************************************************

data: begin of t_excel occurs 0,

vkorg(20) type c, "Sales Org

vbtyp(20) type c, "Document Category

auart(20) type c, "Document Type

ernam(20) type c, "Created By

vbeln(20) type c, "Document Number

posnr(20) type c, "Item Number

erdat(20) type c, "Created Date

vdatu(20) type c, "Header Requested Delivery Date

reqdat(20) type c, "Request date

condat(20) type c, "Confirm date

lifsk(20) type c, "Header Block

txt30(30) type c, "Order User Status Description

lifsp(20) type c, "Line Block

dispo(20) type c, "MRP Controller

dsnam(20) type c, "MRP Controller Description

vmsta(20) type c, "Material Sales Status

kunnr(20) type c, "Sold To

cname(35) type c, "Sold To Name

regio(20) type c, "State

cufd(10) type c, "CUD

bstnk(20) type c, "PO#

bsark(20) type c, "Ordering Method

matnr(20) type c, "Material

maktx(35) type c, "Material Description

t200(20) type c,                                            "T200

vtext(20) type c, "T200 Description

matkl(20) type c, "Material Group

zzbomind(7) type c, "BOM Indicator

ostat(20) type c, "Order Status

cmgst(20) type c, "CRD

inco1(20) type c, "Incoterms

oqty(20) type c, "Order Quantity

pqty(20) type c, "Open Quantity

unit(20) type c, "UOM

onet(20) type c, "Order Value

pnet(20) type c, "Open Value

curr(20) type c, "Currency key

so_bezei like tvkbt-bezei,"Sales Office

sg_bezei like tvgrt-bezei,"Sales Group

bname(20) type c, "Ordering Party

contact(20) type c, "Contact Name

telf1(20) type c, "Contact telf1

reqqty(20) type c, "Item Request qty

reqval(20) type c, "Item Request value

conqty(20) type c, "Item Confirm qty

conval(20) type c, "Item Confirm value

zzrev(02) type c, "Revenue recognition acceptance

bezei(20) type c, "Revenue recognition text

vgbel(20) type c, "Reference Order for RETURNS

0008text(255) type c, "Internal Order Comment Text

end of t_excel.

data: t_excel_bckord like t_excel occurs 0 with header line,
t_excel_bcklog like t_excel occurs 0 with header line,
t_excel_blkord like t_excel occurs 0 with header line.

types: data1(1500) type c,
ty type table of data1.

data: it type ty with header line,
it_2 type ty with header line,
it_3 type ty with header line,

rec type sy-tfill,
deli(1) type c,
l_amt(18) type c.
data: begin of hex,
tab type x,
end of hex.

field-symbols: <fs> .

constants cns_09(2) type n value 09.

assign deli to <fs> type 'X'.
hex-tab = cns_09.
<fs> = hex-tab.

data gv_sheet_name(20) type c .

*----------------------------------------------------------------------*
* M A C R O Declaration
*----------------------------------------------------------------------*
define ole_check_error.
  if &1 ne 0.
    message e001(zz) with &1.
    exit.
  endif.
end-of-definition.

t_excel_bckord-vkorg = 'ABC'.
t_excel_bckord-vbtyp = 'DEF'.
t_excel_bckord-auart = 'GHI'.
t_excel_bckord-ernam = 'JKL'.
t_excel_bckord-vbeln = 'MNO'.
t_excel_bckord-0008text = 'XYZ'.
append t_excel_bckord.

t_excel_bckord-vkorg = 'ABC1'.
t_excel_bckord-vbtyp = 'DEF1'.
t_excel_bckord-auart = 'GHI1'.
t_excel_bckord-ernam = 'JKL1'.
t_excel_bckord-vbeln = 'MNO1'.
t_excel_bckord-0008text = 'XYZ1'.
append t_excel_bckord.

t_excel_bckord-vkorg = 'ABC2'.
t_excel_bckord-vbtyp = 'DEF2'.
t_excel_bckord-auart = 'GHI2'.
t_excel_bckord-ernam = 'JKL2'.
t_excel_bckord-vbeln = 'MNO2'.
t_excel_bckord-0008text = 'XYZ2'.
append t_excel_bckord.



t_excel_bcklog-vkorg = 'ABC'.
t_excel_bcklog-vbtyp = 'DEF'.
t_excel_bcklog-auart = 'GHI'.
t_excel_bcklog-ernam = 'JKL'.
t_excel_bcklog-vbeln = 'MNO'.
t_excel_bcklog-0008text = 'XYZ'.
append t_excel_bcklog.

t_excel_bcklog-vkorg = 'ABC1'.
t_excel_bcklog-vbtyp = 'DEF1'.
t_excel_bcklog-auart = 'GHI1'.
t_excel_bcklog-ernam = 'JKL1'.
t_excel_bcklog-vbeln = 'MNO1'.
t_excel_bcklog-0008text = 'XYZ1'.
append t_excel_bcklog.

t_excel_bcklog-vkorg = 'ABC2'.
t_excel_bcklog-vbtyp = 'DEF2'.
t_excel_bcklog-auart = 'GHI2'.
t_excel_bcklog-ernam = 'JKL2'.
t_excel_bcklog-vbeln = 'MNO2'.
t_excel_bcklog-0008text = 'XYZ2'.
append t_excel_bcklog.

t_excel_bcklog-vkorg = 'ABC3'.
t_excel_bcklog-vbtyp = 'DEF3'..
t_excel_bcklog-auart = 'GHI3'.
t_excel_bcklog-ernam = 'JKL3'.
t_excel_bcklog-vbeln = 'MNO3'.
t_excel_bcklog-0008text = 'XYZ3'.
append t_excel_bcklog.


t_excel_blkord-vkorg = 'ABC'.
t_excel_blkord-vbtyp = 'DEF'.
t_excel_blkord-auart = 'GHI'.
t_excel_blkord-ernam = 'JKL'.
t_excel_blkord-vbeln = 'MNO'.
t_excel_blkord-0008text = 'XYZ'.
append t_excel_blkord.

t_excel_blkord-vkorg = 'ABC1'.
t_excel_blkord-vbtyp = 'DEF1'.
t_excel_blkord-auart = 'GHI1'.
t_excel_blkord-ernam = 'JKL1'.
t_excel_blkord-vbeln = 'MNO1'.
t_excel_blkord-0008text = 'XYZ1'.
append t_excel_blkord.

t_excel_blkord-vkorg = 'ABC2'.
t_excel_blkord-vbtyp = 'DEF2'.
t_excel_blkord-auart = 'GHI2'.
t_excel_blkord-ernam = 'JKL2'.
t_excel_blkord-vbeln = 'MNO2'.
t_excel_blkord-0008text = 'XYZ2'.
append t_excel_blkord.

t_excel_blkord-vkorg = 'ABC3'.
t_excel_blkord-vbtyp = 'DEF3'..
t_excel_blkord-auart = 'GHI3'.
t_excel_blkord-ernam = 'JKL3'.
t_excel_blkord-vbeln = 'MNO3'.
t_excel_blkord-0008text = 'XYZ3'.
append t_excel_blkord.

t_excel_blkord-vkorg = 'ABC4'.
t_excel_blkord-vbtyp = 'DEF4'..
t_excel_blkord-auart = 'GHI4'.
t_excel_blkord-ernam = 'JKL4'.
t_excel_blkord-vbeln = 'MNO4'.
t_excel_blkord-0008text = 'XYZ4'.
append t_excel_blkord.


loop at t_excel_bckord.
  concatenate
  t_excel_bckord-vkorg

  t_excel_bckord-vbtyp

  t_excel_bckord-auart

  t_excel_bckord-ernam

  t_excel_bckord-vbeln

  t_excel_bckord-posnr

  t_excel_bckord-erdat

  t_excel_bckord-vdatu

  t_excel_bckord-reqdat

  t_excel_bckord-condat

  t_excel_bckord-lifsk

  t_excel_bckord-txt30

  t_excel_bckord-lifsp

  t_excel_bckord-dispo

  t_excel_bckord-dsnam

  t_excel_bckord-vmsta

  t_excel_bckord-kunnr

  t_excel_bckord-cname

  t_excel_bckord-regio

  t_excel_bckord-cufd

  t_excel_bckord-bstnk

  t_excel_bckord-bsark

  t_excel_bckord-matnr

  t_excel_bckord-maktx

  t_excel_bckord-t200

  t_excel_bckord-vtext

  t_excel_bckord-matkl

  t_excel_bckord-zzbomind

  t_excel_bckord-ostat

  t_excel_bckord-cmgst

  t_excel_bckord-inco1

  t_excel_bckord-oqty

  t_excel_bckord-pqty

  t_excel_bckord-unit

  t_excel_bckord-onet

  t_excel_bckord-pnet

  t_excel_bckord-curr

  t_excel_bckord-so_bezei

  t_excel_bckord-sg_bezei

  t_excel_bckord-bname

  t_excel_bckord-contact

  t_excel_bckord-telf1

  t_excel_bckord-reqqty

  t_excel_bckord-reqval

  t_excel_bckord-conqty

  t_excel_bckord-conval

  t_excel_bckord-zzrev

  t_excel_bckord-bezei

  t_excel_bckord-vgbel

  t_excel_bckord-0008text

  into it
  separated by deli.

  append it.
  clear it.
endloop.


loop at t_excel_bcklog.
  concatenate
  t_excel_bcklog-vkorg

  t_excel_bcklog-vbtyp

  t_excel_bcklog-auart

  t_excel_bcklog-ernam

  t_excel_bcklog-vbeln

  t_excel_bcklog-posnr

  t_excel_bcklog-erdat

  t_excel_bcklog-vdatu

  t_excel_bcklog-reqdat

  t_excel_bcklog-condat

  t_excel_bcklog-lifsk

  t_excel_bcklog-txt30

  t_excel_bcklog-lifsp

  t_excel_bcklog-dispo

  t_excel_bcklog-dsnam

  t_excel_bcklog-vmsta

  t_excel_bcklog-kunnr

  t_excel_bcklog-cname

  t_excel_bcklog-regio

  t_excel_bcklog-cufd

  t_excel_bcklog-bstnk

  t_excel_bcklog-bsark

  t_excel_bcklog-matnr

  t_excel_bcklog-maktx

  t_excel_bcklog-t200

  t_excel_bcklog-vtext

  t_excel_bcklog-matkl

  t_excel_bcklog-zzbomind

  t_excel_bcklog-ostat

  t_excel_bcklog-cmgst

  t_excel_bcklog-inco1

  t_excel_bcklog-oqty

  t_excel_bcklog-pqty

  t_excel_bcklog-unit

  t_excel_bcklog-onet

  t_excel_bcklog-pnet

  t_excel_bcklog-curr

  t_excel_bcklog-so_bezei

  t_excel_bcklog-sg_bezei

  t_excel_bcklog-bname

  t_excel_bcklog-contact

  t_excel_bcklog-telf1

  t_excel_bcklog-reqqty

  t_excel_bcklog-reqval

  t_excel_bcklog-conqty

  t_excel_bcklog-conval

  t_excel_bcklog-zzrev

  t_excel_bcklog-bezei

  t_excel_bcklog-vgbel

  t_excel_bcklog-0008text

  into it_2
  separated by deli.

  append it_2.
  clear it_2.
endloop.


loop at t_excel_blkord.
  concatenate
  t_excel_blkord-vkorg

  t_excel_blkord-vbtyp

  t_excel_blkord-auart

  t_excel_blkord-ernam

  t_excel_blkord-vbeln

  t_excel_blkord-posnr

  t_excel_blkord-erdat

  t_excel_blkord-vdatu

  t_excel_blkord-reqdat

  t_excel_blkord-condat

  t_excel_blkord-lifsk

  t_excel_blkord-txt30

  t_excel_blkord-lifsp

  t_excel_blkord-dispo

  t_excel_blkord-dsnam

  t_excel_blkord-vmsta

  t_excel_blkord-kunnr

  t_excel_blkord-cname

  t_excel_blkord-regio

  t_excel_blkord-cufd

  t_excel_blkord-bstnk

  t_excel_blkord-bsark

  t_excel_blkord-matnr

  t_excel_blkord-maktx

  t_excel_blkord-t200

  t_excel_blkord-vtext

  t_excel_blkord-matkl

  t_excel_blkord-zzbomind

  t_excel_blkord-ostat

  t_excel_blkord-cmgst

  t_excel_blkord-inco1

  t_excel_blkord-oqty

  t_excel_blkord-pqty

  t_excel_blkord-unit

  t_excel_blkord-onet

  t_excel_blkord-pnet

  t_excel_blkord-curr

  t_excel_blkord-so_bezei

  t_excel_blkord-sg_bezei

  t_excel_blkord-bname

  t_excel_blkord-contact

  t_excel_blkord-telf1

  t_excel_blkord-reqqty

  t_excel_blkord-reqval

  t_excel_blkord-conqty

  t_excel_blkord-conval

  t_excel_blkord-zzrev

  t_excel_blkord-bezei

  t_excel_blkord-vgbel

  t_excel_blkord-0008text

  into it_3
  separated by deli.

  append it_3.
  clear it_3.
endloop.



if h_excel-header = space or h_excel-handle = -1.
* start Excel
  create object h_excel 'EXCEL.APPLICATION'.
endif.

* PERFORM err_hdl.

*--- get list of workbooks, initially empty
call method of h_excel 'Workbooks' = h_mapl.
* PERFORM err_hdl.
set property of h_excel 'Visible' = 1.

* add a new workbook
call method of h_mapl 'Add' = h_map.
* PERFORM err_hdl.
*GV_SHEET_NAME = '1st SHEET'.

gv_sheet_name = 'Back Orders'.



get property of h_excel 'ACTIVESHEET' = worksheet.
set property of worksheet 'Name' = gv_sheet_name .


*--Formatting the area of additional data 1 and doing the BOLD
call method of h_excel 'Cells' = w_cell1
  exporting
    #1 = 1
    #2 = 1.
call method of h_excel 'Cells' = w_cell2
  exporting
    #1 = 1
    #2 = 50.
call method of h_excel 'Range' = h_cell
  exporting
    #1 = w_cell1
    #2 = w_cell2.
*CALL METHOD OF gs_cells 'Select' .
get property of h_cell 'Font' = gs_font .
set property of gs_font 'Bold' = 1 .


data l_rc type i.
call method cl_gui_frontend_services=>clipboard_export
  importing
    data                 = it[]
  changing
    rc                   = l_rc
  exceptions
    cntl_error           = 1
    error_no_gui         = 2
    not_supported_by_gui = 3
    others               = 4.

*CALL METHOD OF h_excel 'Cells' = w_cell1
*  EXPORTING
*    #1 = 1
*    #2 = 1.
*
*CALL METHOD OF h_excel 'Cells' = w_cell2
*  EXPORTING
*    #1 = 1
*    #2 = 1.
** PERFORM err_hdl.

*CALL METHOD OF h_excel 'Range' = range
*  EXPORTING
*    #1 = w_cell1
*    #2 = w_cell2.

*CALL METHOD OF range 'Select'.
* PERFORM err_hdl.
call method of worksheet 'Paste'.
* PERFORM err_hdl.

* CALL METHOD OF h_excel 'QUIT'.

*GV_SHEET_NAME = '2ND SHEET'.
gv_sheet_name = 'Backlog'.


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' = worksheet.


*--Formatting the area of additional data 1 and doing the BOLD
call method of h_excel 'Cells' = w_cell1
  exporting
    #1 = 1
    #2 = 1.
call method of h_excel 'Cells' = w_cell2
  exporting
    #1 = 1
    #2 = 50.
call method of h_excel 'Range' = h_cell
  exporting
    #1 = w_cell1
    #2 = w_cell2.
get property of h_cell 'Font' = gs_font .
set property of gs_font 'Bold' = 1 .

call method cl_gui_frontend_services=>clipboard_export
  importing
    data                 = it_2[]
  changing
    rc                   = l_rc
  exceptions
    cntl_error           = 1
    error_no_gui         = 2
    not_supported_by_gui = 3
    others               = 4.

call method of h_excel 'Cells' = w_cell1
  exporting
    #1 = 1
    #2 = 1.

call method of h_excel 'Cells' = w_cell2
  exporting
    #1 = 1
    #2 = 1.
* PERFORM err_hdl.

call method of h_excel 'Range' = range
  exporting
    #1 = w_cell1
    #2 = w_cell2.

call method of range 'Select'.
* PERFORM err_hdl.
call method of worksheet 'Paste'.



*GV_SHEET_NAME = '3rd SHEET'.
gv_sheet_name = 'Blocked Orders'.

get property of h_excel 'Sheets' = h_sheet3 .

call method of h_sheet3 'Add' = h_map.
set property of h_map 'Name' = gv_sheet_name .
get property of h_excel 'ACTIVESHEET' = worksheet.


*--Formatting the area of additional data 1 and doing the BOLD
call method of h_excel 'Cells' = w_cell1
  exporting
    #1 = 1
    #2 = 1.
call method of h_excel 'Cells' = w_cell2
  exporting
    #1 = 1
    #2 = 50.
call method of h_excel 'Range' = h_cell
  exporting
    #1 = w_cell1
    #2 = w_cell2.
get property of h_cell 'Font' = gs_font .
set property of gs_font 'Bold' = 1 .

call method cl_gui_frontend_services=>clipboard_export
  importing
    data                 = it_3[]
  changing
    rc                   = l_rc
  exceptions
    cntl_error           = 1
    error_no_gui         = 2
    not_supported_by_gui = 3
    others               = 4.

call method of h_excel 'Cells' = w_cell1
  exporting
    #1 = 1
    #2 = 1.

call method of h_excel 'Cells' = w_cell2
  exporting
    #1 = 1
    #2 = 1.
* PERFORM err_hdl.

call method of h_excel 'Range' = range
  exporting
    #1 = w_cell1
    #2 = w_cell2.

call method of range 'Select'.
* PERFORM err_hdl.
call method of worksheet 'Paste'.


*--- disconnect from Excel
free object h_zl.
free object h_mapl.
free object h_map.
free object h_excel.

[Former Member]

Hi Subhani,

Copy this program and run.I think this can help you out.If this works change your program accordingly.

REPORT RSDEMO01 NO STANDARD PAGE HEADING.

• this report demonstrates how to send some ABAP data to an

• EXCEL sheet using OLE automation.

INCLUDE OLE2INCL.

• handles for OLE objects

DATA: H_EXCEL TYPE OLE2_OBJECT, " Excel object

H_MAPL TYPE OLE2_OBJECT, " list of workbooks

H_MAP TYPE OLE2_OBJECT, " workbook

H_ZL TYPE OLE2_OBJECT, " cell

H_F TYPE OLE2_OBJECT. " font

TABLES: SPFLI.

DATA H TYPE I.

• table of flights

DATA: IT_SPFLI LIKE SPFLI OCCURS 10 WITH HEADER LINE.

&----

---

*& Event START-OF-SELECTION

&----

---

START-OF-SELECTION.

• read flights

SELECT * FROM SPFLI INTO TABLE IT_SPFLI UP TO 10 ROWS.

• display header

ULINE (61).

WRITE: / SY-VLINE NO-GAP,

(3) 'Flg'(001) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP,

(4) 'Nr'(002) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP,

(20) 'Von'(003) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP,

(20) 'Nach'(004) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP,

(8) 'Zeit'(005) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP.

ULINE (61).

• display flights

LOOP AT IT_SPFLI.

WRITE: / SY-VLINE NO-GAP,

IT_SPFLI-CARRID COLOR COL_KEY NO-GAP, SY-VLINE NO-GAP,

IT_SPFLI-CONNID COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP,

IT_SPFLI-CITYFROM COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP,

IT_SPFLI-CITYTO COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP,

IT_SPFLI-DEPTIME COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP.

ENDLOOP.

ULINE (61).

• tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

• PERCENTAGE = 0

TEXT = TEXT-007

EXCEPTIONS

OTHERS = 1.

• start Excel

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.

PERFORM ERR_HDL.

SET PROPERTY OF H_EXCEL 'Visible' = 1.

PERFORM ERR_HDL.

• tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

• PERCENTAGE = 0

TEXT = TEXT-008

EXCEPTIONS

OTHERS = 1.

• get list of workbooks, initially empty

CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL.

PERFORM ERR_HDL.

• add a new workbook

CALL METHOD OF H_MAPL 'Add' = H_MAP.

PERFORM ERR_HDL.

• tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

• PERCENTAGE = 0

TEXT = TEXT-009

EXCEPTIONS

OTHERS = 1.

• output column headings to active Excel sheet

PERFORM FILL_CELL USING 1 1 1 'Flug'(001).

PERFORM FILL_CELL USING 1 2 1 'Nr'(002).

PERFORM FILL_CELL USING 1 3 1 'Von'(003).

PERFORM FILL_CELL USING 1 4 1 'Nach'(004).

PERFORM FILL_CELL USING 1 5 1 'Zeit'(005).

LOOP AT IT_SPFLI.

• copy flights to active EXCEL sheet

H = SY-TABIX + 1.

PERFORM FILL_CELL USING H 1 0 IT_SPFLI-CARRID.

PERFORM FILL_CELL USING H 2 0 IT_SPFLI-CONNID.

PERFORM FILL_CELL USING H 3 0 IT_SPFLI-CITYFROM.

PERFORM FILL_CELL USING H 4 0 IT_SPFLI-CITYTO.

PERFORM FILL_CELL USING H 5 0 IT_SPFLI-DEPTIME.

ENDLOOP.

• disconnect from Excel

FREE OBJECT H_EXCEL.

PERFORM ERR_HDL.

----

---

• FORM FILL_CELL *

----

---

• sets cell at coordinates i,j to value val boldtype bold *

----

---

FORM FILL_CELL USING I J BOLD VAL.

CALL METHOD OF H_EXCEL 'Cells' = H_ZL EXPORTING #1 = I #2 = J.

PERFORM ERR_HDL.

SET PROPERTY OF H_ZL 'Value' = VAL .

PERFORM ERR_HDL.

GET PROPERTY OF H_ZL 'Font' = H_F.

PERFORM ERR_HDL.

SET PROPERTY OF H_F 'Bold' = BOLD .

PERFORM ERR_HDL.

ENDFORM.

&----

---

*& Form ERR_HDL

&----

---

• outputs OLE error if any *

----

---

• --> p1 text

• <-- p2 text

----

---

FORM ERR_HDL.

IF SY-SUBRC <> 0.

WRITE: / 'Fehler bei OLE-Automation:'(010), SY-SUBRC.

STOP.

ENDIF.

ENDFORM. " ERR_HDL

Reward Points for helpful.

Regards,

Harini.S