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
Serkan AKKAVAK
Computer Engineer
ABAP Developer & SAP MM SD Consultant
Contact : serkurumsal@yandex.com
Social Plugin