Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Download Data to an excel sheet, with colours

Former Member
0 Kudos

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.

9 REPLIES 9

Former Member
0 Kudos

Hi,

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

Regards,

John.

0 Kudos

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
0 Kudos

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.

0 Kudos

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)

0 Kudos

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

0 Kudos

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

0 Kudos

Manuel,

Try this instead:

SET PROPERTY OF h_i 'ColorIndex' = 19.

0 Kudos

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

0 Kudos

Thanks a lot. It works very well !!