Making Excel Services UDFs work on Excel Client - Part 3
In two previous posts on the subject, I explained how developers of server UDFs can leverage COM to make these UDFs work on the client. The second post was made about 6 months ago and I completely forgot that I promised to have one last post about how to make these UDFs volatile on the client (where needed).
For this example, we will use a new UDF method called GetDateTime() . This method will return a string that contains the full date and time string. If we were to implement it according to the previous two posts, it would look like this:
[UdfMethod(IsVolatile=true)]
public string GetDateTime()
{
return DateTime.Now.ToString();
}
The problem is, of course, that when the workbook is recalculated, Excel will not try to recalculate calls to this method because it thinks the value will never change. To make Excel know that the value can, in fact, change, we need to make a call to the Application.Volatile() method.
To make UDFs that are volatile on the server also behave as volatile on the client, your COM class needs to remember to call the .Volatile() method on the Excel Application object. For your class to be able to do that, one first needs to be able have access to the Application object. Gaining access to the Excel Application can be done by implementing the IDTExtensibility2 interface and to do that, your managed library first needs to add the appropriate references.
In Visual Studio, choose the "Add References" option from the project context sensitive menu. In the .NET tab, choose the "extensibility" assembly and click OK. While we are at it, we will also add the Excel PIA (Primary Interop Assembly) to the project - to do that, again go to the Add Reference dialog and choose the "COM" tab and in there, select the "Microsoft Excel 12.0 Object Library" Type Library.
Once this is done, we can finally implement the functionality. First, add the following using declarations to the top of your file:
using Microsoft.Office.Interop.Excel;
using Extensibility;
Next, we need to implement the interface:
[Guid(Class1.ClsId)]
[ProgId(Class1.ProgId)]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class Class1 : IDTExtensibility2
Right-clicking on the interface name in VS2005 will allow you to quickly have a skeleton implementation of all the methods. Make sure you remove all the throw statements and an implementation to the OnConnection() method:
#region IDTExtensibility2 Members
public void OnAddInsUpdate(ref Array custom)
{
}
public void OnBeginShutdown(ref Array custom)
{
}
public void OnConnection(object Application, Extensibility.ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
{
m_app = (Application)Application;
}
public void OnDisconnection(Extensibility.ext_DisconnectMode RemoveMode, ref Array custom)
{
}
public void OnStartupComplete(ref Array custom)
{
}
#endregion
Make sure you add the m_app field to the implementation of the class:
private Application m_app;
That's it. We can now make the call that will cause this UDF to be volatile in the eyes of Excel:
[UdfMethod(IsVolatile=true)]
public string GetDateTime()
{
if (m_app != null)
{
m_app.Volatile(Type.Missing);
}
return DateTime.Now.ToString();
}
It is important to note that we check to see if m_app is null or not before calling Volatile. By doing this, we guarantee that the method will work properly both on server and on client. In the case that we are running on the client, the OnConnection() method will be called, setting a value in the m_app field. In case this code runs on the server, OnConnection() will not be called and m_app will keep being null.
That's it. This is the last post in this series. You should now be able to create UDFs that are consumable by both server and client.
Comments
Anonymous
March 12, 2007
Hi, I have a UDF where the last parameter uses the params keyword. How do I make a com wrapper method for a UDF that has a params parameter? What is the signature? thanksAnonymous
September 07, 2007
Very good article. Just what I need now. Thank you.Anonymous
April 09, 2008
Hin und wieder werde ich gefragt, ob man mit VSTO auch Tabellenfunktionen für Excel schreiben kann. Nun,