F# function to sort Excel "variants" in Excel-Dna

Question

When trying to sort a 1d array of variants (here by "variant" I mean all the Excel types, eg bool, double (and date), string, various errors...) with the following function :

[<ExcelFunction(Category="test", Description="sort variants.")>]
    let sort_variant ([<ExcelArgument(Description= "Array to sort.")>] arr : obj[]): obj[] =
        arr
        |> Array.sort

I get the following error : Error FS0001 The type 'obj' does not support the 'comparison' constraint. For example, it does not support the 'System.IComparable' interface, probably meaning that there is no generic ordering function available on all obj types.

But Excel has a natural ordering function, which I'd like to emulate (at least ballpark). Eg double (and dates) < string < bool < error...

My question : What is the idiomatic way to sort an array of "variants" in F# / Excel-Dna? (I am after a function which takes an obj[] and return an obj[], nothing else, not a macro...)

My (temporary?) solution : I created a “discriminated union” type

type XLVariant = D of double | S of string | B of bool | NIL of string

(not really sure whether NIL is necessary but it did not hurt. Also in my real life code I added a DT of DateTime instance as I need to distinguish dates from doubles).

let toXLVariant (x : obj) : XLVariant =
    match x with
    | :? double as d -> D d
    | :? string as s -> S s
    | :? bool   as b -> B b
    | _              -> NIL "unknown match"

let ofXLVariant (x : XLVariant) : obj =
    match x with
    | D d   -> box d
    | S s   -> box s
    | B b   -> box b
    | NIL _ -> box ExcelError.ExcelErrorRef

[<ExcelFunction(Category="test", Description="sort variants.")>]
let sort_variant ([<ExcelArgument(Description= "Array to sort.")>] arr : obj[]): obj[] =
    arr
    |> Array.map toXLVariant
    |> Array.sort
    |> Array.map ofXLVariant

(for the sake of simplicity, I missed the Errors types, but the idea is the same)

I don't know Excel-Dna well enough to write a proper answer, but your DU solution looks like a good one to me: by ordering the DU cases to match Excel's natural ordering function (which I didn't know about), you're getting a lot of benefit for not that much code. I'd say that what you've written is a good approach, and I can't think of a way to improve it. — rmunn, May 30 '19 at 14:40
Since the type information is already part of the `obj` value, maybe it's simpler if you just implement `IComparer` with the desired ordering made explicit. — Govert, Jun 01 '19 at 20:17

score 2 · Accepted Answer · answered Jun 01 '19 at 20:50

This seems a bit more explicit to me, since it just sticks to the CLR type system:

// Compare objects in the way Excel would
let xlCompare (v1 : obj) (v2 : obj) =
    match (v1, v2) with
    | (:? double as d1), (:? double as d2) -> d1.CompareTo(d2)
    | (:? double), _ -> -1
    | _, (:? double) -> 1
    | (:? string as s1), (:? string as s2) -> s1.CompareTo(s2)
    | (:? string), _ -> -1
    | _, (:? string) -> 1
    | (:? bool as b1), (:? bool as b2) -> b1.CompareTo(b2)
    | (:? bool), _ -> -1
    | _, (:? bool) -> 1
    | _              -> 2

[<ExcelFunction(Category="test", Description="sort variants.")>]
let sort_variant ([<ExcelArgument(Description= "Array to sort.")>] arr : obj[]): obj[] =
    Array.sortWith xlCompare arr

F# function to sort Excel "variants" in Excel-Dna

1 Answers1