Cell coloring

[sverrirs]

Hi,

First of all very impressed by the ExcelDna Library, excellent work.

And now for my newbie question:
Is it possible to change the background color / border / font
formatting of a cell through ExcelDna?
For example if I had the following function


[ExcelFunction( Name = "Func1", Description = "Just a simple function
to test", Category = "Testing ExcelDna", IsMacroType = true)]
public static object Func1(
[ExcelArgument( Name = "Number1", Description =
"Param1")] object number1,
[ExcelArgument(Name = "Number2", Description =
"Param2")] object number2,
[ExcelArgument(AllowReference = true)] object
reference
)
{
int x1, x2 = 0;
x1 = (int)number1;
x2 = (int)number2;

if( x1 > x2 )
{
// Change the background of the 'reference' to any
color.
}
else if( x1 < x2 )
{
// Change the border of the 'reference' to any
color.
// XlCall.Excel(XlCall.xlcBorder, 0, 5, 5, 5, 5, 0, 1,
5, 10, 15, 20);
}
else if( x1 == x2 )
{
// Change the font of the 'reference' to bold
}

}

Any help would be greatly appreciated :)

Thanks,
Sverrir S.

[Govert van Drimmelen]

Hi,

Excel does not allow you to make changes to the cell formatting as
part of a function call. You'll be able to do this in a macro.

Regards,
Govert

[Rookie]

You may try the following:
pass the worksheet object (say ws) and then declare the cell as a
range and use get_Range to format the cell e.g.
ws.get_Range("F8","Z1000").NumberFormat = "#,##0_);[Red](#,##0)";

> > Sverrir S.- Hide quoted text -
>
> - Show quoted text -

[incred]

Just as a query, what is your reason for wanting to do this?

Sometimes it's better to let excel do what it's good at. This would be
something that would be done using either Cell formating directly or
conditional formatting if you had multiple colours for multiple
conditions.

I would definitely suggest leaving your number crunching in your
addin, and your presentation in your excel sheet/template.

> > - Show quoted text -- Hide quoted text -

[sverrirs]

Thank you all for the quick replies and suggestions.

My reasons for wanting to alter the appearance of cells (momentarily
or permanently) is that I have an RTD server and I want to be able to
highlight changes to cell values (e.g. set the cell background to red
if the value shifts by a certain % down or green if it shifts some %
upwards).

It is a shame that I can't do this directly through the C-API as it
would have been nice to be able to bypass the painfully slow COM API.
(I saw that the ManagedXLL library demonstrates behavior like this on
their feature page http://www.stochastix.de/solutions/excel/managedxll/latest/features,
"Worksheet functions" does that sound like COM usage?)

Anyways, I tried two approaches to solviing this issue using a rather
simplistic RTD server that every two seconds updated all topics with a
random number:

1)
Implementing a rather naive (and excruciatingly slow) method of
setting the colorindex of the cell.

[ExcelCommand( Description = "Sets the background color of a cell to a
certain value (macro)" )]
public static void SetCellColorIndex( ExcelReference reference, int
colorIndex )
{
string r1 = Utilities.GetExcelCellReference
( reference.ColumnFirst, reference.RowFirst );
string r2 = Utilities.GetExcelCellReference( reference.ColumnLast,
reference.RowLast );
// LogDisplay.WriteLine( "Setting COLOR REF, " + r1 + "," + r2 + "
- " + colorIndex );
try
{
Instance.xlApp.get_Range( r1, r2 ).Interior.ColorIndex =
colorIndex;
}
catch( Exception ex )
{
//MessageBox.Show( ex.Message );
}
}

This approach I can't recommend as it is making to many expensive
calls over COM (assuming you update coloring every time the RTD
notifies of an update)


2)
Manipulating the cell's conditional formatting fields (as suggested by
Govert).
This approach worked out much better as instead of manually updating
the value every time the RTD server pushed out new values I could
simply set the conditional formatting once (when the RTD call is
created) and then have Excel handle all the messy stuff. A Timer class
handles all the COM stuff in the background. Bear with me, the code is
experimental and only intended to help people along (or spark a
discussion on what could be done better).

private struct ConditionalRange
{
public readonly ExcelReference reference;
public readonly double value;
public ConditionalRange( ExcelReference reference, double value )
{ this.reference = reference; this.value = value; }
}

