Export (0) Print
Expand All
Expand Minimize

Data-Type Handling with Excel Services User-Defined Functions

SharePoint 2007

Summary: Learn how to handle data types when working with Excel Services user-defined functions (UDFs). (6 printed pages)

Danny Khen, Microsoft Corporation

Shahar Prish, Microsoft Corporation

January 2007

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

Contents

Overview of Data-Type Handling with Excel Services User-Defined Functions

Microsoft Office Excel 2007 is not strict in how it converts argument and return-value types. Excel Services user-defined functions (UDFs), however, are much stricter in their conversions because Excel Services UDFs are based on the Microsoft .NET Framework. This article discusses the types that are supported in Excel Services, and how Excel Services UDFs behave for those supported types in conjunction with various Excel types.

Bb226682.note(en-us,office.12).gifNote:

Excel Services is a new server technology included in Microsoft Office SharePoint Server 2007.

Argument Data Types

This section discusses the data types, type errors, arrays, ranges, and parameter arrays for argument data types that Excel Services supports.

About Supported Types

The following table shows the pairing between an Excel type value that is passed to a UDF argument and a supported .NET Framework type argument.

The table explains how each pair behaves, that is, what gets passed to the UDF in the argument. If a pair is not supported, the table shows the cell error that is returned to Excel. If the .NET Framework type is not supported, the UDF is not called.

For example, if you pass an Excel Double value type to an Excel Services UDF argument for the DateTime value type, the table shows how Excel Services converts the Excel representation of date/time into the .NET Framework representation. In this example, Double is converted to DateTime.

