CODEWITHSUNDEEP
sortingf#excel-dna
Janthelme
Janthelme

Reputation: 999

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

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)

Upvotes: 3

Views: 387

Answers (1)

Govert
Govert

Reputation: 16907

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

Upvotes: 2

Related Questions