How do I convert from a 'double' valued color to a System.Drawing.Color?

N

Nat

Hi All,

I am looking for any advice as to how to detect cell colors in an
Excel Spreadsheet programatically using .NET interop with C#. I have
read many examples where 'ColorTranslator.ToOle(...)' is used to set a
color using a System.Drawing.Color, but have been unable to establish
how to go the other way.

When I pull the cell color in question it appears to be a 'double' and
not an 'int'. I have read that the double actually represents a
series of byte encoded RGB values. Here's the code snippet I am
using:

Excel.Worksheet ws = (Excel.Worksheet)ThisApplication.ActiveSheet;

Excel.Range usedRange = ws.UsedRange;

Excel.Range cell;
// Just get the ranges with data
Excel.Range sRange = null;
try
{
sRange = usedRange.SpecialCells(Excel.XlCellType.xlCellTypeConstants,
(object)3);
/*This seems to throw an exception if there are no data cells
found.*/
}
catch( COMException e )
{
System.Diagnostics.Debug.WriteLine("Error, no cells
found.\r\n"+e.Message+":\r\n"+e.StackTrace);
return;
}
// Create a collection of unique colors
StringDictionary colorList = new StringDictionary();

ThisApplication.ScreenUpdating = false;
for( int areaId = 1; areaId <= sRange.Areas.Count; areaId++)
{
Excel.Range areaRange = sRange.Areas.get_Item( areaId );
for( int row = 1; row <= areaRange.Rows.Count; row++)
{
// Let's just look at the first cell of each row.
cell = (Excel.Range)areaRange.Cells[row, 1];
// Print the Cell value
System.Diagnostics.Debug.WriteLine("Cell = "+cell.Value2);
// Get the Cell Color
object cellColor = cell.Interior.GetType().InvokeMember(
"Color", BindingFlags.GetProperty, null, cell.Interior ,null);

Debug.WriteLine("Reflected Color = "+cellColor);

try
{
// This shows a System.Double
Debug.WriteLine( "Cell value type = "+cellColor.GetType());

// This is the Hack -works sometimes.
Color realDeal = ColorTranslator.FromOle( int.Parse( cellColor+"") );
Debug.WriteLine("Color's Name = "+realDeal.Name);

// Add our color to the list
colorList[ realDeal.Name ] = null;
}
catch( Exception e)
{ // This is mostly here for the Hack.
System.Diagnostics.Debug.WriteLine(
"ERROR:\r\n"+e.Message+":\r\n"+e.StackTrace);
}

}
}
// Display the unique colors found on the spreadsheet
StringBuilder buff = new StringBuilder();
foreach( string key in colorList.Keys )
{
buff.Append(key).Append("\r\n");
}
ThisApplication.ScreenUpdating = true;
MessageBox.Show( buff.ToString(), "Colors Found During Analysis.");
}

Any help would be welcome.

Thanks,
Nat

PS. If you have any good reference materials for programming add-ins
using C# I would love a pointer to them! Thanks again.
 
T

Tom Ogilvy

You might want to start with David McRitchie's page on Color in Excel:

http://www.mvps.org/dmcritchie/excel/colors.htm

--
Regards,
Tom Ogilvy

Nat said:
Hi All,

I am looking for any advice as to how to detect cell colors in an
Excel Spreadsheet programatically using .NET interop with C#. I have
read many examples where 'ColorTranslator.ToOle(...)' is used to set a
color using a System.Drawing.Color, but have been unable to establish
how to go the other way.

When I pull the cell color in question it appears to be a 'double' and
not an 'int'. I have read that the double actually represents a
series of byte encoded RGB values. Here's the code snippet I am
using:

Excel.Worksheet ws = (Excel.Worksheet)ThisApplication.ActiveSheet;

Excel.Range usedRange = ws.UsedRange;

Excel.Range cell;
// Just get the ranges with data
Excel.Range sRange = null;
try
{
sRange = usedRange.SpecialCells(Excel.XlCellType.xlCellTypeConstants,
(object)3);
/*This seems to throw an exception if there are no data cells
found.*/
}
catch( COMException e )
{
System.Diagnostics.Debug.WriteLine("Error, no cells
found.\r\n"+e.Message+":\r\n"+e.StackTrace);
return;
}
// Create a collection of unique colors
StringDictionary colorList = new StringDictionary();

ThisApplication.ScreenUpdating = false;
for( int areaId = 1; areaId <= sRange.Areas.Count; areaId++)
{
Excel.Range areaRange = sRange.Areas.get_Item( areaId );
for( int row = 1; row <= areaRange.Rows.Count; row++)
{
// Let's just look at the first cell of each row.
cell = (Excel.Range)areaRange.Cells[row, 1];
// Print the Cell value
System.Diagnostics.Debug.WriteLine("Cell = "+cell.Value2);
// Get the Cell Color
object cellColor = cell.Interior.GetType().InvokeMember(
"Color", BindingFlags.GetProperty, null, cell.Interior ,null);

Debug.WriteLine("Reflected Color = "+cellColor);

try
{
// This shows a System.Double
Debug.WriteLine( "Cell value type = "+cellColor.GetType());

// This is the Hack -works sometimes.
Color realDeal = ColorTranslator.FromOle( int.Parse( cellColor+"") );
Debug.WriteLine("Color's Name = "+realDeal.Name);

// Add our color to the list
colorList[ realDeal.Name ] = null;
}
catch( Exception e)
{ // This is mostly here for the Hack.
System.Diagnostics.Debug.WriteLine(
"ERROR:\r\n"+e.Message+":\r\n"+e.StackTrace);
}

}
}
// Display the unique colors found on the spreadsheet
StringBuilder buff = new StringBuilder();
foreach( string key in colorList.Keys )
{
buff.Append(key).Append("\r\n");
}
ThisApplication.ScreenUpdating = true;
MessageBox.Show( buff.ToString(), "Colors Found During Analysis.");
}

