I already posted about what sort of functions can be created for Excel Services. Now I wanted to add a bit of information about Session State vs. Global State with Excel Services UDFs. As you know, Excel Services allows multiple users to execute multiple workbooks at the same time. For this reason (and others), the Excel Client UDF model does not fit 100% when taken to the server.

On the client, an Addin can listen to events and know what a workbook has been closed and decide to clear some cache items. No such events exist on the server – furthermore, the same workbook can be opened hundreds of time on the server. So what tools do we provide for handling such cases?

As you recall, Excel Services supports only classes that are non-abstract and have a parameterless constructor. The reason behind this is that for every session that will be initialized on the server, we will instantiate an instance of your UDF class. This will allow you to have session-only caching if you choose to.

In the DNS Lookup example I posted about, I showed how we can call Web Services and return the results of these calls to Excel Services. You can easily imagine a case where you would want to cache these results so that consequent calls asking for the same name will not cause an actual Web Service call to be made.

Session Level Caching

Doing session-level caching in your UDF is incredibly simple and straightforward, you simply need to use your UDF class non-static members for storage of the cache. Since a new instance of the class is created for each new session, you will get a fresh copy of the cache each time a user opens a workbook on the server.

When Excel Services is done with the session and needs to close it, it will check to see if your UDF class inherits from IDisposable. If it does, it will make a call to your Dispose() method allowing you to do what clean-ups you deem necessary (though, unless you have native resources, there’s no real reason for you to do this – the GC will take care of everything for you).

Global Caching

For global caching, use static members in your class to store the cached information. Keep in mind though that Excel Services is a server and as such is expected to be up for very long periods of time. You may need to add mechanisms that will age your cache and get rid of memory that is not being used – otherwise, after running for a long while, you may end up choking the server with memory that is not really needed.