f# read xls file - how to parse a value2 object

Question

I tried to use F# read a xls file as below

open Microsoft.Office.Interop.Excel
let app = ApplicationClass(Visible = false)
let book = app.Workbooks.Open "test.xls"
let sheet = book.Worksheets.[1] :?> _Worksheet
let vals = sheet.UsedRange.Value2

The problem is how can I parse vals into a F# type? in fsx.exe, the vals showed as

'val vals: obj = [bound1
                  bound2
                  ["colname1"; "colname2"; ...]
                  [1234,5678,]...]

I wanted to retrieve the string represetation first, but printfn "%A" vals.ToString();; shows "System.Object[,]" only. If I then try to access vals.[1,1], I got error The field,constructor or member 'item' is not defined

thanks,

Answer 1

The type of Value2 is obj. If the range represents just a single cell, the actual type will be some primitive type (int, float, decimal, string). If the range represents several cells (your case), then the returned value is a two-dimensional .NET array of type obj[,].

You can cast the value returned by Value2 to an array and access it using indexers:

let vals = sheet.UsedRange.Value2 :?> obj[,]
vals.[1, 1]

Note that the returned array is 1-based (and not zero based as usual). The indexer again returns obj, so you need to cast the values to their actual type. Depending on your sheet, this will be probably float or string:

let firstTitle = vals.[1, 1] :?> string
let firstValue = vals.[2, 1] :?> float

(Assuming you have a title in A1 and a number in A2)