WSS 3.0 and MOSS 2007 tips #5 – Popular Misconceptions – Excel Services UDFs and Excel Client 2007

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.

Click to zoom! Not a valid Excel 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

Click to zoom! Running RegAsm from the command prompt.

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.

Click to zoom! Managing Excel Add-ins in Excel 2007.

Next, select Automation and find your registered Add-in class.

Click to zoom! Automation Add-Ins in Excel 2007.

This *can* cause Excel to complain with the following error:

Click to zoom! Cannot find add-in 'mscoree.dll'. Delete from list?

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.

Click to zoom! Using my Excel Services UDF in Excel Client 2007.

If you’d like the Visual Studio 2008 solution for this example, you can download it here:

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>