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.
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.