How UDFs work in Excel Services - a Primer

In the past few posts, I described the various ways to communicate with Excel Services from without – when you want to do operations such as calculate a workbook, get back values etc. In this post, I will introduce you to how to extend Excel Services from within by adding User Defined Functions (UDFs) that are accessible as cell formulas.

What are Excel Services UDF DLLs

A UDF DLL is a .NET 2.0 Assembly that contains at least one class marked as a UdfClass and at least one method in that class marked as a UdfMethod. A UDF can do anything it wants (as long as the administrator has not applied any CAS to it) – this includes the ability to call into existing native code (so that you can leverage existing code), calling web services and so on and so forth.

When you add a UDF DLL to Excel Services, you are in essence adding functions to the existing Excel functions – this is similar to how XLLs and Automation Addins work in Excel.

In the example I am about to give, we are going to create a UDF DLL that will take a host name as a parameter (“microsoft.com”) and return an IP address (207.46.130.108).

First step – adding reference

The first thing that’s needed is to add a reference to the Microsoft.Office.Excel.Server.Udfs.dll managed assembly so that your program will be able to access the necessary attributes.

To do that on the Office 2007 Beta, you would need to make sure you have the DLL accessible on your development machine. For that, you will need to copy the DLL from the server.

Note: Make sure you do not distribute the DLL with your solution! You only need it so that your application will compile successfully.

To find the DLL on the server, open a command prompt and execute the following command:

 

copy “%windir%\assembly\GAC_MSIL\
Microsoft.Office.Excel.Server.Udf\?
12.0.0.0__71e9bce111e9429c\
Microsoft.Office.Excel.Server.Udf.dll”
<Dev Machine Location>

Second step – Create the UDF project

Next, we will create a “class library” project using your favorite .NET programming language. Don’t forget to Add Reference the DLL we just copied. For the functionality of resolving a host name to an IP address, add a reference to a web service located in: https://www.atomic-x.com/xmlservices/dnslookupservice.asmx?wsdl - call the generated namespace “DnsLookup”.

 

In the project, we will create a new class called MyUdfClass. In that class, we will create a method called ResolveHostName() which takes a string and returns a string:

 

public class MyUdfClass

{

       public string ResolveHostName(string name)

       {

       }

}

 

Next, we will implement this function by making a call into the web-service we added:

 

public string ResolveHostName(string name)

{

       string st;

       using (DnsLookup.DNSLookupService dns = new Blog_DNSUdf.DnsLookup.DNSLookupService())

       {

              st = dns.GetDNSInfoByWebAddress(name).IPAddress;

       }

       return st;

}

 

Lastly, we will mark the class as a UDF class and the method as a UDF method. This will allow Excel Services to find them:

 

using Microsoft.Office.Excel.Server.Udf;

[UdfClass]

public class MyUdfClass

{

       [UdfMethod]

       public string ResolveHostName(string name)

       {

              string st;

              using (DnsLookup.DNSLookupService dns = new Blog_DNSUdf.DnsLookup.DNSLookupService())

              {

                     st = dns.GetDNSInfoByWebAddress(name).IPAddress[0].ToString();

              }

              return st;

       }

}

(Note that we added a using declaration of the Microsoft.Office.Excel.Server.Udf namespace – this allows us to use the UdfClass and UdfMethod attributes without qualifying them).

 

Try compiling your assembly – if you are able to, it means you just created a Udf Dll. Congratulations!

Third Step – So how do I actually use it?

To make the server aware of the UDF dll you just created, we will need to go through some of the administration pages to make sure it’s aware of it.

First, go to the User Defined Functions admin pages and add your DLL. The link is:

https://ServerName:AdminPort/ssp/admin/_layouts/ExcelServerUserDefinedFunctions.aspx

In here, click the “Add User-Defined Function Assembly” button at the top of the page. Then, fill in the fields:

 

 

Don’t forget to change the “Location of the Assembly” to local file.

 

On top of adding the UDF to the list, you will need to enable UDFs on your Trusted Location (that’s where the Workbook that will contain the call to the UDF lives).

To do that, go to your Trusted Location configuration, choose the one you are interested in and scroll to the bottom. You will see a checkbox there instructing Excel Services to enable UDFs on this specific Trusted Location:

 

Creating the workbook

All that’s left is to create the workbook – in this case, we will create a workbook with a parameter that will allow us to query various host names:

 

 

 

Make sure you add a Named Range to the workbook that is B1 so that we can use it as a parameter.

Next, publish the workbook (using the Save As/Excel Services menu item – depending on your Beta, it may be located under a different menu). Make sure you add the named range we just defined as a parameter (in the Excel Services Options dialog box). Also, make sure the “Open this workbook in my browser after I save” checkbox is checked.

Once you save the workbook, it should come up with the parameters task-bar opened. If you put in an address in there, you should see the address resolve:

 

 

So what did we learn

Creating UDFs for Excel Services is incredibly simple. All you need is something that can create .NET 2.0 assemblies and about 5 lines of code. Using UDFs, you can augment the existing Excel Services formulas and add your own.

In future posts, I will show you how to make use of session caching, global caching, async operations and other goodies.