As another example, the combination of a UDF argument of type DateTime with an Excel cell of type Error (a cell that contains #NUM, #VALUE, #DIV/0, and so on) returns a #VALUE error as the UDF return value, because the Excel cell and the UDF argument type are incompatible.

Bb226682.note(en-us,office.12).gifNote:

Boxing and unboxing enable value types to be treated as objects. For more information about boxing and unboxing, see Boxing and Unboxing.

Table 1. Supported Excel types and UDF arguments

Office 2007 Excel Services UDFs

1. Types are in the System namespace. These are the only supported argument types.

2. The term "All numeric" refers to the following types: Double, Single, Int32, UInt32, Int16, UInt16, Byte, Sbyte. Specifically, Int64 and UInt64 are not supported types.

3. Date/time in Excel is a Double internally, but the representation (algorithm to encode date/time as Double) is different from the .NET Framework representation, and conversion is necessary. Therefore, UDF arguments support DateTime as an explicit type, and Excel Services convert any Excel Double into a .NET DateTime when the argument is of type DateTime (assuming that the Excel Double is actually a date/time value).

4. Scalar object arguments are not supported; only one-dimensional or two-dimensional object arrays are supported (see the "Arrays and Ranges" later in this article). This table row defines only how each cell in the range that is passed into the array is handled.

Type Errors

The following type errors can occur:

  • Conversion errors   If, because of its size, a formula value that is passed into a UDF argument cannot be converted to the argument type, the UDF call fails, and a #NUM! error is placed in the result cell, cells, or formula.

  • Type mismatches   If a type mismatch occurs between the formula value and the argument type, the UDF call fails, and a #VALUE! error is placed in the result cell, cells, or formula. A type mismatch means that the Excel/.NET Framework type pair is not supported by Excel Services. The same is true for a value mismatch, for example, if type conversion fails (for example, for DateTime) because the value cannot be converted to the destination type.

  • Unsupported type errors   If a UDF argument is of an unsupported type, the UDF call fails, and a #VALUE! error is placed in the result cell, cells, or formula.

Arrays and Ranges

Using Excel Services UDFs, you can work with the following arrays and ranges:

  • Object arrays   For Excel Services UDFs with one-dimensional or two-dimensional Object array arguments, only arrays of type Object are supported; arrays of strong types are not supported. In Microsoft Visual C#, object[] and object[,] arguments are supported.

  • Contiguous Excel ranges   Only contiguous Excel ranges can be passed as arrays.

  • Scalar argument   The type Object is supported only in array arguments, not in scalar arguments.

  • One-dimensional arrays   One-dimensional arrays are treated as two-dimensional arrays with a single element in the other dimension. They can receive a range of cells in a single row in Excel, that is, their index represents a column number.

  • Two-dimensional arrays   Two-dimensional arrays can receive an Excel range, such that the first index designates a row and the second index designates a column. For more information, see Multidimensional Arrays (C# Programming Guide). In Visual C#, for example, if a two-dimensional argument array is defined as int[,] a, then you would index it as a[row,col].

  • Passed single value   If you pass a single value, an array is created, with the size of (1) or (1,1), and the value is packed in it.

  • Passed range   Excel Services copies the range contents into an array that fits the description. If the rank (dimensionality) of the array argument does not fit in the dimensionality of the range that is passed in, #VALUE is returned.

    Bb226682.note(en-us,office.12).gifNote:

    A single cell always fits within a one-dimensional or two-dimensional array. A one-dimensional array always fits within a two-dimensional array.

Parameter Arrays

Excel Services supports UDFs that define parameter arrays.

Bb226682.note(en-us,office.12).gifNote:

Parameter arrays enable you to pass a variable number of scalar arguments that appear to the method as elements of an array.

Parameter arrays can only be typed (as the parameters) or as an object-array type, as shown in the following example.

int ElipsisMethod1(params int[] intparams)
int ElipsisMethod2(params object[][,] arrayparams)

Return Value Data Types

This section discusses the supported data types, type or value errors, arrays, and ranges for return value data types.

Supported Types

The following table defines the supported UDF return value types. For each supported type, Excel Services passes a VARIANT of a certain type to the Excel cell or formula. The VARIANT type is defined in the following table. Excel then sets the VARIANT value into the cell or formula.

Table 2. Supported UDF return value types

Supported UDF return value type 1 VARIANT type passed to Excel

All numeric2

Cast to Double.

String

String.

Boolean

Boolean.

DateTime

Excel Double date representation, to the extent that the value is supported. Excel supports from 1/1/1900 to 12/31/9999 (more accurately, up to the double value 2958465.9999); .NET Framework DateTime supports 1/1/0001 to 12/31/9999 (more accurately, up to the double value 2958465.99999999). For any .NET Framework DateTime value that is outside of the supported Excel range, #NUM! is returned.

Object[]

Type[]

Array formula. As with most arrays or ranges returned in Excel, if applied to a single cell, the first value in the array (index 0) goes into the cell.

Object[,]

Type[,]

Array formula. As with most arrays or ranges returned in Excel, if applied to a single cell, the first value (col =0, row = 0) goes into the cell.

Object

Excel Services determines if the object is of one of the types described previously, and behaves accordingly.

Object (Null)

Treated as an empty or null string.

1. Types are in the System namespace. These are the only supported return value types.

2. The term "All numeric" refers to the following types: Double, Single, Int32, UInt32, Int16, UInt16, Byte, Sbyte. Specifically, Int64 and UInt64 are not supported types.

Type/Value Errors

For an unsupported return value type, or an unsupported return value, the UDF call fails, and a #VALUE! error is placed in the result cell, cells, or formula.

Arrays and Ranges

You can work with the following arrays and ranges:

  • Object arrays   Excel Services support UDFs that return a one-dimensional or two-dimensional .NET Framework array. Such UDFs can be used in array formulas.

  • Single cells  As with most arrays or ranges returned in Excel, if applied to a single cell, the first value in the array (index 0 for one-dimensional arrays and indexes 0,0 for two-dimensional arrays) goes into the cell.

  • One-dimensional arrays   One-dimensional arrays are treated as two-dimensional arrays with a single element in the other dimension. These arrays map to a range of cells in a single row in Excel, that is, their index represents a column number.

  • Two-dimensional arrays   Two-dimensional arrays map to an Excel range, such that the first index designates a row, and the second index designates a column. For example in Visual C#, you would define a two-dimensional array to be returned by a UDF in the following way.

    int[,] a = new int[nRows,nCols];
    

Conclusion

This article describes how to handle data types when working with Excel Services UDFs. It also discusses the supported data types, type or value errors, arrays, ranges, and parameter arrays that are related to argument and return value data types.

About the Authors

Danny Khen is a program manager on the Microsoft Office Excel team. He worked on the programmability functionality for Excel Services in Office SharePoint Server 2007.

Shahar Prish is a senior developer on the Excel Services team. He blogs (Blog: Shahar Prish) about Excel Services, among other subjects.

Additional Resources

Community Additions

ADD
Show:
© 2014 Microsoft