Share via

Creating Managed-Code UDFs for Excel Services

  • Article

Summary:  Learn how to create managed-code user-defined functions (UDFs) for Excel Services.

Office Visual How To

Applies to:  Microsoft Office System, Microsoft Office SharePoint Server 2007

Joel Krist, Akona Systems

April 2007

Overview

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:

  • Provide functionality that is not available with the native Excel function library.

  • Build custom data feeds for legacy or unsupported data sources, and application-specific data flows.

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:

  • Call custom mathematical functions.

  • Get data from unsupported data sources such as legacy databases, Microsoft Office Access, and Microsoft SQL Server into worksheets.

  • Call Web services.

Code It

Download the Code Sample

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 Microsoft Visual Studio 2005.

  2. Add a reference to the Excel Services UDF assembly.

  3. Create custom functions.

  4. Configure Excel Services to allow UDFs to be used.

  5. Create and publish an Excel workbook that calls UDFs.

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

  1. Start Visual Studio 2005.

  2. On the File menu, select New, and then click Project. The New Project dialog box appears.

  3. In the Project Types pane, select Visual C# or Visual Basic.

  4. In the Templates pane, click Class Library.

  5. Specify a Name and Location for the project and click OK.

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:

  1. Right-click the project in the Visual Studio Solution Explorer and select the Add Reference… menu item.

  2. Select the .NET tab in the Add Reference dialog box, then scroll down to the Excel Services UDF Framework component, select it, and then click OK to add the reference.

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:

  1. Right-click the project in the Visual Studio Solution Explorer and select the Add Reference menu item.

  2. Select the Browse tab in the Add Reference dialog box and then navigate to the local folder that contains the copy of the Excel Services UDF assembly. Select it, and then click OK to add the reference.

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:

using Microsoft.Office.Excel.Server.Udf;

Imports Microsoft.Office.Excel.Server.Udf

The next step is to mark the class that contains the methods that implement the UDFs with the UdfClass attribute.

[UdfClass]
public class Class1

<UdfClass()> Public Class Class1

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]
public double MyDouble(double d)
{
    return (d * 9);
}

[UdfMethod(IsVolatile = true)]
public DateTime ReturnDateTimeToday()
{
    return (DateTime.Today);
}

<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:

  1. Create a trusted file location.

  2. Register the UDF assembly.

  3. A trusted file location is a Microsoft Windows SharePoint Services location, network file share, or Web folder from which a server running Excel Services is permitted to access workbooks. To create a trusted Windows SharePoint Services location, do the following on a computer that has SharePoint Server 2007 installed.

To create a trusted Windows SharePoint Services location

  1. On the Start menu, click All Programs.

  2. Point to Microsoft Office Server and click SharePoint 3.0 Central Administration.

  3. On the Quick Launch, click the shared services provider (SSP) link—for example, "SharedServices1"—to view the Shared Services home page for that particular SSP.

  4. On the Shared Services home page, in the Excel Services Settings section, click Trusted file locations.

  5. On the Excel Services Trusted File Locations page, click Add Trusted File Location.

  6. On the Excel Services Add Trusted File Location page, in the Address box, type the location where the Excel workbook that calls UDFs will be saved to—for example, http://MyServer002/Shared%20Documents.

  7. Under Location type, click the appropriate location type. In this example, select Windows SharePoint Services.

  8. Under Trust Children, select Children trusted to trust child libraries or directories.

  9. Under Allow User-Defined Functions, select User-defined functions allowed to allow UDFs to be called from workbooks stored in this trusted location.

  10. Click OK.

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

  1. On the Start menu, click All Programs.

  2. Point to Microsoft Office Server and click SharePoint 3.0 Central Administration.

  3. On the Quick Launch, click the shared services provider (SSP) link—for example, "SharedServices1"—to view the Shared Services home page for that particular SSP.

  4. Under Excel Services Settings, click User-defined function assemblies.

  5. On the Excel Services User-Defined Functions page, click Add User-Defined Function Assembly to open the Excel Services Add User-Defined Function Assembly page.

  6. In the Assembly box, type the path to the UDF assembly that contains the code created previously in the Create Custom Functions section.

  7. Under Assembly Location, select the appropriate location type.

  8. Under Enable Assembly, the Assembly enabled check box should be selected by default.

  9. Click OK.

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

  1. Start Excel 2007.

  2. In cell A1, type the formula to call the MyDouble UDF. The MyDouble function takes an argument of type double. This example takes the argument from cell B1. In cell A1, type =MyDouble(B1).

  3. In cell B1, type the number 8.

  4. Make cell B1 a named range. First click the Formulas tab. Then click cell B1 to select it. On the Formulas tab, in the Defined Named group, click Define. In the New Name dialog box, in the Name box, type MyDoubleParam.

  5. In cell A2, type the formula to call the ReturnDateTimeToday UDF. Type =ReturnDateTimeToday().

  6. In cell A3, type the formula to call the ReturnDateTimeToday UDF. Type =ReturnDateTimeToday(). Next, right-click cell A3 to display the menu. Click Format Cells.

  7. In the Format Cells dialog box, on the Number tab, select Date. Select a date format type from the Type list—for example, *3/4/2001.

  8. Click OK.

  9. Save the workbook to a folder on a local drive. Name the workbook "TestSampleUdf.xlsx".

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

  1. Click the Microsoft Office Button, point to Publish, and click Excel Services.

  2. In the Save As dialog box, click Excel Services Options.

  3. In the Excel Services Options dialog box, on the Show tab, make sure that Entire Workbook is selected.

  4. Click the Parameters tab.

  5. Click the Add button.

  6. In the Add Parameters dialog box, select the MyDoubleParam check box.

  7. Click OK. "MyDoubleParam" is now listed in the Parameters list.

  8. Click OK.

  9. In the Save As dialog box, make sure that the Open in Excel Services check box is selected.

  10. In the File name box, type the path to the trusted SharePoint document library where this workbook will be stored. For example, http://MyServer002/Shared%20Documents/TestSampleUdf.xlsx.

  11. Click Save.

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.

Read It

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:

  1. Create a managed-code UDF project in Visual Studio 2005.

  2. Add a reference to the Excel Services UDF Framework.

  3. Create custom functions that provide the UDF implementation.

  4. Configure Excel Services to allow UDFs to be used.

  5. Create and publish an Excel workbook that calls UDFs.

 See It Thumbnail of Creating Managed-Code UDFs Video

Watch the Video

Length: 09:28 | Size: 6.63 MB | Type: WMV file

Explore It