Wrox Home  
Search
Beginning Excel Services
by Liviu Asnash, Eran Megiddo, Craig Thomas
March 2007, Paperback


Excerpt from Beginning Excel Services

Excel Services User-Defined Functions (UDFs)

by Craig Thomas

Excel Services UDFs represent another tier in the Excel Services capability to support custom solutions. UDFs provide a means to call custom-managed code functions from within a workbook. Without UDFs, a workbook is restricted to using only the intrinsic Excel functions. With UDFs, custom functions can be called from the workbook as well.

UDFs are very instrumental to custom server solutions because Excel Services does not support loading workbooks that contain code behind (VBA). Nor does Excel Services support the Excel client add-ins used by workbooks to extend functionality. With the right UDF-managed wrapper solution, you could leverage existing custom client solutions on the server, but that topic isn't discussed here.

This article describes how to author manage UDFs and deploy them to the server to make them available to workbooks that are loaded from the trusted file locations. An example solution is provided to demonstrate the authoring and building of a UDF assembly. Additional material is provided to explain how the workbook interacts with UDF methods, and how to pass and return arguments to the workbook.

UdfMethodAttribute Class

Each public method in the public UDF class must have the [UdfMethod] attribute if the UDF is to be treated as a public UDF. The UdfMethodAttribute has two Boolean properties: IsVolatile and ReturnsPersonalInformation.

The IsVolatile property has a default value of false. When set to true, the UDF method is treated like an Excel volatile function. A volatile function always calculates when any part of a workbook needs to be calculated. UDF volatile methods are called when the Volatile Function Cache Lifetime setting has passed. This setting is defined on the trusted file location where the workbook was loaded.

The ReturnsPersonalInformation property also has a default value of false. When set to false, the thread's Windows identity is hidden, so all callers of the UDF method share the same results cache. When set to true, the UDF method returns results based on the identity, which ensures that callers of the method are not sharing cached values. If a UDF method is expected to return results based on the caller's identity, then the ReturnsPersonalInformation should be true so that each caller gets only their identity-specific results.

Argument Data Types

The supported UDF argument data types are in the .NET System namespace. Excel supports a smaller set of data types that can be applied to data in cells. The following table describes the behavior that you can expect from combinations of UDF argument types and Excel types. The first column represents the UDF argument data type. The remaining columns represent the Excel types that are passed into the UDF through the argument. The contents of the table indicate the error that is returned if the pair is unsupported, or what to expect if an error is not going to be returned.

EXCEL DATA TYPE
UDF ARGUMENT DATA TYPENOTATION DOUBLE STRING BOOLEAN EMPTY
Numeric Tries to cast; Byte and Sbyte return #NUM #VALUE #VALUE 0
String #VALUE String #VALUE String.Empty
Boolean #VALUE #VALUE Boolean False
DateTime Double* #VALUE #VALUE #VALUE
Object Boxed double Reference to a string Boxed Boolean Null

