Is it possible to get an int from Range.Value in VSTO?

5
votes

I have a VSTO addin and I'm reading data from an Excel worksheet.

It seems that almost all numeric data is read as a double. Is it ever possible to get an int value from Range.Value?

Here is some code to demonstrate what I mean.

Worksheet w = (Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets["Sheet1"];
var value = ((Range)w.Cells[1, 1]).Value;
bool isInt = value is int;
bool isDouble = value is double;

No matter which format I use in worksheet Sheet1, cell A1, isInt always comes back false.

Is there some format I should be using to get int? I thought maybe General or 0 would work, but it seems not.

4
c#excelvstoint
I don't think you'd ever get an int - Excel never stores numbers as an int internally, always a double. – Govert
@Govert Okay, that's what I suspected but I wanted confirmation. If you post an answer (preferably with a source about that Excel tidbit) I'll accept it. – Kris Harper

4 Answers

2
votes

Numeric values returned by Range.Value are doubles (possibly becuase they are all stored as doubles in Excel, a number looking like an integer or not is the result of formatting)

The only ints that I have seen coming through GetValue are the ErrorCodes for errors

e.g.

  • DIV/0! is an Int32 with a value of -2146826281
1
votes

Govert is correct in saying that Excel never stores numbers as an integers.

Excel stores numeric values as Double Precision Floating Point numbers, or as you know Doubles for short.

Doubles are 8-byte variables that can store numbers accurate to approximately 15 decimal places.

EDIT:

Since you are looking for a link, see this

Link: http://support.microsoft.com/kb/78113

1
votes

I'm pretty sure all numbers are doubles in Excel. For example the following VBA code sets a cell value to a long integer (Int32), then reads it back.

Dim n As Long
n = 123
Range("A1").Value = n
Debug.Print VarType(n)

Dim v As Variant
v = Range("A1").Value
Debug.Print VarType(v)
  • Input is VarType(n) = 3 = vbLong (Int32)
  • Output is VarType(v) = 5 = vbDouble
0
votes

From the docs when you call var value = ((Range)w.Cells[1,1]).Value the Value type is derived from the value type from the Worksheet. So it would seem that your Excel worksheet has the values formatted as doubles, not ints. There is no "integer" formatting for Excel that will translate to C#, the best bet would be to cast the value as an integer when you read it in. The Value type comes in whatever format it was within the Excel worksheet.

This article shows how Excel deals with double precision floating point values.