In my previous post on this subject, I showed how to adapt simple Excel Services UDFs work on the client by exposing them as COM classes that are then used by Excels automation extension capabilities.
In this post, I will show how to make not-so-simple UDFs work.
Say you have the following server UDF:
public double MySum(object[,] range)
double sum = 0;
foreach (object o in range)
if (o is double)
sum += (double)o;
Using the simple trick of exposing this method through COM will not work because Excel does not know how to turn a range into a simple object array. Instead, we will need to make the call to change the value from what Excel passes in to what the UDF method knows how to handle:
public double MySum(object rangeParam)
// Get the Value2 property from the object.
Type type = rangeParam.GetType();
double result = 0;
object[,] range = (object[,])type.InvokeMember("Value2",
int rows = range.GetLength(0);
int columns = range.GetLength(1);
object[,] param = new object[rows, columns];
Array.Copy(range, param, rows * columns);
result = MySum(param);
What this method does is to invoke the “Value2” property of the range object that Excel passes into it and get back the array of values. That array is then fed into the original MySum() method which makes the calculation.
In this instance, the Array.Copy() call is not necessary – we could have just passed the array we got straight to the original MySum() method. However, this could cause some potential bugs in other cases. Excel uses 1-based arrays whereas most .NET languages use 0-based ones. Because the original MySum() method uses a foreach statement, it would never hit this issue. If it was to do a regular for iteration, it could have potentially hit a problem here.
A final, very important part is to make sure that the original MySum() method is not visible for Excel to use. To do that, we will simply decorate it with the ComVisible attribute:
The next and final post about this will show how to handle UDF volatility when adapting a server UDF to work on the server.
Corners I rounded in this post and general notes: