Setting Excel colors

[Louise Evans]

Hi Everyone,

I'm trying to set the colors in a excel sheet/cell according to colors a user has selected back in my progress browse. The problem I'm having is ensuring that if the users wants to see column X in red, to show it in red in Excel, no matter the version etc. etc.

My guess is I have to do something with RGB function to set the colors as per the progress.ini file. But I'm not having any success getting that working....

Has anyone got any ideas/suggestions on how I can do this?

Thanks!

Louise

 

[bendaluz2]

DEFINE VARIABLE chExcel AS COMPONENT-HANDLE NO-UNDO.
DEFINE VARIABLE chBook AS COMPONENT-HANDLE NO-UNDO.
DEFINE VARIABLE chSheets AS COMPONENT-HANDLE NO-UNDO.
DEFINE VARIABLE chSheet AS COMPONENT-HANDLE NO-UNDO.
DEFINE VARIABLE chRange AS COMPONENT-HANDLE NO-UNDO.

CREATE 'Excel.Application.8' chExcel.

ASSIGN chExcel:VISIBLE = NO
       chBook = chExcel:WORKBOOKS:ADD()
       chSheets = chBook:WORKSHEETS.
chExcel:DISPLAYALERTS = FALSE.

ASSIGN chSheet = chSheets:ADD().

**Dump your data**

ASSIGN chRange = chSheet:RANGE("A1:A4")
       chRange:FONT:COLOR = bgr-value.

RELEASE OBJECT chRange.

RELEASE OBJECT chSheet
    NO-ERROR.

chBook:SAVEAS("test.xls",43,"","",no,no,1)
    NO-ERROR.

RELEASE OBJECT chSheets
    NO-ERROR.
RELEASE OBJECT chBook
    NO-ERROR.

chExcel:QUIT().

RELEASE OBJECT chExcel
    NO-ERROR.

This would change the colour of the cell range A1 - A4, so just change this to the range you want.

From progress you need to specify colours as BGR values rather than RGB values (dont ask me why). It doesnt mean much difference really, just the byte positions are changed. Basically each color, Blue, Green and Red has an 8 bit value (i.e. 0 - 255) which indicates the strength of the colour. To calculate the bgr-value do the following calculation

(Blue-Value * (256 * 256)) + (Green-Value * 256) + (Red-Value)

i.e. bright red would be 255, bright green would be 65280 and bright blue would be 16711680

Hope this helps

Originally posted by Louise Evans
Hi Everyone,

I'm trying to set the colors in a excel sheet/cell according to colors a user has selected back in my progress browse. The problem I'm having is ensuring that if the users wants to see column X in red, to show it in red in Excel, no matter the version etc. etc.

My guess is I have to do something with RGB function to set the colors as per the progress.ini file. But I'm not having any success getting that working....

Has anyone got any ideas/suggestions on how I can do this?

Thanks!

Louise