F# Excel type provider throws System.InvalidCastException when reading empty cell

Question

I am using the F# Excel Type Provider. The little sample code I took from the documentation works fine until I have an empty cell.

let valueA = row.ColumnA

This then throws System.InvalidCastException

An unhandled exception of type 'System.InvalidCastException' occurred in sample.exe

Additional information: Unable to cast object of type 'System.DBNull' to type 'System.String'.

I have fixed this with this code:

let mutable valueA = Unchecked.defaultof<string>
    try
        valueA <- row.ColumnA
    with ex -> 
        valueA <- String.Empty

This is not very nice code. At least I would like to create a function like this, but then this throws the same InvalidCastException?

let getString (value : Object) =  
     match value.GetType() = typeof<String> with
        | true -> value.ToString()
        | false -> String.Empty     

let valueA = getString row.ColumnA

Edit: It seems that being new to F# I misunderstood what was going on, the Excel Type Provider wasn't returning DbNull, it was actually throwing the exception. So this does fix the problem:

let getString func =  
     try
        func()
     with ex -> 
        String.Empty
let valueA = getString (fun() -> row.ColumnA)

I guess my question now is shouldn't the type provider be retuning the option type so it can handle missing values without throwing exceptions? Or am I still missing something and there is another more elegant way of handling this?

Answer 1

It appears this was actually a bug in the type provider, and it has been fixed in the latest version. It was not supposed to throw an exception when returning data from a perfectly valid excel file, it is returning null now.