Any help would be welcome.

Thanks,
Nat

PS. If you have any good reference materials for programming add-ins
using C# I would love a pointer to them! Thanks again.
Click to expand...
 
N

Nathanial Langman

Hi Tom,

Thank you for the quick reply. I have seen and read this page. While
it was helpful for me, it didn't address my core problem: namely, the
color being returned as a double from the Excel application. I am
working with C#, have I missed something obvious?

Thanks,
Nat


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
T

Tom Ogilvy

Visual Basic and Excel return a double with colors in the form BBGGRR

while I believe system colors may be stored as RRGGBB although I am not
sure what you specifically mean by system colors. There are special
definitions for the colors of scollbars and such which are termed system
colors and have a different numbering system.

anyway, as an example, if we returned the number in hexidecimal.

ActiveCell.Interior.Color = vbBlue
? hex(activecell.Interior.Color)
FF0000
ActiveCell.Interior.Color = vbRed
? hex(activecell.Interior.Color)
FF
activeCell.Interior.Color = vbGreen
? hex(activecell.Interior.Color)
FF00
? vbBlue, hex(vbBlue)
16711680 FF0000
? vbGreen, hex(vbGreen)
65280 FF00
? vbRed, hex(vbRed)
255 FF

In any event, it appears the number you are passing to system colors is not
using the same system of values as system colors.
 
N

Nathanial Langman

Hi Tom,

Thanks again for the speedy reply. I am going to take a look into your
comments shortly. I think perhaps my expectations were skewed. I read
many articles where the examples used:

ColorTranslator.ToOle( Color.White );

to set a cell color. I was trying to make this work in reverse; namely,
to decode the color of the cell using:

Color realDeal = ColorTranslator.FromOle( cellColor );

Where 'cellColor' is the interior color I got from the Excel
application. This didn't work because the function argument only
accepts an integer, and Excel returns a double for it's colors.

Stepping back a moment... The big picture of my effort was to be able
to write a simple add-in for Excel in C# that could read through a sheet
an create an index of the unique cell colors that it encountered. This
seems like it should be much easier, so I can't help feel like I missing
the fundamentals.

I truly appreciate the advice, thank you.
Nat

PS. Are there any books about programming C# add-ins for office that
you might recommend? Thanks again.





*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
T

Tom Ogilvy

If I knew anything about C# or even .Net, I could probably be more help.
Unfortunately, those are not subjects I have explored.

You might want to ask in

news://msnews.microsoft.com/microsoft.public.vsnet.vstools.office
 
K

keepITcool

Nathaniel,

BTW:
I hope you're familiar with the difference between colorindex and
color.. colorindex is a pointer to the Workbook's .Colors array.

in VBA i'd use a collection object (with on error resume next) to build
a collection of all used colors...

Sub ColorIndex()
Dim itm As Variant
Dim cell As Range
Dim colColors As Collection

Set colColors = New Collection
On Error Resume Next
For Each cell In ActiveSheet.UsedRange.Cells
With cell.Interior
colColors.Add .ColorIndex, CStr(.ColorIndex)
End With
Next
For Each itm In colColors
Debug.Print itm, ActiveWorkbook.Colors(itm), _
Hex(ActiveWorkbook.Colors(itm))
Next
End Sub

Can you translate to C# ? <bg>


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
K

keepITcool

oops :)

didn't read back the entire thread ..
probably cause we had a wee party in Holland last night :)


Well.. the difference being I collect colorindex
then run thru wb.colors() to get the color

looked as if OP wasn't aware of .colors()



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
T

Tom Ogilvy

looked as if OP wasn't aware of .colors()
Perhaps it appeared that way, but in his code, it appears he does.

object cellColor = cell.Interior.GetType().InvokeMember(
"Color", BindingFlags.GetProperty, null, cell.Interior ,null);


Note "Color"
 
K

keepITcool

exactly...

which i assume is c# equiv of
callbyname(cell.interior, "color", vbget)

so..

OP is still NOT using colorindex and workbook.colors()


i was refering to this:
quote:
Stepping back a moment... The big picture of my effort was to be able
to write a simple add-in for Excel in C# that could read through a sheet
an create an index of the unique cell colors that it encountered. This
seems like it should be much easier, so I can't help feel like I missing
the fundamentals.




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Top