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

Question

I tried using F # to read the 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 type F #? in fsx.exe, vals showed how

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

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

thanks,

Answer 1

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

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

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

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

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

(Suppose you have a heading in A1 and a number in A2)