Excel Services Getting Started – Pt2 UDFs

This continues my previous checklist on getting started with Excel Services. Here, I’ll append a walkthrough for creating, deploying and updating User-Defined Function assemblies with Excel Services.

4. Create and deploy a UDF assembly

4.1 Create a UDF assembly in Visual Studio (SampleUdf.dll)

a. I’m assuming here that your developer machine is not the same as your Excel Server machine. When you build UDFs, you do need to reference an assembly that is part of Excel Services, but which is unlikely to be available on your dev machine.

b. For simplicity, take a copy of the Microsoft.Office.Excel.Server.Udf.dll from the Excel Server machine, and put it onto your dev machine, so that you can reference it locally. On the Excel Server machine, this DLL is in the GAC, typically here: C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Excel.Server.Udf\12.0.0.0__71e9bce111e9429c\. Copy this to a suitable location on your dev machine.

c. In Visual Studio, create a simple Class Library project. Add a reference to Microsoft.Office.Excel.Server.Udf.dll (and set the Copy Local property to false). You need this for the UdfClass and UdfMethod attributes. Add a suitable using statement for the Microsoft.Office.Excel.Server.Udf namespace.

d. Create a simple class with two methods, using the UdfClass and UdfMethod attributes, eg:

[UdfClass()]

public class MyUdfs

{

    [UdfMethod]

    public double GetDouble(double d)

    {

        return d * 2;

    }

    [UdfMethod(IsVolatile = false)]

    public DateTime GetStaticTime()

    {

        return DateTime.Now;

    }

    [UdfMethod(IsVolatile = true)]

    public DateTime GetVolatileTime()

    {

        return DateTime.Now;

    }

}

e. In the example above, there are 2 non-volatile methods: GetDouble and GetStaticTime, and one volatile method, GetVolatileTime. GetStaticTime will often return the current time, but Excel Services makes performance optimizations by caching return values of non-volatile methods. So, sometimes Excel Services will return a cached value for GetStaticTime instead of calling the method again. On the other hand, GetVolatileTime will be called every time anything on the sheet is recalculated and re-rendered to the client.

f. Build the DLL.

4.2 Deploy the UDF assembly to Excel Services

a. Copy the UDF DLL from your dev machine to some suitable location on the server machine, eg: “C:\Data\UDFs”. You can deploy UDFs either to an arbitrary file location, or to the GAC. Note that normal probing rules apply, so any dependencies need to be either in the same folder tree as the primary UDF assembly or in the GAC.

b. On the SharePoint Server machine, in Central Administration, select the Application Management tab at the top.

c. Click the SSP link for your Excel Services SSP (by default, “SharedServices1”) to go to the administration page for that SSP.

d. On the Shared Services Administration page, under Excel Services Settings, select the User-defined function assemblies link.

e. Click the Add User Defined Function Assembly option.

f. In the Add User-Defined Function Assembly page, specify the path where you copied the UDF DLL to on the server, eg: “C:\Data\UDFs\SampleUdf.dll”.

g. Specify that this is a File path location, and ensure that the Assembly enabled option is checked. Click OK.

h. The UDF should now be listed on the Excel Services User-Defined Functions page for the SSP.

4.3 Test the UDF assembly (TestSampleUdf.xlsx)

a. Create a new Excel workbook. In Column A, enter labels for the 3 UDFs: Double, StaticTime and VolatileTime. In Column B, enter formulas that call each of these methods:

b. Note that Excel client will return #NAME? for these UDF calls because it cannot find the UDFs on the client. This is expected.

c. Note also that the GetDouble call uses cell C1 as a parameter. Define a name for cell C1, eg: “DoubleParam”.

d. Publish the workbook to Excel Services as normal. Only publish Sheet1, and add a parameter for the DoubleParam cell.

e. When the workbook is published, it is rendered in the browser. You can enter new values for the DoubleParam, and when you click Apply, Excel Services will recalculate the sheet and re-render it.

f. Note that whenever GetDouble is recalculated, so is GetVolatileTime. On the other hand, Excel Services will often return a cached value for GetStaticTime.

4.4 Update the UDF assembly

a. Excel Services loads UDF assemblies when it needs to look for a UDF in a sheet that it is calculating. It only loads each assembly once, and never unloads any assemblies. This makes it difficult to develop iteratively.

b. To force Excel Services to release its lock on a UDF assembly, you can simply recycle the Excel Services application pool.

c. This simple command will recycle the pool:

cd %windir%\system32\inetsrv

appcmd recycle apppool "OfficeServerApplicationPool"

pause

d. You can find the name of the app pool used by Excel Services in IIS Manager, under Application Pools.

e. If necessary, you can also reset IIS altogether, although this is obviously pretty draconian:

net stop iisadmin

rem respond Y

net start w3svc

pause

I hope this continuation checklist is useful – as before, it simply collects together and distils information from a range of documentation on MSDN.