CODEWITHSUNDEEP
c#.netexcelcom-interop
Samuel Jack
Samuel Jack

Reputation: 33270

Milliseconds missing when getting a DateTime value from Excel using .Net Interop

If I put a DateTime value into an Excel cell using Range.set_value through .Net COM Interop, and then retrieve the value of that same cell using Range.get_value, the Millisecond part of the value is not returned, though everything else is correct.

Is this a bug?

What is the workaround? I'm guessing that using the Value2 property instead might help. Has anybody else tried this?

Upvotes: 2

Views: 3300

Answers (3)

to StackOverflow
to StackOverflow

Reputation: 124696

It's probably because of the issue described in this KB article.

In which case, setting the Value2 property should work.

Upvotes: 2

Samuel Jack
Samuel Jack

Reputation: 33270

As Jon suggested, converting the DateTime to a double using DateTime.ToOADate (then back again using DateTime.FromOADate) works if you set the value using the Range.Value2 property.

The only trouble with this property is that, if you don't already know, it doesn't tell you that the cell is supposed to be treated as a DateTime. I guess to solve this, you'd need to use a two-pass approach: get cell values using Range.get_Value to determine their type, then, for any DateTime cells, get their values again using Range.Value2, then convert using DateTime.FromOADate.

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1500345

If you set a date/time with a millisecond value in Excel manually, does it maintain it? I don't know about the Excel internal object model, but it's conceivable that it just doesn't support milliseconds.

EDIT: Okay, now we know that the set fails (the get may also fail, of course)... you could try setting it as a double after converting it via DateTime.ToOADate(). I'm not saying I'm holding out much hope, but it's worth a try...

Upvotes: 4

Related Questions