Excel 2007 investments in UDFs #3: client/server solution using a core library

In the previous post I showed how an existing set of UDFs, defined in an Excel addin, can be used with Excel Services by wrapping them with a managed UDF assembly.

Some companies develop calculation libraries in a modular way. They want to use their library functions for various purposes on various platforms, and Excel UDFs is just one of those purposes. So they develop a function library, say in C or C++, which is independent of any of those platforms, and does not assume any application-specific interface. Then, to use the function library with Excel, they wrap it with an Excel addin, typically an XLL.

If you have such a solution, and now you wish to extend it to Excel Services as well, then all you need to do is develop the extra managed wrapper for the serer side. Since you already have a core function library, you don’t need to mess with wrapping an XLL; you can wrap your core library directly, and in many cases you can even embed it within the managed DLL – making server deployment much easier.

Let’s see how this is done.

“Wish Excel Had†– version 3

Let’s (ab)use the “Wish Excel Had†code sample from the previous posts once again. This time, the sample I am attaching is based on a core C++ library, which implements the static Word and Wordcount methods in a simple C++ class.  Here is the sample code, and here is a workbook to use with the code.

You will find an XLL addin, that wraps those two C++ methods with UDFs for Excel client – WehWord and WehWordcount. In addition, you will find a managed UDF assembly, implemented in C++/CLI (“Managed C++â€), which exposes the same two functions as managed UDFs for Excel Services. This time, the wrapper is much simpler; it does not have to deal with mapping .NET types to Excel’s specific interface. E.g. it does not deal with byte-count strings, it does not need to parse XLOPER structures and isolate error cases, and it does not need to handle the XLL way of allocating and releasing memory.

In this sample, the UDF assembly DLL actually embeds the core native function library, so all you need to deploy to the server is this single DLL.

More Excel Services UDF documentation, tips and tricks

• Shahar Prish has lots of good stuff in his blog. He is a distinguished member of the Excel Services developer team, and he really knows what he’s talking about (he actually implemented much of this). Very good reading material. Look under the Excel Services category.

• Office 2007 Beta program participants can find the Office SharePoint Server 2007 SDK documentation on the beta web site. The SDK covers the development of UDFs for Excel Services.