Making Excel Services UDFs work on Excel 2007 – Part 2


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.

Comments (11)

  1. Instead of re-hashing information I’ve found elsewhere I figured a pre-reqs post would be good.

    One…

  2. TS says:

    I have mentioned this before, but I was thinking that with Office 2007, the need to mess with COM altogether should be a thing of the past. Why aren’t there ways of doing everything without COM? Is that a plan to remove all dependence on COM (i personally don’t like getting my hands dirty messing with it).

    thanks !

  3. Shahar says:

    I cannot comment about future developments in Office of course.

    However, this I do know:

    1. Though I don’t know for sure, I personally doubt we will "get rid of COM" anytime soon – there are too many solutions that rely on it and that would break all of them.

    2. With every version, I think we make some nice progress with the managed story we have. Hopefuly at some point in the future (and I really cant say when, because I honestly dont know) there will be less and less need for COM when programming against Excel.

  4. Hin und wieder werde ich gefragt, ob man mit VSTO auch Tabellenfunktionen für Excel schreiben kann. Nun,

  5. Johnson says:

    Hi,

     How the following code snippet can make work for excel 2007 client – Here Object array as output – I’d really appreciate if you can explain how to do it.

    [UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]

           public object[,] getSharepointView(string serverName,

                                              string siteName,

                                              string listName,

                                              string viewName,

                                              bool getTitles)

    Thanks,

  6. Shahar says:

    Hi Johnson,

    This should pretty much work the same way on client – what error are you seeing?

  7. Johnson says:

    I’m seeing #VALUE- but the following one is working fine

           [UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]

           public object externalRef(string workbookPath,

                                     string sheetName,

                                     string range)

           {

    So i’m suspecting that an object array output giving error.

  8. Youdhbir Singh says:

    //You make following changes in the code (class1.cs) provided and pass your site base address in site name

    // For me sitename="http://hddlntdmt0281:100". And press ctrl+shift+enter to run your UDF (as it is

    // returning array. Please let me know if u still get problem. :-)

    public object[,] getSharepointView(string siteName, string listName, string viewName, bool getTitles)

           {

               try

               {

                   // We should impersonate the current user viewing EWR… not the UDF manager…

                   //using (WindowsImpersonationContext wiContext = impersonateUser())

                   //{

                       // Grab Sharepoint Objects

                       SPSite site = new SPSite(siteName);

                       SPList list = site.AllWebs[""].Lists[listName];

                       SPView view = list.Views[viewName];

  9. Andrew says:

    How do you stop the Excel web access returning #VALUE! when using the above code in a UDF?

  10. Andrew says:

    How do you stop the Excel web access returning #VALUE! when using the above code in a UDF?