Creating Managed-Code UDFs for Excel Services
Summary: Learn how to create managed-code user-defined functions (UDFs) for Excel Services.
Applies to: Microsoft Office System, Microsoft Office SharePoint Server 2007
Joel Krist, Akona Systems
Much like previous versions of Microsoft Office Excel, Microsoft Office Excel 2007 provides support for user-defined functions (UDFs). UDFs are custom functions that extend the calculation and data-import capabilities of Excel 2007. You can use UDFs to:
In the 2007 Microsoft Office system, you can use Excel Services to build UDFs based on the .NET Framework and deploy these UDFs to Microsoft Office SharePoint Server 2007. These UDFs can be called by Excel workbooks deployed to Excel Services. UDFs can be created to:
This section walks through five major steps to illustrate how to create managed-code UDFs for Excel Services:
1. Create a Managed-Code UDF Project in Visual Studio 2005
You can use the Visual Studio 2005 class library template to easily create an Excel Services managed-code UDF assembly.
To create a managed-code class library project in Visual Studio 2005
Visual Studio 2005 generates a class library project with a single source file in it called either Class1.cs or Class1.vb, depending on the language selected in step 3.
2. Add a Reference to the Excel Services UDF Assembly
Next, add a reference to the Excel Services UDF assembly. If Visual Studio 2005 is running on a computer that has SharePoint Server 2007 installed, do the following:
If Visual Studio 2005 is running on a computer that does not have SharePoint Server 2007 installed on it, the Excel Services UDF assembly is not available. In this case, you can copy the Excel Services UDF assembly from a computer that has SharePoint Server 2007 installed on it to a local project folder on the development computer. The Excel Services UDF assembly is called Microsoft.Office.Excel.Server.UDF.dll and by default is located in the following folder on a computer that has SharePoint Server 2007 installed:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI
After you make a local copy of the Excel Services UDF assembly, you can add a reference to it by browsing for the local file. To add a reference to a local copy of the assembly do the following:
Note that the Microsoft.Office.Excel.Server.UDF.dll is compiled using Microsoft .NET Framework 2.0 assemblies. For this reason, you cannot use Microsoft Visual Studio 2003 to create a managed-code UDF because it is not possible for assemblies created with an older version of the .NET Framework to include a reference to an assembly created with .NET Framework 2.0.
3. Create Custom Functions
After adding the reference to the Excel Services UDF assembly, the next step is to create the custom functions and mark them with the Excel Services UDF attributes. You must mark the class that implements UDF methods with the Microsoft.Office.Excel.Server.UDF.UDFClass attribute and mark the UDF methods with the Microsoft.Office.Excel.Server.UDF.UDFMethod attribute. Next, add the following line to the source file for the UDF assembly project to allow these attributes to be used without having to specify the fully qualified namespace path:
The next step is to mark the class that contains the methods that implement the UDFs with the UdfClass attribute.
The final step is to create the methods that implement the UDFs. Add the following methods to the class just marked with the UdfClass attribute.
<UdfMethod()> _ Function MyDouble(ByVal d As Double) As Double Return (d * 9) End Function <UdfMethod(IsVolatile:=True)> _ Function ReturnDateTimeToday() As DateTime Return (DateTime.Today) End Function
The MyDouble method accepts a parameter of type double and returns the result of that value multiplied by 9. The ReturnDateTimeToday method returns the current date. Both methods are marked with the UdfMethod attribute. Any methods in the UDF assembly that are not marked with the UdfMethod attribute are ignored because they are not considered UDF methods. The UdfMethod attribute has a Boolean IsVolatile property that you can use to specify a UDF method as volatile or nonvolatile. The default value is FALSE, which means that a UDF method is nonvolatile.
4. Configure Excel Services
Configuring Excel Services to work with a managed-code UDF assembly involves two steps:
To create a trusted Windows SharePoint Services location
Excel Services maintains a list of registered UDF assemblies. You can enable or disable each assembly for loading and calling by Excel Services. To register a UDF assembly with Excel Services, do the following on a computer that has SharePoint Server 2007 installed.
To register a UDF assembly with Excel Services
When you register a UDF assembly with Excel Services, one of the Assembly Location options is a file path. A file path location can be a local folder or a network share. To enable the local folder file path option to work in all situations, specifically in a server farm environment where many computers would need to locate the UDF assembly, you can create a SharePoint Services solution package to deploy the UDF assembly to each server computer. For more information regarding creating a SharePoint Services solution package, see How to: Deploy UDFs Using Windows SharePoint Services Solutions.
5. Create and Publish an Excel Workbook That Calls UDFs
Create an Excel workbook that calls the UDFs implemented in the previous section, Create Custom Functions, by doing the following.
Create an Excel workbook that calls UDFs
Cells A1, A2, and A3 will evaluate to "#NAME?" in Excel. This is because Excel cannot resolve the calls to the MyDouble UDF and ReturnDateTimeToday UDF. The formulas are evaluated correctly when the workbook is displayed using Excel Services. For more information regarding the options for creating UDF solutions that work across both client and server, see Developing User-Defined Functions for Excel 2007 and Excel Services.
The final step is to publish the workbook created previously to Excel Services by doing the following.
To publish the workbook
The TestSampleUdf.xlsx workbook loads in Excel Web Access. Cell A1 displays the number "72" because cell B1 * 9 = 8 * 9, which is 72. Cell A2 displays a number and cell A3 displays the current date. The number in cell A2 represents the number of days since 1/1/1900 (or 1/1/1904 if Excel is configured to "Use 1904 Date System"). To change the value passed to the MyDouble UDF that is called in cell A1, specify a different value in the Parameters pane, and then click the Apply button. Excel Services recalculates the workbook.
With the introduction of Excel Services in the 2007 Microsoft Office system, you have a great opportunity to integrate Excel 2007 as a key component to business applications. This article explores how to create managed-code UDFs for Excel Services. The key steps include:
Length: 09:28 | Size: 6.63 MB | Type: WMV file