05-27-2008 4:47 PM
I am using the type pools OLE2 to put colors in the Excel sheet.
What is the property to fill color in the Excel sheet cell.
Also I need to merge cells in the excel sheet.
Can some one send me some sample code if you have?
Please help.Its urgent.
05-27-2008 6:03 PM
Hi Kangana,
Check this .
SET_COLOR
Sets the font and background colors for a particular area. The values come from the color
palette in Excel, not RGB values, even if the spreadsheet in question only accepts RGB values.
CALL METHOD spreadsheet->set_color
EXPORTING rangename = rangename
front = front
back = back
no_flush = no_flush
IMPORTING error = error
retcode = retcode.
Check this link below : [OLE Objects|http://help.sap.com/printdocu/core/Print46c/en/data/pdf/BCCIOFFI/BCCIOFFI.pdf]
05-27-2008 7:59 PM
hi rowal,
Hi Ashutosh...Use the below code i think it will be helpful
This program demonstrates how to send abap data to excel sheet
using OLE automation
include ole2incl.
*handles for OLE object
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
data: h type i.
types: begin of t_bkpf,
bukrs type bkpf-bukrs,
belnr type bkpf-belnr,
gjahr type bkpf-gjahr,
blart type bkpf-blart,
budat type bkpf-budat,
end of t_bkpf.
data: it_bkpf type standard table of t_bkpf,
wa_bkpf type t_bkpf.
********start-of-selection*******************
start-of-selection.
select bukrs
belnr
gjahr
blart
budat into table it_bkpf
from bkpf
up to 10 rows.
call function 'SAPGUI_PROGRESS_INDICATOR'
exporting
PERCENTAGE = 0
text = text-001
exceptions
others = 1.
Start excel
create object h_excel 'EXCEL.APPLICATION'.
perform err_hdl.
set property of h_excel 'Visible' = 1.
perform err_hdl.
call function 'SAPGUI_PROGRESS_INDICATOR'
exporting
PERCENTAGE = 0
text = text-002
exceptions
others = 1.
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.
call function 'SAPGUI_PROGRESS_INDICATOR'
exporting
PERCENTAGE = 0
text = text-003
exceptions
others = 1.
Output Column Headings to active excel sheet
perform fill_cell using 1 1 1 text-004.
perform fill_cell using 1 2 1 text-005.
perform fill_cell using 1 3 1 text-006.
perform fill_cell using 1 4 1 text-007.
perform fill_cell using 1 5 1 text-008.
Copy internal table to excel sheet
loop at it_bkpf into wa_bkpf.
h = sy-tabix + 1.
perform fill_cell1 using h 1 0 wa_bkpf-bukrs.
perform fill_cell2 using h 2 0 wa_bkpf-belnr.
perform fill_cell1 using h 3 0 wa_bkpf-gjahr.
perform fill_cell1 using h 4 0 wa_bkpf-blart.
perform fill_cell1 using h 5 0 wa_bkpf-budat.
endloop.
disconnect from Excel
free object h_excel.
perform err_hdl.
&----
*& Form err_hdl
&----
text
-
--> p1 text
<-- p2 text
-
form err_hdl .
if sy-subrc <> 0.
write: / 'Error in OLE Automation'.
stop.
endif.
endform. " err_hdl
&----
*& Form fill_cell
&----
text
-
-->P_1 text
-->P_1 text
-->P_1 text
-->P_TEXT_004 text
-
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.
set property of h_f 'colorindex' = 10.
endform. " fill_cell
&----
*& Form fill_cell1
&----
text
-
-->P_H text
-->P_1 text
-->P_0 text
-->P_WA_BKPF_BUKRS text
-
form fill_cell1 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. " fill_cell1
&----
*& Form fill_cell2
&----
text
-
-->P_H text
-->P_2 text
-->P_0 text
-->P_WA_BKPF_BELNR text
-
form fill_cell2 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.
set property of h_f 'colorindex' = 13.
endform. " fill_cell2
hope the code will help you to solve the problem,
reward points if usefull,
Thanks ,
kalyan.
07-23-2008 1:08 PM
Hi kalyan , found your solution very useful.
but i have one more issue.
for example:
In your output i need to get the first row in complete yellow.ie the back ground should be in yellow and text should be in black..
PLz post me the answers..i have an urgent requirement.
07-23-2008 1:15 PM
hiii
you can refer to following link for colours in EXCEL
http://www.sap-img.com/abap/download-to-excel-with-format-border-color-cell-etc.htm
regards
twinkal
07-23-2008 1:24 PM
hiii
please refer to following code.execute this program first and check with output that it satisfies with your requirement or not as it is having background colour as different and font in different colour.
http://sap.ittoolbox.com/code/archives.asp?d=3027&a=s&i=10
regards
twinkal
01-20-2014 9:01 AM