Excel keeps spreadsheet data in native C structure XLOPER12. If we want to use .NET functions in Excel formulas we need to be able to convert Excel data into .NET function arguments and from .NET function result.

FCell exposes Excel XLOPER12 structure as F# discriminated union XlData. Here is its definition:

F# Copy imageCopy
open FCell.ManagedXll

type XlError =
    | XlErrorNull
    | XlErrorDiv0
    | XlErrorValue
    | XlErrorRef
    | XlErrorName
    | XlErrorNum
    | XlErrorNA 

type XlValue =
    | XlNumeric of float
    | XlString of string
    | XlBool of bool
    | XlErr of XlError
    | XlMissing
    | XlNil

type XlData =
    | XlScalar of XlValue
    | XlVector of XlValue[]
    | XlArray2D of XlValue[,]

XlData can represent a value in 1 Excel cell, values in a column or row, or values in a 2D range. Data in an individual cell is represented as XlValue, which can be numeric, string, boolean, error or missing.

Built-in converters for primitive .NET types

FCell will automatically convert XLOPER12 to/from any of these primitive .NET types:

  • Double (float in F#)

  • Single (float32 in F#)

  • Boolean

  • String

  • Decimal

  • Byte

  • SByte

  • Int16

  • UInt16

  • Int32

  • UInt32

  • Int64

  • UInt64

  • DateTime

  • Enum

Enum type input arguments can be passed from Excel as string or int.

Note

You can pass groups of primitive arguments as ranges, e.g. instead of calling =MyUdf(A1, A2, A3) you can call =MyUdf(A1:A3)

Built-in converters for aggregate types

FCell will automatically convert XLOPER12 to/from any of these aggregate types based on primitive types shown above:

  • 1D Array

  • 2D Array

  • IEnumerable (seq in F#)

  • F# list

  • System.Tuple

  • F# option

  • F# option array, list or seq