Partager via

Making Excel Services UDFs work on Excel 2007 - Part 1

  • Article

I have made a few posts about UDFs over the past couple of months. One of the things I neglected to explain is how to make server targeted UDFs work on the client. Among other reasons why one would want that, is the fact that it’s useful to actually see what the results of your workbook is before you publish it to the server.

This series of posts will concentrate on one of the ways that this can be achieved. While there are ways that are more efficient (perf wise), the one I will present is probably the easiest to do yourself.

 

This post will show how to modify a simple UDF that only takes strings, dates or primitives as parameters and so that it works on the client. The second post in the series will show how more elaborate UDFs (those that take ranges for example) can be modified to work on the client. The third will show one way of supporting volatile UDFs on the client.

 

For this example, lets take a UDF that returns the n’th element in a delimited string. The following call, for example, will return “fox”:

=GetNthWord(“The quick brown fox got hit by a truck”, 3, “ “)

The first parameter is the string we are parsing. The second one is the zero-based index of the word we are looking for and the third parameter is the delimiter we are using.

 

This would be a potential implementation of this UDF:

 

namespace NewUdf

{

       [UdfClass]

       public class Class1

       {

[UdfMethod()]

public string GetNthWord(string sentence, int index, string delimiter)

{

              if (delimiter.Length > 1)

              {

              throw new InvalidOperationException();

              }

             

              string[] splat = sentence.Split(delimiter[0]);

              if (splat.Length <= index)

              {

              throw new InvalidOperationException();

              }

             

              return splat[index];

}

       }

}

 

(Note that this could be made to be slightly more efficient by only asking the .Split() method to do so until the Nth string)

 

Now, as you know if you ever did Excel Services UDF programming, using this UDF inside Excel would result in a #NAME error. However, by changing the file to support COM Interop, it is possible to make it usable by Excel. There are four relatively easy steps that need to be done:

 

1. Decorate the class with attributes so that it can be used by COM:

[UdfClass]

[Guid(Class1.ClsId)]

[ProgId(Class1.ProgId)]

[ClassInterface(ClassInterfaceType.AutoDual)]

[ComVisible(true)]

public class Class1

{

       const string ClsId = "D08A3087-F858-4103-8C52-66B2D2898346";

       const string ProgId = "SampleUdf.Class1";

       //...

}

 

Note: It is important to specify the ClassInterface attribute with AutoDual or Excel will not be able to use the UDF class.

 

2. Make sure that the “Programmable” key is added to the registration of this class in the COM infrastructure. If you don’t do that, you won’t be able to make Excel recognize the UDF class. This is done by adding the ComRegisterFunction and ComUnregisterFunction attributes to the class:

[ComRegisterFunction]

public static void RegistrationMethod(Type type)

{

       // Only add stuff to the registration

       // if it's this class that's being registered.

       if (typeof(Class1) != type)

       {

              return;

       }

       // Add "Programmable" under our key.

       RegistryKey key = Registry.ClassesRoot.CreateSubKey(

              "CLSID\\{" + ClsId + "}\\Programmable");

       key.Close();

}

[ComUnregisterFunction]

public static void UnregisterationMethod(Type type)

{

       // Only add stuff to the registration

       // if it's this class that's being registered.

       if (typeof(Class1) != type)

       {

              return;

       }

       // Add "Programmable" under our key.

       Registry.ClassesRoot.DeleteSubKey("CLSID\\{" + ClsId+ "}\\Programmable");

}

These two functions will be executed by the tool that makes assemblies usable by COM upon registration.

3. We also need to register the assembly for COM usage. To do that, we will make use of the regasm.exe command line tool which is located in your C:\WINNT\Microsoft.NET\Framework\v2.0.50727 directory (the name of the directory may vary of course). To register the assembly, run the following command:

regasm /codebase NewUdf.dll

You will get a warning which you can ignore for the purposes of this post. The UDF is now registered and ready for use in Excel.

4. In Excel 2007 use the File menu to choose the “Excel Options” button. In the dialog that comes up, choose the “Add-Ins” section and at the bottom, make sure “Excel Add-ins” is selected in the combo box at the bottom of the dialog and click “Go”.

Another dialog (“Add-Ins”) will come up. Click the “Automation…” button to bring up a list of automation servers.

The “Automation Servers” dialog will contain a huge list – search for your automation add-in there. The name will be [Namespace].[ClassName] (in our case, NewUDF.Class1).

Select that automation server and click “OK”. You will get a warning message about mscoree.dll – ignore it and click “No”.

