Here is the second post from Danny Khen, a program manager on the Excel Services team. 

In the previous post I explained about the investments we made around UDFs in Excel 2007 and in the Excel Services. I also showed how Excel Services use a different UDF technology than those used by the Excel client – managed UDFs. “Whoa”, you may be asking – “so what about all my investment in Excel UDFs – can’t I take them to the server as well?”

Well, you actually can, with some limitations. And we believe that many implementations of UDFs transfer just fine.

Can my existing UDFs be used on the server?

Or, to be more precise: Can my Excel workbooks call my UDFs on both client and server, and get the same results?

The answer is yes – if your UDFs have the following qualities:

  • The UDF’s entire interface with the Excel sheet is done thru the method call signature; arguments are passed into the function from the Excel formula, and return values are passed back into the formula. Excel Services don’t have an OM like Excel’s, so UDFs that make use of the Excel OM cannot run on the server.
  • The workbooks don’t try to pass one type of data into a different type of UDF argument. (But there’s a workaround if they do, if you’re willing to make a change to the original UDF – use Variants.)
  • There’s no need to distinguish between different error cases in the returned values; #VALUE! is used for all calculation errors as the returned value.
  • Your UDFs are thread-safe. Excel Services run different user sessions in different threads, so a UDF must be thread-safe to be deployed with the server.

If your UDFs comply with those requirements, or if you’re willing to invest in making them compliant – I’ll show you how you can use them with Excel Services.

Wrap them

The basic principle is simply “wrapping” your existing UDFs with managed code UDFs, which expose an equivalent interface. These wrappers, together with the native UDFs they wrap, are deployed to the server; when a workbook formula makes a UDF call, Excel Services uses the UDF method in the managed wrapper, which in turn calls your original UDF and returns its result into the sheet. All of this is transparent to the workbook users – as far as they are concerned, the workbook simply keeps working on the server and presents the same results.

I will focus on UDFs in XLL addins to demonstrate this concept, and briefly mention COM addins as well.

XLL addin UDFs

In the previous post, I attached a code sample to an Excel Services UDF assembly – a couple of methods we “wish Excel had”.

Today I am attaching another implementation of the same couple of methods – this time a cross client/server implementation. I am using the “X” suffix for all names here, to designate that this solution is XLL-based.

What you will find inside is an XLL implementation of two methods – WehWordX and WehWordcountX – that do exactly what their managed equivalents from the previous post did. In addition, you will find a managed assembly, implemented in C++/CLI (“Managed C++”), which exposes a class with two methods by the same name, each of which calls one of the XLL UDFs.

The wrapper functions do the following:

  • String conversion (managed to native; Unicode to ASCII; zero-terminated to Pascal byte-count strings; memory allocation handling).
  • Managed/native interop (we don’t really explicitly do anything in the code to achieve this; C++/CLI does it automagically for us).
  • XLOPER-to-Exception error mapping.

The attached Excel workbook calls the two UDFs and can be used to test them. It can be used as is on the client; it needs to be saved as an XLSX file to the server.

This looks pretty complex…

And it is – by nature of the beast. XLLs have an Excel-specific interface, designed to make them integrate well with Excel and run efficiently; it is somewhat of a challenge to map this interface to a generic, non-Excel function interface.

Next time I will show a more structured client/server UDF solution, which is based on a core function library that was not designed around the Excel interface. I will show how two wrappers are used around the core library – one for Excel client, the other for Excel Services.

COM addin UDFs

If your UDFs are implemented in a COM addin, you can take advantage of Visual Studio’s ability to reflect against your COM interface and generate an interop assembly – a managed DLL with methods that cross the managed/native boundary and call your native UDFs. You then write a thin layer around the interop assembly, with classes and methods that use the UDF attributes that Excel Services requires, and call the interop assembly methods. Your UDF wrappers will also take care of throwing exceptions whenever the original UDF returns a cell error – just like the XLL wrapper sample shows.