private Timer mConditionalTimer = new Timer( ConditionalTimerCallback,
null, Timeout.Infinite, Timeout.Infinite );
private readonly OrderedDictionary<string, bool> mConditionalRanges =
new OrderedDictionary<string, bool>();
private readonly List<ConditionalRange> mConditionalRangesList = new
List<ConditionalRange>();

public void AddToConditionalRange( ExcelReference r, double value )
{
lock( mConditionalRangesList )
{
if( mConditionalTimerCanStart )
{
mConditionalTimer.Change( 2000, 1000 );
mConditionalTimerCanStart = false;
}

string r1 = Utilities.GetExcelCellReference(r.ColumnFirst,
r.RowFirst);
string r2 = Utilities.GetExcelCellReference(r.ColumnLast,
r.RowLast);
string range = r1 + ":" + r2;
if( !mConditionalRanges.ContainsKey( range ) )
{
mConditionalRangesList.Add(new ConditionalRange(new
ExcelReference(r.RowFirst, r.RowLast, r.ColumnFirst, r.ColumnLast),
value));
mConditionalRanges.Add( range, true );
}
}
}

public void RemoveConditionalRange(ExcelReference r)
{
lock (mConditionalRangesList)
{
string r1 = Utilities.GetExcelCellReference(r.ColumnFirst,
r.RowFirst);
string r2 = Utilities.GetExcelCellReference(r.ColumnLast,
r.RowLast);
string range = r1 + ":" + r2;
if (!mConditionalRanges.ContainsKey(range))
{
mConditionalRanges.Remove( range );
// TODO Delete also the range from the conditional
formatting list if it has yet to be applied
}
}
}

private void ConditionalTimerCallback( object state )
{
lock( mConditionalRangesList )
{
bool allok = true;
try
{
if( mConditionalRangesList.Count <= 0 )
return;

for( int i = 0; i < Math.Min( 20,
mConditionalRangesList.Count ); i++ )
{
try
{
ConditionalRange cRange = mConditionalRangesList
[i];
string r1 = Utilities.GetExcelCellReference
( cRange.reference.ColumnFirst, cRange.reference.RowFirst );
string r2 = r1;// Utilities.GetExcelCellReference
(cRange.reference.ColumnLast, cRange.reference.RowLast);
Instance.xlApp.get_Range( r1,
r2 ).FormatConditions.Delete();
IconSetCondition iconCond = (IconSetCondition)
Instance.xlApp.get_Range( r1, r2 ).FormatConditions.AddIconSetCondition
();
iconCond.IconSet = xlApp.ActiveWorkbook.IconSets
[XlIconSet.xl3Arrows];
iconCond.ModifyAppliesToRange(((Worksheet)
Instance.xlApp.ActiveSheet).get_Range(r1, r2).Cells);
/*
* Excel iterrates through the criterias in reverse
direction, and apparently the first item in the list
* nr = 1, is read-only and thus cannot be modified
* http://social.msdn.microsoft.com/forums/en-US/vsto/thread/f9d6e6e3-a7e8-4237-a11d-a3b55c4a6c57/
* Also must set the type before you set the value,
because setting the type clears the value field!!!
*/
for( int j = 1; j < iconCond.IconCriteria.Count; j+
+ )
{
iconCond.IconCriteria[j + 1].Type =
XlConditionValueTypes.xlConditionValueNumber;
switch( j )
{
case 1:
iconCond.IconCriteria[j + 1].Operator
= (int)XlFormatConditionOperator.xlGreaterEqual; // Yellow condition
iconCond.IconCriteria[j + 1].Value =
cRange.value * 0.75;
break;
case 2:
iconCond.IconCriteria[j + 1].Operator
= (int)XlFormatConditionOperator.xlGreater; // Green condition
iconCond.IconCriteria[j + 1].Value =
cRange.value * 1.25;
break;
}
}
string rangeStr = Utilities.GetExcelCellReference
( cRange.reference.ColumnFirst, cRange.reference.RowFirst ) + ":" +
Utilities.GetExcelCellReference( cRange.reference.ColumnLast,
cRange.reference.RowLast );
if( mConditionalRanges.ContainsKey( rangeStr ) )
mConditionalRanges.Remove( rangeStr );
mConditionalRangesList.RemoveAt( i );
}
catch( Exception ex )
{
allok = false;
//mLogger.Error( "ERROR Adding condition " +
ex.Message );
}
}

if( allok )
allok = mConditionalRangesList.Count == 0;
}
catch( Exception ex )
{
allok = false;
//mLogger.Fatal( "ConditionalTimerCallback " + ex );
}
if( allok )
{
mConditionalTimer.Change( Timeout.Infinite,
Timeout.Infinite );
mConditionalTimerCanStart = true;
}
}
}