Here is a further explanation of the data types in the table:

  • Numeric — Refers to the following System namespace types: Byte, Double, Int16, Int32, Sbyte, Single, UInt16, and UInt32. The Int64 and UInt64 types are not supported.
  • DateTime* — Internally, Excel treats dates as a double. The ECS converts a DateTime double from Excel into a .NET DateTime.
  • Object — Defines the behavior for each cell in the range that is passed into the array.
  • #VALUE — Can be returned for different reasons, including the following:
  • The Excel type is an error, such as division by zero (#DIV/0!).
  • The UDF argument is an unsupported type, such as Int64.
  • The Excel and .NET type pair is not supported by ECS.
  • The type conversion fails, which can occur for a DateTime type.

Ranges as Arguments

A UDF argument can be either a one-dimensional or two-dimensional array argument. Only object arrays are supported; strong typed arrays are not. #VALUE! is returned if the dimensions of the array argument are insufficient to hold the passed-in range. A single cell range can fit into a one-dimensional array, and a one-dimensional range can fit into a two-dimensional array.

A one-dimensional array can receive a range consisting of a single row. The following UDF method has a single object array argument (xlRow) and returns an integer that represents the number of columns (xlRow.Length) in the array argument. A row is passed in, and the number of columns in that row is returned.

[UdfMethod]
public int ReturnNumberOfColumns(object[] xlRow)
{
   return (xlRow.Length);
}

In Excel, you call the ReturnNumberOfColumns by entering the following code into a cell. Excel evaluates the method as #NAME?. The example noted here uses E5:H5 as the argument to pass in, and 4 is the return value to represent the column count in that range.

=ReturnNumberOfColumns(E5:H5)

A two-dimensional array can receive a range that spans one or more rows. The following UDF method has a single object array argument (xlRange) and returns an integer that represents the number of cells (xlRange) in the two-dimensional array argument. One or more rows are passed in, and the number of cells in that range is returned.

[UdfMethod]
public int ReturnNumberOfCells(object[,] xlRange)
{
   return (xlRange)
}

In Excel, you call the ReturnNumberOfCells method by entering the following code into a cell. Excel evaluates the method as #NAME?. The example noted here uses E5:H6 as the argument to pass in, and 8 is the return value to represent the cell count in that range.

=ReturnNumberOfCells(E5:H6)

Parameter Arrays as Arguments

You can also use a parameter array argument to get values into a UDF. This approach provides the flexibility of passing in a variable number of scalar arguments (such as an int type) or as an object-array type.

A one-dimensional parameter array can receive values or single cell references. The following ReturnNumberOfCellsReceived UDF method has a single params array argument (xlCells) and returns an integer that represents the number of cells passed in through the params array argument:

[UdfMethod]
public int ReturnNumberOfCellsReceived(params int[] xlCells)
{
   return (xlCells.Length);
}

In Excel, you call the ReturnNumberOfCellsReceived method by entering the following code into a cell. Excel evaluates the method as #NAME?. The example noted here provides one value (6) and two cell references (F2 and E5) as the arguments being passed in, and 3 is the return value that represents the number of items (or cells) passed in from Excel.

=ReturnNumberOfCellsReceived(6,F2,E5)

A second way to use parameter arrays as arguments is to create a two-dimensional array argument that can receive multiple ranges. The following ReturnCountOfCellsReceived UDF method has a two-dimensional object array argument (xlArray), and returns an integer that represents the number of cells in the items passed in from Excel.

[UdfMethod]
public int ReturnCountOfCellsReceived(params object[][,] xlArray)
{
   int elements = 0;
   for (int x = 0; x < xlArray.Length; x++)
   {
      elements += xlArray[x].Length;
   }
   return (elements)
}

In Excel, you call the ReturnCountOfCellsReceived method by entering the following code into a cell. Excel evaluates the method as #NAME?. The example noted here provides one value (6), a cell reference (F2), and a range (G2:H3) as the arguments being passed in, and 6 is the return value that represents the number of cells passed in from Excel.

=ReturnCountOfCellsReceived(6,F2,G2:H3)

Return Data Types

The supported UDF return data types are in the .NET System namespace. Excel supports a smaller set of data types that can be applied to data in cells. The following table describes supported return types, as well as the behavior that you can expect from combinations of UDF return types and Excel types. The first column represents the UDF return data type. The second column represents the Excel behavior.

UDF RETURN TYPE EXCEL BEHAVIOR
Numeric Cast to double
String String
Boolean Boolean
DateTime Recognizes the Double as a DateTime
Object[] Type[] Array (first value goes into the first cell, and so on)
Object[,] Type[,] Array (first value goes into the first cell, and so on)
Object Excel tries to map to one of the types noted above and handles it accordingly
Object(Null) Empty/Null String

The Numeric data type refers to the following System namespace types: Byte, Double, Int16, Int32, Sbyte, Single, UInt16, and UInt32. The Int64 and UInt64 types are not supported.

Returning a Range

In addition to returning single-valued data types (also referred to as scalar data types), a UDF can return one- or two-dimensional arrays. Only object arrays are supported; strong typed arrays are not.

A one-dimensional array can hold a range consisting of a single row. The following UDF method has a single object array argument (xlRow) and returns an object array that represents the object that was passed in. A row is passed in and the same row is returned.

[UdfMethod]
public object[] Return1dObjectArray(object[] xlRow)
{
   return (xlRow);
}

In Excel, you call the Return1dObjectArray method by entering the following code into cell C7. To create the array formula, select cells C7:F7, press F2, and then press Ctrl+Shift+Enter. Excel automatically inserts the formula between {} (curly braces). The C7:F7 range represents the cells where the object[] returned from Return1dObjectArray is applied. Excel evaluates the method as #NAME?. The example noted here uses E5:H5 as the argument to pass in. After Return1dObjectArray returns, C7:F7 contains the same values as E5:H5.

=Return1dObjectArray(E5:H5)

A two-dimensional array can receive a range that spans one or more rows. The following UDF method has a single two-dimensional object array argument (xlRange) and returns a two-dimensional object array that represents the object that was passed in. One or more rows are passed in, and the same rows are returned.

[UdfMethod]
public object[,] Return2dObjectArray(object[,] xlRange)
{
   return (xlRange)
}

In Excel, you call the Return2dObjectArray method by entering the following code into cell C11. To create the array formula, select cells C11:E15, press F2, and then press Ctrl+Shift+Enter. Excel automatically inserts the formula between {} (curly braces). The C11:E15 range represents the cells where the object[,] returned from Return2dObjectArray is applied. Excel evaluates the method as #NAME?. The example noted here uses H8:J12 as the argument to pass in. After Return2dObjectArray returns, C11:E15 contains the same values as H8:J12.

=Return2dObjectArray(H8:J12)

Creating a UDF

This section focuses on creating an Excel Services UDF, deploying the managed UDF assembly to the ECS, and calling the UDF methods from an Excel workbook. Here are a few prerequisites that must be met before you get started:

  • Microsoft Office SharePoint Server 2007 must be available.
  • Excel Services must be set up and ready to load workbooks.
  • There must be an Excel 12 client and an ECS trusted location where at least View permissions are set.
  • A Microsoft .Net Framework 2.0 development environment must be set up (The procedures use Microsoft Visual Studio 2005.)
Creating the C# Class Library

Follow these steps to launch Visual Studio and create the C# class library:

  1. Start Visual Studio.
  2. Select File --> New --> Project to open the New Project dialog box.
  3. In the Project Types frame, expand the Visual C# node and select Windows. In the Templates frame, select Class Library.
  4. In the Name field, enter xlUdf.
  5. Set the Location field to the location where you want to create the solution.
  6. Click OK to create the library. The New Project dialog box closes, and Visual Studio opens the xlUdf solution with Class1.cs open and visible. The namespace is xlUdf.
Adding the UDF Run-Time Reference

The Excel Services run-time assembly is installed with each Complete or Stand-Alone (evaluation) type of MOSS installation. The run-time assembly may be available as a download some day, so you might want to search www.Microsoft.com for this. Obtain a copy of the assembly (Microsoft.Office.Excel.Server.Udf.dll) and place it in a location where your project can access it. Ensure that the assembly is compatible with the ECS that will ultimately host the UDF by using either a 32-bit or a 64-bit version of the assembly.

Follow these steps to add the UDF run-time reference:

  1. Select Project --> Add Reference to open the Add Reference dialog box.
  2. Select the Browse tab in the Add Reference dialog box. Navigate to the directory that contains the Microsoft.Office.Excel.Server.Udf.dll run-time assembly, and select the file. Click OK. The Solution Explorer shows the run-time assembly as a reference for the xlUdf solution.
  3. In Class1.cs, add a directive for the run-time assembly namespace. At the beginning of Class1.cs add the following using statement:
using Microsoft.Office.Excel.Server.Udf
Adding Attributes and a Method

Follow these steps to add attributes and a method:

  1. In Class1.cs, add the [UdfClass] attribute to the class. Enter the following on the line immediately preceding public class Class1:
[UdfClass]
  1. Define a UDF method within Class1. Add the following [UdfMethod] attribute to any public UDF method being created:
[UdfMethod]
public string EchoInput(string userInput)
{
   return "Input: " + userInput;
}

The complete solution for the UDF assembly is as follows:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Excel.Server.Udf;
namespace xlUdf
{
   [UdfClass]
   public class Class1
   {
      [UdfMethod]
      public string EchoInput(string userInput)
      {
         return "Input: " + userInput;
      }
   }
}
Deploying the UDF Assembly

UDF assemblies are enabled at the Shared Services Provider (SSP) level. Any UDF method calls in a workbook to a UDF assembly that is not enabled will fail. Each ECS server in an SSP must be able to access all of the enabled UDF assemblies, so ensure that the assemblies are accessible to each ECS that needs access.

Follow these steps to make the UDF assembly accessible:

  1. Build the xlUdf.dll assembly if you have not already done so.
  2. Copy xlUdf.dll to a local folder on the ECS server. For example, place the assembly in D:\Udfs\xlUdf.dll on the ECS server.

Follow these steps to add xlUdf.dll to the list of trusted UDF assemblies for the SSP:

  1. Open the MOSS Central Administration by selecting Start --> All Programs --> Microsoft Office Server --> SharePoint 3.0 Central Administration. The Central Administration page loads in the browser.
  2. Navigate to the trusted UDF assemblies administration page by clicking the SSP name (ShareServices1, for example). Locate the link for the UDF assemblies and click it.
  3. Register and enable the UDF by clicking the Add User-Defined Function Assembly link. The page to add the UDF assembly is displayed.
  4. Enter the assembly full path. For example, enter D:\Udfs\xlUdf.dll. You can use a network share or a local file path. A second option is to add the assembly to the Global Assembly Cache (GAC) and enter the Strong Name here instead of a file path.
  5. If you entered a file path in the previous step, select File Path in the Assembly Location section of the page. If instead you added the UDF assembly to the GAC and entered a Strong Name in the previous step, select Global Assembly Cache.
  6. Click the Assembly Enabled check box to enable the UDF.
  7. Select OK to save the UDF Assembly settings and close the page. The xlUdf.dll assembly is now registered and enabled.
  8. Reset IIS. (This is necessary to enable a workbook to make calls to the UDF.)

Follow these steps to enable UDFs at the trusted file location level:

  1. Click the Trusted File Locations link. The Trusted File Locations page loads and a list of the defined trusted locations is displayed.
  2. Click the trusted location where UDFs are to be supported. The Trusted Location edit page is displayed. Scroll to the bottom and select the User-Defined Functions Allowed option.
Calling the UDF

The syntax you use to make a UDF method call from a workbook is essentially the same as the syntax to call a built-in Excel function. Follow these steps to create the workbook, create a defined name to be used as a parameter, and make a UDF method call that takes the parameter input as an argument:

  1. Create a new workbook by launching Excel 12 and selecting File --> New --> Blank Workbook --> Create.
  2. Create a defined name by selecting Formulas --> Define Name. For the name, enter String_Input and set the Refers To field to =Sheet1!$A$1. Click OK to create the defined name.
  3. Make the UDF method call by selecting cell A3 on Sheet1 and typing the following code.
=EchoInput(String_Input)
  1. Press Enter. The UDF method call evaluates to #NAME? because Excel doesn't know about the UDF method.
  2. Publish the UDF to a trusted file location by selecting File --> Publish --> Excel Services. Type the trusted file location path in the File Name field. Uncheck the Open In Excel Services box to deselect this option. Click Save.
  3. Configure the defined name as a parameter and use the EWA to load the workbook by selecting File --> Publish --> Excel Services. Click the Excel Services Options link to open the Excel Services Options dialog box. Click the Parameters tab and then select Add. Click the check box next to the String_Input entry on the Add Parameters dialog box. Select OK on both the Add Parameters and the Excel Services Options dialog boxes. In the Save As dialog box, select the Open in Excel Services option and then click Save.

A browser launches and displays the EWA with the workbook. There is a Parameters pane on the right side of the EWA, and cell A3 contains Input:.

  1. Enter text in the String_Input parameter field and click Apply. The ECS applies the parameter value to cell A1, calculates cell A3 by passing the String_Input value to the EchoInput UDF method, and then returns the new value for cell A3. The EWA reloads and displays the new values in cells A1 and A3.

ECS XLL UDF

The Excel client supports UDFs in Excel add-ins (or XLLs). This type of UDF implementation is very common for Excel, and many customers have invested heavily in the XLL type of add-in. You can craft a solution that will wrap existing functionality and essentially create a UDF solution for the server. Because Excel Services supports only managed UDFs, the XLL requires a managed wrapper to make the calls to the XLL UDF.

This article is excepted from Chapter 14, "Building Custom Solutions," of Beginning Excel Services (Wrox, 2007, ISBN: 978-0-470-10489-7), by Liviu Asnash, Eran Megiddo, Craig Thomas. Craig Thomas is a Senior Test Lead who works in Redmond, Washington, for Microsoft. For the Office SharePoint Server 2007 release, he is the Release Test Lead for Excel Services. He also leads a team of testers who focus primarily on server performance and reliability. Before joining the Office team, he was contributing to shipping Exchange Server 2003 with a focus on leading a test team responsible for the Outlook Web Access component of Exchange. Thomas discovered his technology passions later in life, after an 11-year career as a submariner in the United States Navy. He has had programming experiences with C and C++ early on, but now prefers C#, and keeps his coding skills fresh by staying involved with tools development, authoring test automation scripts, and, of course, writing custom solutions for Excel Services.