Data-Type Handling with Excel Services User-Defined Functions
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
Argument Data Types
Return Value Data Types
Conclusion
Additional Resources
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.
Note
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.
Note
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
.gif "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.
Note
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.
Note
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 type1 | 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
For more information, see the following resources: