09-02-2014 8:31 AM
Hi,
I need to send Excel as attachment and some particular columns should be coloured.
I am sending Excel attachment using FM SO_DOCUMENT_SEND_API1 when report executed in foreground.
Please help how to make columns coloured.
Thanks,
Phani
09-02-2014 9:00 AM
09-02-2014 9:02 AM
Not sure if you know of abap2xlsx, however I would recommend this as the easiest/best route, here a link thereto:
http://wiki.scn.sap.com/wiki/display/ABAP/abap2xlsx
Hope this helps.
09-02-2014 9:54 AM
hi,
This report demonstrates how to send some ABAP data to an EXCEL sheet using OLE automation by this it makes EXCEL columns colorful.
TYPE-POOLS OLE2 .
* 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
Regards,
Mukesh