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:

[UdfMethod]

public double MySum(object[,] range)

{

       double sum = 0;

       foreach (object o in range)

       {

              if (o is double)

              {

                     sum += (double)o;

              }

       }

       return sum;

}

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;

       if (type.IsCOMObject)

       {

              object[,] range = (object[,])type.InvokeMember("Value2",

                     System.Reflection.BindingFlags.Instance |

                     System.Reflection.BindingFlags.Public |

                     System.Reflection.BindingFlags.GetProperty,

                     null,

                     rangeParam,

                     null);

              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);

       }

       return result;

}

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:

[UdfMethod]

[ComVisible(false)]

public double MySum(object[,] range)

{

       // ...

}

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:

• If the object passed into the second version of the MySum() method is not a COM object, it may be useful to do something more descriptive (such as returning an error string or throwing an error perhaps) so that potential programming errors do not result in seemingly properly calculated workbooks.
• Even though .NET uses zero-based arrays by default, it can use any other based array as well. The code to detect this and handle it is not usually employed though and most code expects to get zero-based arrays. For that reason, it’s a good idea to use the Array.Copy() mechanism before passing the array into the server UDF method.