My RTD wrapper function (saves the calling cell and passes it into the
RTD server):
==============================
[ExcelFunction( Name = "RandomRTDFunction", Description = "Just a
simple random RTD function to test", Category = "Testing

ExcelDna",IsMacroType = true )]

public static object RandomRTDFunction( object feedcode, object
market )
{
try
{
if( !IsValid( feedcode ) || !IsValid( market ) )
return 0;

ExcelReference reference = Utilities.Caller();
LogDisplay.WriteLine( ( reference != null ? ( "Cell Ref:" +
reference.RowFirst + ":" + reference.ColumnFirst ) : "No
reference" ) );
object retValue = Instance.xlApp.WorksheetFunction.RTD
( "ExcelDnaRtdServer.SimpleRtdServer", "", "Func1",
(reference != null ? (reference.RowFirst + ":" +
reference.RowLast + ":" + reference.ColumnFirst + ":" +
reference.ColumnLast) : string.Empty),
feedcode, market,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,MISSING, MISSING,
MISSING, MISSING, MISSING, MISSING, MISSING, MISSING, MISSING,
MISSING, MISSING );
return retValue;
}
catch( Exception ex )
{
return ex.Message;
}
}

== And the Utilities function to convert numerical values into Excel
Colum/Row references
/// <summary>
/// Expects a zero based indices to a row/column (adjust to
fit the excel way of referencing)
/// </summary>
/// <param name="column"></param>
/// <param name="row"></param>
/// <returns></returns>
[ExcelFunction(IsHidden = true)]
public static string GetExcelCellReference( int column, int
row)
{
return string.Format( "{0}{1}", ConvertToExcelColumnString
( column+1), row+1 );
}

private static string ConvertToExcelColumnString( int
remainder )
{
// Base case if empty
if (remainder <= 0)
return string.Empty;

string ret = string.Empty;

double div = remainder;
int mod = 0;
while( div > 26 )
{
mod = (int)( div % 26 );
if( mod == 0 )
{
mod = 26;
div = div - 1;
}
ret = (char)( 64 + mod ) + ret;
div /= 26;
}

ret = (char)( 64 + div ) + ret;
return ret;
}


Although the "registration" phase, that is the time it takes the
background thread to set all the conditional formulas takes a while to
complete this experiment works alright. However what needs to be done
is to throttle the RTD updates during the COM registration so that we
actually get access to the worksheet through COM (dirty i know). But a
start right?

What do the specialist think?
Could something like this be made faster by incorporating support for
it into the C wrapper in some way?


Cheers,
Sverrir S.

On Mar 8, 9:37 pm, incred <grleach...@yahoo.com> wrote:
> Just as a query, what is your reason for wanting to do this?
>
> Sometimes it's better to let excel do what it's good at. This would be
> something that would be done using either Cell formating directly or

> conditionalformattingif you had multiple colours for multiple

> conditions.
>
> I would definitely suggest leaving your number crunching in your
> addin, and your presentation in your excel sheet/template.
>
> On Mar 6, 9:25 pm, Rookie <r98...@hotmail.com> wrote:
>
> > You may try the following:
> > pass the worksheet object (say ws) and then declare the cell as a
> > range and use get_Range to format the cell e.g.
> > ws.get_Range("F8","Z1000").NumberFormat = "#,##0_);[Red](#,##0)";
>
> > On Mar 6, 4:20 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
>
> > > Hi,
>
> > > Excel does not allow you to make changes to the cellformattingas
> > > part of a function call. You'll be able to do this  in a macro.
>
> > > Regards,
> > > Govert
>
> > > On Mar 4, 6:40 pm, sverrirs <sverr...@gmail.com> wrote:
>
> > > > Hi,
>
> > > > First of all very impressed by the ExcelDna Library, excellent work.
>
> > > > And now for my newbie question:
> > > > Is it possible to change the background color / border / font

> > > >formattingof a cell through ExcelDna?

[incred]

Hi.

I'm still of the view that you should not be putting your conditional
formatting in your code. This should be part of your design in your
excel. Rather return the current and previous versions to 2 columns,
hide the previous, and conditional format the current. This would be
keeping your model-view seperate. Saying that, you really are biting
the bullet there with using Rtd. DDE is much better for this. DDE
functions can be called on ranges which makes them far more effective
for ranged queries. RTD just doesn't cut it for useful data sets,
bringing back data one call at a time will kill your machine if you
have large sets.

But then again, I can't see your code, your spreadsheet, or your
issues.

I'd be glad to see your RTD server. I'm tryhing to find the time to do
a simpleDDEServer... 3 months later.... Nada :)

On Mar 9, 6:21 pm, sverrirs <sverr...@gmail.com> wrote:
> Thank you all for the quick replies and suggestions.
>
> My reasons for wanting to alter the appearance of cells (momentarily
> or permanently) is that I have an RTD server and I want to be able to
> highlight changes to cell values (e.g. set the cell background to red
> if the value shifts by a certain % down or green if it shifts some %
> upwards).
>
> It is a shame that I can't do this directly through the C-API as it
> would have been nice to be able to bypass the painfully slow COM API.
> (I saw that the ManagedXLL library demonstrates behavior like this on

> their feature pagehttp://www.stochastix.de/solutions/excel/managedxll/latest/features,

>                     *http://social.msdn.microsoft.com/forums/en-US/vsto/thread/f9d6e6e3-a7...

[burnt quant]

Not sure about the DDE vs RTD arguments - when Excel calls back into
your RTD server you can update many values at once, if for instance
1000 prices have changed then you just send their keys and values back
to Excel in one go. The problem I had, historically with DDE was it's
stability, all those realtime financial data spreadsheets that just
stop updating for no discernible reason whatsoever.

Regarding sverrirs's original issue: how about getting your RTD server
to return a field that your spreadsheet can interpret as to how to
colour the cell? For instance if you return a "last tick direction"
field that can be either 1,0,-1 to indicate up, down or no change.
Then you can format the price cell conditionally on the basis of the
value of this "last tick direction" cell. Should give you complete
control without having to do anything hack-ish, also keeps all your
code in the RTD server.

[sverrirs]

Hi Burnt,

Thank you for the reply, seems like a simple and easy solution for the
RTD.
One of those when reading it you slap your forehead and murmur "Of
course!" :)

I however guess that I´d have to implement it in a sligtly more
complex manner than just a single function call (as RTDs cannot return
arrays of values) so I´d think that having the XLL function return an
array of two functions that would then trigger the actual RTD calls. I
´ll try that out and see how it works.

And on the matter of RTD servers vs DDE, I believe that they were
developed by the Excel team to improve upon the existing DDE framework
(as DDE can at best described as temperamental).


Cheers,
Sverrir S.

[burnt quant]

I'm glad you found that comment useful, encouraged I will go on!

I don't think you have to worry about returning arrays of values for
instance in my market data RTD I use cell formulas like this:

=RTD("COM SERVER NAME", "", "TICKER NAME", "FIELD")

Where COM SERVER NAME is obviously the name of the com server in
question, TICKER NAME is the name of the stock or whatever object you
want and FIELD is the actual data that you need from that object (eg
Price, volume, last tick direction, etc). The elements of your return
array are simply different fields, if I have understood you correctly?
I don't know if you've actually gone through writing an RTD server
yet, but you'll find that Excel assigns a topic id to each field that
you define and then you keep a hashtable of those ids and the
properties of the particular object that they refer to. Then when
excel asks for updates you give it the topic id's and all the values
as one big list. It then puts the values into the cells as per the
formula above. Really you don't need to return an array when multiple
properties of an object change, all the relevant cells will update.

I realise at this point that we are drifting away from a relevant
discussion of ExcelDNA. I think in this particular case you can get
away with just using an RTD server and some formatting in the
spreadsheet itself. ExcelDNA is a wonderful tool, it's just that you
probably don't need it for this particular problem.