Back in my first blog posting on WSS 3.0 and MOSS 2007, I discussed Excel Services and User Defined Functions. Since then I have had a number of requests for a follow up post. A common misconception from students in my SharePoint developer courses is this:
Popular Misconception #1: “I can’t test my UDFs in Excel! When I write a UDF class and method, my spreadsheet works on the server, but I get #NAME? showing up when I try to build my spreadsheet in Excel on my desktop.”
This is kind of true. A UDF is just a .NET assembly that has bee appropriately attributed to mark the Class and Methods as discoverable and usable by Excel Services. You deploy this to Excel Services by installing it in the GAC on the server and registering it in the Shared Service Provider (SSP) for Excel Services.
Excel Client 2007, on the other hand, does not know what to do with these .NET assemblies. You can try adding these .DLLs as Excel Add-Ins or COM Add-Ins and Excel will complain that your assembly is not a valid add-in.
So how do we work around this? Some say you can wrap your .NET assembly in a .XLL library written in C++. Some say there are third party wrappers that will do this for you at a premium.
You don’t need to go that far.
Let’s have a look at a simple (trivially so) UDF for Excel Services.
1: namespace JoelsUDF
2: {
3: // Mark this class as an Excel Services UDF
4: [UdfClass]
5: public class JoelsUDFClass
6: {
7: #region UDF bits
8: /// <summary>
9: /// My very complex UDF method...
10: /// </summary>
11: /// <param name="value">Some number.</param>
12: /// <returns>Passed in value inc
remented by one.</returns>
13: // Mark this method as an Excel Services UDF method
14: [UdfMethod]
15: public double AddOne(double value)
16: {
17: return value + 1;
18: }
19: #endregion
20: }
21: }
Very, very simply, this marks the class as a UdfClass, with a single UdfMethod that takes a double and returns the value incremented by one. This can be signed and deployed into Excel Services using the method described in my previous post on Excel Services and UDFs.
If I now want to deploy this same assembly into Excel Client 2007 as an add-in, I could add a few lines of COM registration code. Once again, don’t forget to sign your assembly!
1: namespace JoelsUDF
2: {
3: // Mark this class as visible in COM
4: [ProgId(JoelsUDFClass.ProgID)]
5: [Guid(JoelsUDFClass.ClassID)]
6: [ClassInterface(ClassInterfaceType.AutoDual)]
7: [ComVisible(true)]
8:
9: // Mark this class as an Excel Services UDF
10: [UdfClass]
11: public class JoelsUDFClass
12: {
13: #region COM bits
14: // COM ClassID (GUID) and ProgID (Type Name)
15: const string ClassID = "81FCB6CB-E4F1-4728-A6A7-8BBA85D0B5EA";
16: const string ProgID = "JoelsUDF.JoelsUDFClass";
17:
18: // Registers COM class in the registry
19: [ComRegisterFunction]
20: public static void RegistrationMethod(Type type)
21: {
22: if (typeof(JoelsUDFClass) == type)
23: {
24: RegistryKey regKey = Registry.ClassesRoot.CreateSubKey(
25: "CLSID\\{" + ClassID + "}\\Programmable");
26: regKey.SetValue("", System.Environment.GetFolderPath(
27: Environment.SpecialFolder.System) + @"\mscoree.dll");
28: regKey.Close();
29: }
30: }
31:
32: // Unregisters COM class from the registry
33: [ComUnregisterFunction]
34: public static void UnregistrationMethod(Type type)
35: {
36: if (typeof(JoelsUDFClass) == type)
37: Registry.ClassesRoot.DeleteSubKey("CLSID\\{" +
38: ClassID + "}\\Programmable");
39: }
40: #endregion
41: #region UDF bits
42: /// <summary>
43: /// My very complex UDF method...
44: /// </summary>
45: /// <param name="value">Some number.</param>
46: /// <returns>Passed in value incremented by one.</returns>
47: // Mark this method as an Excel Services UDF method
48: [UdfMethod]
49: public double AddOne(double value)
50: {
51: return value + 1;
52: }
53: #endregion
54: }
55: }
I’d then need to register the assembly with RegAsm.exe (from the %SystemRoot%\Microsoft.NET\Framework\v2.xxxx folder).
RegAsm.exe /codebase UDFExample.dll
Finally I’d add it to Excel as a Excel Automation add-in. Under Excel Client 2007, this is available in File –> Excel Options –> Add-Ins, then select Manage: Excel Add-ins.
Next, select Automation and find your registered Add-in class.
This *can* cause Excel to complain with the following error:
It’s important that you say “no” to this.
Then you can start using your UDFs in Excel as if they were built in functions.
If you’d like the Visual Studio 2008 solution for this example, you can download it here:
Technorati Tags: Excel, Excel Add-ins, Excel Services, MOSS 2007, Office Server, SharePoint, SharePoint Architecture, UDF, User Defined Functions
Filed Under : Training by Joel Jeffery
Jul.15,2009
Tags : 
