Supported data types

Data types in Excel

A relatively small number of data types can be displayed in a cell in Excel

A date is not represented as a data type in itself but is rather just a formatting of a number. The same goes for money, which is also only a number (without any notion of a specific currency).

In addition to these types, a formula in Excel can also return arrays (in one or two dimensions), consisting of a combination of the simple data types.

Data types in XlBridge

Simple types

XlBridge converts the primitive C# types to the suitable Excel data type. The following data types are supported when returning (simple) values from C#.

A method with a return type of any of the above will display properly in Excel. All numeric formats, including DateTime and the integer types will be converted to a (floating-point) number in Excel.

Array types

XlBridge (currently) supports four different return types for arrays.

Returning any of those types will result in an array-formula in Excel. The object array types may only consist of a mix of objects of the simple types.

Error return types

Excel supports a number of error codes, and they are supported in XlBridge by returning a BridgeErrorEnum.

public enum BridgeErrorEnum
{
    Unknown = 0,
    Null,
    Div0,
    Value,
    Ref,
    Name,
    Num,
    Na,
    GettingData,
    Empty,
    Missing
}

Any exception in a call will result in an error of type Unknown.

Object types

XlBridge also supports returning an object. As long as the type of the object is any of the above, it will be processed in exactly the same way as if it was typed explicitly.

An advantage of returning an object in some cases is the abilitiy to return different types depending on the result of the method.

Reference types

If an object return type is not any of the simple types, it will be converted to an object reference and displayed as a text value in Excel. Passing the object reference back to (another) formula defined in C# will convert it back to the original C# object.

XlBridge keeps track of the references in Excel, and will drop the C# reference when no cell uses the reference any more, enabling the .NET Runtime to garbage collect it.

Remark: Keeping track of references returned as a part of an array formula is currently NOT supported due to the design of the Excel API.

Asynchronous return types

Any method returning a Task<T> will be executed asynchronously by Excel. On executing the formula Excel will display #GETTING_DATA in the cell until the task is completed.

Observable return types

Any method returning an (explicit) IObservable<T> will be subscribe to by XlBridge and updates will be posted in Excel.

Remark: The behaviour in Excel from returning array-types from an observable method is sometimes unintuitive, and should be avoided.

Type conversions when calling C# methods

Excel behaviour

Since Excel only supports a limited number of data types in a cell, every value sent to a formula will be either

XlBridge conversions

The XlBridge integration library will (try to) convert all values from Excel to the types defined in the C# method. If formula arguments are entered properly in Excel the type conversion should take place invisibly for the user, including passing the C# objects for any reference types.

Overloaded methods

XlBridge does not support overloaded methods with the same (Excel) name. Since there are so few Excel types, it would be difficult to correctly determine which overload to call for different cases. In certain cases it might work, but not in others.

To support different call signatures custom naming for the Excel function could be used, alternatively a function taking object parameters only could be used together with custom built type conversion and dispatching to different function.

A simple example would be implementing a function like SUM that both accepts a list of numeric values or a list of numeric arrays. An appropiate C# signature would be

double Sum(object number1, params object[] numbers)

Any checking for what type each number is, and how to convert it and handle it is then performed in the custom code.

Read more