Download Data to an excel sheet, with colours

[Former Member]

Hi All,

I am downloading data of an internal table into an excel sheet, i want one column to have red colour. Is there any Function Module which helps in acheiving this functionality or do i need to add code in my program.

your fast help in this regard is highly appreciated.

Thanks in advance for your help.

[Former Member]

Hi,

You will need some OLE-coding to color your column.

Regards,

John.

[Former Member]

Thanks for your early reply.

But if you do not mind. can you please, place the code that is to be used for the purpose. it will be of great help to me.

Thanks in advance for your reply.

[Former Member]

Hi Raghavendra,

Below you'll find some ABAP sample code that allows you to modify colors on individual cells in an MS Excel sheet (using OLE2 coupling). Both the Cell background color as the Text color is modified (in sample cell A1).

INCLUDE ole2incl.

DATA:

h_excel TYPE ole2_object, " Master MS Excel object

h_mapl TYPE ole2_object, " Container list of workbooks

h_map TYPE ole2_object, " A specific workbook

h_sheet TYPE ole2_object, " A specific sheet

h_zl TYPE ole2_object, " A specific cell

h_f TYPE ole2_object, " Font definition object

h_i TYPE ole2_object. " Interior definition object

• MS Excel object

CREATE OBJECT h_excel 'EXCEL.APPLICATION'.

CALL METHOD OF h_excel 'Workbooks' = h_mapl.

• Add a new worksheet

CALL METHOD OF h_mapl 'Add' = h_map.

GET PROPERTY OF h_excel 'ACTIVESHEET' = h_sheet.

SET PROPERTY OF h_sheet 'NAME' = 'Color Test'.

• Position to active cell (A1)

CALL METHOD OF h_excel 'Cells' = h_zl EXPORTING #1 = 1 #2 = 1.

• Fill the cell with something

SET PROPERTY OF h_zl 'Value' = 'Content A1'.

• Modify the color

GET PROPERTY OF h_zl 'Interior' = h_i.

SET PROPERTY OF h_i 'Color' = 16711680.

• Modify the text property into Bold

GET PROPERTY OF h_zl 'Font' = h_f.

SET PROPERTY OF h_f 'Bold' = 1.

• Text color (Magenta)

SET PROPERTY OF h_f 'Color' = 16711935.

• Show MS Excel

SET PROPERTY OF h_excel 'Visible' = 1.

• Release MS Excel object (MS Excel stays alive)

FREE OBJECT h_excel.

Hope this helps you with your problem.

Regards,

Rob.

[Former Member]

Here's a chart I use that contains common fill colors. It's a little easier to use than trying to figure out that red = 16711680. They are ints, not char.

*key to bkgrnd colors

• 1 = black

• 2 = white

• 3 = red

• 4 = bright green

• 5 = bright blue

• 6 = bright yellow

• 7 = fuscia

• 8 = cyan

• 9 = dark red (brownish)

*10 = green (med-to-dark)

*11 = dark blue

*12 = dark yellow (mustard)

*13 = violet

*14 = teal

*15 = gray 25%

*16 = gray 50%

*17 = lavender

*18 = plum

*19 = light yellow

*20 = light blue

*21 = violet

*22 = med peach

*23 = med blue

*24 = light lavendr

*25 = dark blue

*26 = fuscia

*27 = bright yellow

*28 = cyan

*29 = violet

*30 = dark red

*31 = med teal

*32 = bright blue

*33 = carolina blue

*34 = light blue(pastel)

*35 = light green(pastel)

*36 = light yellow pastel)

*37 = pale blue (pastel)

*38 = rose (pastel)

[Former Member]

Hello,

i want to send the data of a report to excel. the problem is that i have header information an position information. so i have to format the excel file in abap. is there a documentation of formating excel with ole in abap ? I saw a print file using ole objects at sap, but there is no information how to format the cells in excel.

thanks in advance for answers

[Former Member]

Hello,

I figured out that there is another topic where is explained how to format the excel output by using microsoft excel library, but I dont know how to use your chart. I want a light yellow backround in my cell and I tried:

GET PROPERTY OF h_z27sl 'Interior' = h_i.

SET PROPERTY OF h_i 'Color' = 19.

GET PROPERTY OF h_z27sl 'Interior' = h_i.

SET PROPERTY OF h_i 'Color' = 16711619.

Both makes my background color black.

What is the key key to bkgrnd colors ?

Thanks in advance

Manuel Nagler

[Former Member]

Manuel,

Try this instead:

SET PROPERTY OF h_i 'ColorIndex' = 19.

[Former Member]

That was really nice . Thank you for ur suggestions . Actually i was not aware of that colorindex. Thank you very much.

[Former Member]

Thanks a lot. It works very well !!