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.
Comments
Anonymous
April 04, 2006
>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.
This is sweet, but why isn't it included on the client version of Excel? Automation add-in are a pain and slow, and calling .NET code from an XLL is not the easiest thing.Anonymous
April 06, 2006
A description of what's supported (type wise) in Excel UDFs.Anonymous
April 06, 2006
There are some 3rd party options for creating managed UDFs for the Excel client. The free ExcelDna library is an option (http://exceldna.typepad.com) as is the commercial ManagedXLL project.
For ExcelDna I will consider building compatibility with the Excel Services UDF story - it looks quite easy from what I see so far. If I get it right, this will allow you to use the same assemblies in the Excel client and in Excel Services.Anonymous
April 11, 2006
Excel Services UDFs can use any dirty trick in the .NET book (as long as not blocked by the admin). In this post, we will show how a UDF can greatly increase performance of multiple calls made to web-services.Anonymous
May 02, 2006
Excel Services does not support External Workbook References. I show how you can use a UDF to get similar functionality.Anonymous
August 03, 2006
I have made a few posts about UDFs over the past couple of months. One of the things I neglected to explain...Anonymous
September 01, 2006
Shahar Prish, one of the developers on the Excel Services team, has recently posted a few entries on...Anonymous
September 28, 2006
This is an example ofwhat is possible when web services is baked into the platform.&nbsp; Excel Services...Anonymous
September 28, 2006
Sharepoint 2007 UDF User Defined FunctionsAnonymous
September 28, 2006
Instead of re-hashing information I've found elsewhere I figured a pre-reqs post would be good.
One...Anonymous
October 30, 2006
Instead of re-hashing information I've found elsewhere I figured a pre-reqs post would be good. One ofAnonymous
December 29, 2006
So I just finished coding the solution for my chapter on Excel Services. The more I dig deep in thisAnonymous
August 09, 2007
This is an example ofwhat is possible when web services is baked into the platform. Excel Services inAnonymous
August 14, 2007
Today’s author: Christian Stich, a program manager on the Excel Services team who likes to combine hisAnonymous
August 14, 2007
Today’s author: Christian Stich, a program manager on the Excel Services team who likes to combine hisAnonymous
May 09, 2008
I am trying to call a MOSS web service from Excel UDF with the calling users security context with Trusted Subsystem and i am getting the following error. Can anyone point the correct way without using Delegation mode? Unable to cast object of type 'Microsoft.Office.Excel.Server.CalculationServer.WorkOnBehalfIdentity' to type 'System.Security.Principal.WindowsIdentity'. thanks RathnaAnonymous
July 18, 2011
Great options to add udf in your various excel projects for providing more funtinality into the user excel spreadsheet. Thanks for this nice piece of information to share with us. Take a look here how the things are happening in real time or live www.livevol.com/options-excel.htmlAnonymous
February 10, 2014
How créate a UDF for Excel service SharePoint 2013 that realice this Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) Select Case activeCell.Value Case "hello" SheetViews("Hoja2").Select() End Select Cancel = True End SubAnonymous
December 29, 2015
Hi, I tried the above steps on Sharepoint 2010. However, It is not working for me. Any help will be appreciated !