Skip to end of metadata
Go to start of metadata
Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'
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                                       *
*----------------------------------------------------------------------*
FORM err_hdl.
  IF sy-subrc <> 0.
    WRITE: / 'OLE-Automation Error:'(010), sy-subrc.
    STOP.
  ENDIF.
ENDFORM.                    " ERR_HDL
  • No labels

1 Comment

  1. Thanks Venkat Appikonda.Very good document and very nice.