Partager via

Making Excel Services UDFs work on Excel 2007 - Part 2

  • Article

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

  • Anonymous
    September 01, 2006
    PingBack from http://blogs.msdn.com/excel/archive/2006/09/01/735561.aspx

  • Anonymous
    September 28, 2006
    Instead of re-hashing information I've found elsewhere I figured a pre-reqs post would be good.
    One...

  • Anonymous
    February 05, 2007
    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 !

  • Anonymous
    February 05, 2007
    The comment has been removed

  • Anonymous
    April 09, 2008
    Hin und wieder werde ich gefragt, ob man mit VSTO auch Tabellenfunktionen für Excel schreiben kann. Nun,

  • Anonymous
    August 19, 2008
    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,

  • Anonymous
    August 19, 2008
    Hi Johnson, This should pretty much work the same way on client - what error are you seeing?

  • Anonymous
    August 19, 2008
    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.

  • Anonymous
    September 19, 2008
    //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];

  • Anonymous
    January 08, 2009
    How do you stop the Excel web access returning #VALUE! when using the above code in a UDF?

  • Anonymous
    January 08, 2009
    How do you stop the Excel web access returning #VALUE! when using the above code in a UDF?