Excel 2007 investments in UDFs #1

For the next few posts, we have a “guest post” from Danny Khen, a program manager on the Excel Services team.  Danny is going to talk about UDFs in Excel and Excel Services.  Enjoy.

UDFs are user-defined worksheet functions – custom functions that you create to supplement Excel’s set of intrinsic worksheet functions. UDFs are used to create calculation libraries, or to import data into Excel sheets in custom ways.

In Excel 2007, we’ve made a number of key investments around UDFs. They revolve around two main areas: allowing UDFs to take advantage of important Excel improvements, and extending UDF-based Excel solutions to the server side with Excel Services.

Updated XLLs

A while back Dave mentioned that we updated XLLs (Excel’s addins based on the C-API) to give developers access to new Excel functionality. As that post explained, one of the common things that developers do in XLLs is to implement UDFs. We wanted to make sure that XLL authors can make use of some great new features of Excel 2007 itself in the UDFs they create. To recap, XLLs have support in Excel 2007 for:

• The bigger grid
• More function arguments
• Multi-threaded calculation

There are many more detailed about those improvements in that other post.

Server-side UDFs

Dave has also posted a number of entries about Excel Services – the new feature in the Office SharePoint Server 2007 that enables calculation, display, and exploration of Excel workbooks on the server. These posts cover many aspects of Excel Services.

Much like Excel’s ability to be extended by writing UDFs in Excel addins, Excel Services also has an extensibility mechanism for writing UDFs. I’d like to use the rest of this post to explain and demonstrate Excel Services UDFs. In a follow-up couple of posts, I will show how you can create Excel solutions that use UDFs and can run both on a client machine using Excel 2007 and in a server environment using Excel Services.

They’re managed

Server-side UDFs are implemented as methods .NET 2.0 assemblies. That is to say, Excel Services directly supports only managed code UDFs. Existing native function libraries and Excel UDFs can be used with Excel Services by “wrapping” them with the new style of server managed UDFs; I will show how in the follow-up posts.

But why did we actually “go managed”? Excellent question. Managed code for enterprise-level solutions is becoming more and more popular, because of the many advantages that .NET code has to offer. Robustness and security are among the important advantages. Some of you may already be engaged in developing managed UDFs or other types of managed solutions. With Excel Services, we focused specifically on server stability, and we felt that using .NET as the basis for our extensibility would be the right thing to do in this respect.

They’re part of a V1 feature

In Office SharePoint Server 2007 we introduce the first implementation of Excel Services and of its extensibility mechanism. To start with, we simply had to prioritize the support for various features in Excel Services (even regardless of UDFs), and not everything made it for this first version. Moreover, we know that going forward we will be doing a lot of thinking around managed interfaces and extensibility for Excel – both on the client side and on the server. We want to make sure that customers' initial investment in managed UDFs is secured, and that at the same time we are not constrained in any way when we design the best possible infrastructure in the future. For those reasons, there are certain restrictions with the first version of server UDFs:

• No Excel OM: the entire interface with the Excel sheet is done thru the UDF call signature; arguments are passed into the function from the Excel formula, and return values are passed back into the formula.
• More restrictive than Excel addins with

• Type conversion and supported data types.
• Error handling: all exceptions thrown by the UDF code are returned into the Excel sheet as #VALUE errors.

• Simple load / runtime behavior: for example, all UDFs currently run together with Excel Services code in the same .NET application domain.

We believe that lots of useful solutions can be created under these restrictions; in fact we see many existing Excel UDF-based solutions that could comply.

They need to be thread-safe

Excel Services is a server feature, and its calculation engine runs as a multi-threaded backend service. We have to require (and assume) that all UDFs are thread safe.

Security

A couple of things to mention about the way a server administrator can control the security of Excel Services UDFs.

First, Excel Services will not load and run just any old method in any old .NET assembly. An admin needs to register the assembly on a server list of trusted UDF assemblies.

On top of that, since these are .NET assemblies, an admin can make use of .NET’s CAS (Code Access Security) infrastructure to restrict UDF access to resources. For example, if a UDF package is simply a collection of math calculation functions, an admin can turn off its ability to access web services, external data, native code etc – and make the server environment safer for everyone.

So what does this thing look like?

Very simple, actually. All you need to know about (assuming you speak some .NET dialect) is two new attributes. Both of them are defined in the Microsoft.Office.Excel.Server.Udf namespace, and you need to reference an assembly that is shipped with Excel Services (Microsoft.Office.Excel.Server.Udf.dll) to get them.

The two attributes are UdfClass, which you use to mark a class where server UDFs are defined, and UdfMethod, with which you mark each individual public method to be considered a server UDF. The UdfMethod also has a boolean property – IsVolatile – used to declare the UDF as volatile, if you want the server to call it each time it recalculates the workbook, regardless of any change in its dependencies. The default is false, which means that the method is non-volatile; it gets called only when a value changes somewhere down the dependency chain of the formula that calls the UDF.

That’s really it. A typical UDF class will look something like this:

using Microsoft.Office.Excel.Server.Udf;

namespace YourNamespace
{
    [UdfClass]
    public class YourClass
    {
        [UdfMethod]
        public <return-type> NonVolatileMethod(<arguments>)
        {
  ...
        }
        [UdfMethod(IsVolatile=true)]
        public <return-type> VolatileMethod(<arguments>)
        {
  ...
        }
    }
}

Show us the goods!

Well, alright… Attached to this blog post is a fully developed code sample. It shows a UDF package by the name of WishExcelHad (because it implements a couple of functions that we wish Excel had…). The package has two method to manipulate text strings with words. The first method, WehWordM, takes the string, a required word position, and a delimiter string, and returns the word in that position after parsing the text with the given delimiter. The other method, WehWordcountM, takes a string and a delimiter, and returns the number of words in that string, when it is parsed with the given delimiter.

You can use the attached Excel workbook as an example that calls these two methods in formulas. You’ll need to save it to Excel Services as an XLSX file.

Why the weird “M” as the suffix for those method names? It designates “Managed” – these UDFs are implemented purely with managed code, and are intended to run with Excel Services. Stay tuned to the blog and look for the following posts, where I will show versions of the same UDFs, that can run across Excel 2007 and Excel Services.