Google Search - Blog...........

ABAP - Download ABAP Data To An Excel Sheet Using OLE Automation.

Report ZMULTICOLOR_TEST 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
h_c type ole2_object. " color

DATA: FILENAME LIKE RLGRAP-FILENAME.

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.
* 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.
* CALL METHOD OF H_EXCEL 'FILESAVEAS' EXPORTING #1 = 'c:\kis_excel.xls' .

* 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 200 'Carrier id'(001).
perform fill_cell using 1 2 1 200 'Connection id'(002).
perform fill_cell using 1 3 1 200 'City from'(003).
perform fill_cell using 1 4 1 200 'City to'(004).
perform fill_cell using 1 5 1 200 'Dep. Time'(005).
loop at it_spfli.

* copy flights to active EXCEL sheet
h = sy-tabix + 1.
if it_spfli-carrid cs 'AA'.
perform fill_cell using h 1 0 000255000 it_spfli-carrid.
elseif it_spfli-carrid cs 'AZ'.
perform fill_cell using h 1 0 168000000 it_spfli-carrid.
elseif it_spfli-carrid cs 'JL'.
perform fill_cell using h 1 0 168168000 it_spfli-carrid.
elseif it_spfli-carrid cs 'LH'.
perform fill_cell using h 1 0 111111111 it_spfli-carrid.
elseif it_spfli-carrid cs 'SQ'.
perform fill_cell using h 1 0 100100100 it_spfli-carrid.
else.
perform fill_cell using h 1 0 000145000 it_spfli-carrid.
endif.

if it_spfli-connid lt 400.
perform fill_cell using h 2 0 255000255 it_spfli-connid.
elseif it_spfli-connid lt 800.
perform fill_cell using h 2 0 077099088 it_spfli-connid.
else.
perform fill_cell using h 2 0 246156138 it_spfli-connid.
endif.

if it_spfli-cityfrom cp 'S*'.
perform fill_cell using h 3 0 155155155 it_spfli-cityfrom.
elseif it_spfli-cityfrom cp 'N*'.
perform fill_cell using h 3 0 189111222 it_spfli-cityfrom.
else.
perform fill_cell using h 3 0 111230222 it_spfli-cityfrom.
endif.

if it_spfli-cityto cp 'S*'.
perform fill_cell using h 4 0 200200200 it_spfli-cityto.
elseif it_spfli-cityto cp 'N*'.
perform fill_cell using h 4 0 000111222 it_spfli-cityto.
else.
perform fill_cell using h 4 0 130230230 it_spfli-cityto.
endif.

if it_spfli-deptime lt '020000'.
perform fill_cell using h 5 0 145145145 it_spfli-deptime.
elseif it_spfli-deptime lt '120000' .
perform fill_cell using h 5 0 015215205 it_spfli-deptime.
elseif it_spfli-deptime lt '180000' .
perform fill_cell using h 5 0 000215205 it_spfli-deptime.
else.
perform fill_cell using h 5 0 115115105 it_spfli-deptime.
endif.
endloop.


* EXCEL FILENAME
CONCATENATE SY-REPID '_' SY-DATUM+6(2) '_' SY-DATUM+4(2) '_'
SY-DATUM(4) '_' SY-UZEIT '.XLS' INTO FILENAME.

CALL METHOD OF H_MAP 'SAVEAS' EXPORTING #1 = FILENAME.


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

*&---------------------------------------------------------------------*

*& Form ERR_HDL
*&---------------------------------------------------------------------*

* outputs OLE error if any *

*----------------------------------------------------------------------*

* --> p1 text
* <-- p2 text *----------------------------------------------------------------------* form err_hdl. if sy-subrc <> 0.
write: / 'OLE-Automation Error:'(010), sy-subrc.
stop.
endif.
endform. " ERR_HDL




ALSO READ:




RETURN TO MAIN INDEX:

- Sample Programs On Uploading & Downloading Files.

- Sample Programs On HR ABAP.

- Sample Report Programs On ALV List/ Grid Display.

- Sample Programs On Selection Screen.

- Sample Programs On BDC.

.....Back To MAIN INDEX.
Tags ,

1 comment:

  1. UnknownThursday, February 19, 2009 at 10:03:00 AM GMT+5:30

    Hi Any idea as to how we format the exceel sheet for sendit it as an email attachment

    ReplyDelete

Subscribe to: Post Comments (Atom)