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.xlCellType Constants,
(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.

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" wrote in message
om...
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.xlCellType Constants,
(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.

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!

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.

--
Regards,
Tom Ogilvy


"Nathanial Langman" wrote in message
...
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!

http://msdn.microsoft.com/library/de...asst opic.asp

might be useful. Even it is looking for RRGGBB, so you would have to
convert the value you get.

this vb function written by Chip Pearson demonstrates how to break it apart.

Sub GetRGB(RGB As Long, ByRef Red As Integer, _
ByRef Green As Integer, ByRef Blue As Integer)
Red = RGB And 255
Green = RGB \ 256 And 255
Blue = RGB \ 256 ^ 2 And 255
End Sub




--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Nathanial Langman" wrote in message
...
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!

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!

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/microsof...vstools.office

--
Regards,
Tom Ogilvy

"Nathanial Langman" wrote in message
...
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!

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


Nathanial Langman wrote:

Hi Tom,

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!

// Create a collection of unique colors
StringDictionary colorList = new StringDictionary();

--
Regards,
Tom Ogilvy


"keepITcool" wrote in message
...
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


Nathanial Langman wrote:

Hi Tom,

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!

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


"Tom Ogilvy" wrote:

// Create a collection of unique colors
StringDictionary colorList = new StringDictionary();

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"

--
Regards,
Tom Ogilvy

"keepITcool" wrote in message
...
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


"Tom Ogilvy" wrote:

// Create a collection of unique colors
StringDictionary colorList = new StringDictionary();

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


"Tom Ogilvy" wrote:

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"