Excel Services User-Defined Functions (UDFs)

Excerpt from Beginning Excel Services

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
<font size="2">Numeric</font>
Tries to cast;

<font size="2">Byte</font>

and

<font size="2">Sbyte</font>

return #

<font size="2">NUM</font>
<font size="2">#VALUE</font>
<font size="2">#VALUE</font>
<font size="2">0</font>
<font size="2">String</font>
<font size="2">#VALUE</font>
<font size="2">String</font>
<font size="2">#VALUE</font>
<font size="2">String.Empty</font>
<font size="2">Boolean</font>
<font size="2">#VALUE</font>
<font size="2">#VALUE</font>
Boolean
<font size="2">False</font>
<font size="2">DateTime</font>
Double*
<font size="2">#VALUE</font>
<font size="2">#VALUE</font>
<font size="2">#VALUE</font>
<font size="2">Object</font>
Boxed double Reference to a string Boxed Boolean
<font size="2">Null</font>

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
<font size="2">Numeric</font>
Cast to double
<font size="2">String</font>
String
<font size="2">Boolean</font>
Boolean
<font size="2">DateTime</font>
Recognizes the Double as a

<font size="2">DateTime</font>
<font size="2">Object[] Type[]</font>
Array (first value goes into the first cell, and so on)
<font size="2">Object[,] Type[,]</font>
Array (first value goes into the first cell, and so on)
<font size="2">Object</font>
Excel tries to map to one of the types noted above and handles it accordingly
<font size="2">Object(Null)</font>
Empty/

<font size="2">Null</font>

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

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *