Back to Questions
1.4k
views7
comments
Excel Content and Type Inferencing
f#

Hello

I have an interesting conundrum related to type inferencing. I am reading an Excel spreadsheet into a 2-dim array with

worksheet.UsedRange.Value2 :?> obj[,]

One of the cells has date semantics, e.g. "01/11/2007".

Unfortunately the type is inferred as double and thus reads as 39387.0. I cannot find a way around this as worksheet.UsedRange.Value2 is of type object until the downcast is applied. By the time that happens however it would appear that I can no longer get the raw value.

I must be missing something simple, do you know what it is?

Many thanks!

Edward

.

F#'s pattern matching over types was made for these sorts of situtations. You want to say something like:

1
2
3
4

match worksheet.UsedRange.Value2 with
| :? DateTime as date -> //do something with date
| :? float as f -> //do something with float
| :? obj[,] as range -> //do something with range
By on 12/5/2007 2:00 AM ()

Hi Robert and Julien

Many thanks for your responses.

Robert, I don't think it's that simple - the UsedRange is two-dimensional, i.e. a set of rows from a Worksheet:

let obj_array = worksheet.UsedRange.Value2 :?> obj[,] // Notice: worksheet.UsedRange

Where rows are something like

A B C
1 foo 01/11/2007 12
2 bar 01/12/2006 13
...

So, to be sure, UsedRange is A1:C2, a 2-dim array 2X3, or an obj[,].

But the fundamental problem is that when I invoke something like...

let date_val = array.(1,2) // cell B1

...F# always returns a double, despite the "/" in the field. Coersions fail as well. I would have thought this would give me a string, but there you go. Interestingly in fsi doing a "let a = 01/11/2007" returns an int 0.

I want to transform the rows into records of type { Name: string; Date: [DateTime or String]; Value: int }. (I would prefer to coerce to string for the Date member so I can validate it, (i.e. it must be dd/mm/yyyy)).

I have a function value that iterates the rows and calls something like:

let create_record row (array:obj[,]) = // type annotation required
{ Name = array.(row, 1); Date = array.(row, 2); Value = array.(row,3) }

The problem being that the Date assignment fails because it's returned as a double.

I would be happy to post more code if that would help clarify any of this further.

If I could simply have the raw values of the fields then this problem would go away.

Many thanks!

Edward

P.S. I have your book on my desk, it's been a great help.

By on 12/5/2007 5:13 AM ()

[link:msdn2.microsoft.com]

Value2 will return a float for both date and currency data types.

Hope this helps.

By on 12/5/2007 1:41 AM ()

Hi Julien

Please see above!

Kind regards,

Edward

By on 12/5/2007 5:14 AM ()

Hi Edward,

Glad you like the book!

You mention in your post that in fsi "let a = 1/11/2007" returns 0. This is because F# has no date litteral, so what your preforming is integer division.

That aside, I think Julien hit the nail on the head and I was half asleep when I made my post. As msdn says: "The only difference between this property and the Value property is that the Value2 property doesn’t use the Currency and Date data types. You can return values formatted with these data types as floating-point numbers by using the Double data type."

So we would expect a date to be represented as a double. This forum post digs a bit deeper into the problem: [link:www.thescripts.com] (For those who can't be bothered to follow the link the post basically says you need to use DateTime.FromOADate to convert an Excel date).

So for your function to work I expect you need to do something like:

1
2

  let create_record row (array:obj[,]) = 
    { Name = array.(row, 1) :?> string; Date = DateTime.FromOADate(array.(row, 2) :?> double); Value = array.(row,3) :?> double }

But, working with Excel automation you're always going to bump into lots of these type of problems. At work we moved over to use a 3rd party component to read from excel, and have seen both the amount of code required process the file and the time to process go down. We use Syncfusion ([link:www.syncfusion.com]), if there's a budget for this sort of thing I'd recommend it.

Cheers,
Rob

By on 12/5/2007 5:48 AM ()

Hi Guys

Many thanks for helping me out! As suggested, it turns out that DateTime.FromOADate works perfectly.

Kind regards

Edward

By on 12/10/2007 7:01 AM ()

[link:msdn2.microsoft.com]
You may want to use the value property (which needs a parameter to be passed)

[link:msdn2.microsoft.com]
...or the text property (but this means you'd probably have to do some more conversions afterwards)

[link:msdn2.microsoft.com]
...or convert the value2 to a DateTime with DateTime.FromOADateTime

Hope this helps

By on 12/5/2007 5:36 AM ()

    Topic tags

    IntelliFactory Offices Copyright (c) 2011-2012 IntelliFactory. All rights reserved.
    Home | Products | Consulting | Trainings | Blogs | Jobs | Contact Us | Terms of Use | Privacy Policy | Cookie Policy     		Built with WebSharper