Excel should now be ready to use the newly created UDF. Typing that same formula that we did before should now result in the word “fox” in the cell.

In the next post, I will talk about how to make UDFs that expect object[] or object[,] work.

Comments

  • Anonymous
    September 01, 2006
    Shahar Prish, one of the developers on the Excel Services team, has recently posted a few entries on...

  • Anonymous
    September 17, 2006
    Well done!
    [url=http://kocnsmfs.com/rpul/kttd.html]My homepage[/url] | [url=http://jodzsbcg.com/unal/trei.html]Cool site[/url]

  • Anonymous
    September 17, 2006
    Great work!
    <a href="http://kocnsmfs.com/rpul/kttd.html">My homepage</a> | <a href="http://kygqdbev.com/uugv/udtl.html">Please visit</a>

  • Anonymous
    September 28, 2006
    Instead of re-hashing information I've found elsewhere I figured a pre-reqs post would be good.
    One...

  • Anonymous
    January 02, 2007
    Does this work in excel cleints installed outside sharepoint environment? for example on excel 2007 running on xp

  • Anonymous
    January 02, 2007
    It should, yeah. If you have any specific problems, let me know.

  • Anonymous
    January 21, 2007
    hi salis. Could you post the screen shot  of "File menu "t and “Excel Options” ? I can't find them in excel 2007

  • Anonymous
    January 21, 2007
    hi salis. Could you post the screen shot  of "File menu "t and “Excel Options” ? I can't find them in excel 2007

  • Anonymous
    January 22, 2007
    In Excel 2007, you will see a circle at the top right corner. It may even blink at you - it has the Office Logo on it. Click it. At the bottom of the menu that comes up there's an "Excel Options" button. Click that.

  • Anonymous
    February 07, 2007
    How did you come up with the Class ID as below, did you make it up? const string ClsId = "D08A3087-F858-4103-8C52-66B2D2898346";

  • Anonymous
    February 07, 2007
    You will need to generate your own using the Guidgen tool (or the UI GUID generator you can easily find in the Tools menu option in Visual Studio)

  • Anonymous
    February 07, 2007
    thanks, while i still can't get the function to work properly, I need to be able to replace the dll. How do i get the system from locking the dll, even after unregistering it?

  • Anonymous
    February 07, 2007
    Exit Excel.

  • Anonymous
    February 13, 2007
    Hi I have followed all th steps mentioned but while opening the excel sheet in Excel am getting following error. "values does not fall on the esxpected range". Is there any restrictions like we need to select the range in excel workbook exactly same as the columns and rows of sharepoint list and call the function. will it be a problem if i select less or more than the list dimensions.Please let me know about this as i got stuck here and not to display the list in excel workbook. Thanks,

  • Anonymous
    February 13, 2007
    Can you post your code? (Just the code that builds the result you send back from the UDF)

  • Anonymous
    February 13, 2007
    Thanks shahar, I am very happy that my problem got resolved as i was not passing parameters in calling t he UDF correctly..

  • Anonymous
    April 09, 2008
    Hin und wieder werde ich gefragt, ob man mit VSTO auch Tabellenfunktionen für Excel schreiben kann. Nun,

  • Anonymous
    February 19, 2010
    I followed the steps (and received all of the responses that were listed, such as warnings) in this article but I still receive the "#NAME?" value in Excel? Any ideas on what I may have missed?

  • Anonymous
    February 19, 2010
    Not really Jason - as long as you turned on UDF for your trusted location, this should just work. You can look at the logs to make sure UDFs were loaded and you can also use a deugger to debug into w3wp.exe to see if your UDF is actually loaded.

  • Anonymous
    February 19, 2010
    After reading your response, I can see how my original post may not have been the most clear. I have the UDF working when viewing the spreadsheet on the server.  It is not working when viewing the spreadsheet on the client.

  • Anonymous
    February 19, 2010
    Jason: Take a look here: http://blogs.msdn.com/cumgranosalis/archive/2006/08/03/ServerClientUDFsCompat1.aspx#9966505

  • Anonymous
    March 02, 2010
    Like Jason, I get the "#NAME?" value in Excel Client.  I have followed all the steps listed in the article. Any ideas what I am missing?

  • Anonymous
    March 02, 2010
    Sandeep - what's the signature of your UDF?

  • Anonymous
    March 03, 2010
    I found the problems I was having.

  1.  The AssemblyInfo.cs ComVisible property for my assembly was set to false, I had to change it to true.
  2.  Some of my methods return type was void, I had to change these to return either bool or string. These two fixes solved the problem. Thanks for your help. Sandeep.