4

When reading values from Excel cells that contain decimals, I am running into the following issue: If I enter 9.95 in the cell in Excel, CellValue.InnerText in C# returns "9.9499999999999993"

How can I get the actual value that was entered, meaning "9.95", knowing that the code that's trying to get these values does not know ahead of time that it's a decimal or indeed a number.

Luis Ferrao
  • 1,463
  • 2
  • 15
  • 30
  • 1
    Have you seen [this msdn page](http://msdn.microsoft.com/en-us/library/office/hh298534(v=office.15).aspx)? Scroll down to "Retrieving the Value" section. – gunr2171 Jun 27 '14 at 14:20
  • 1
    @gunr2171 I have and it doesn't answer the question – Luis Ferrao Jun 27 '14 at 14:26
  • @pnuts why are you giving me a lesson on floating point arithmetic when all I want is to get the (text) value the way Excel displays it – Luis Ferrao Jun 27 '14 at 14:36
  • You're confusing the actual value that was entered (by the user, 9.95) with the value that Excel stores (9.94999993) – Luis Ferrao Jun 27 '14 at 14:40
  • 2
    If Excel can't bu sure, how come when you change the format to display 20 decimal places, it will still display 9.950000000000000000, and not the stored value 9.9499999999993? – Luis Ferrao Jun 27 '14 at 14:59
  • Does this answer your question? [OpenXML 2.0 Gets wrong cell value](https://stackoverflow.com/questions/8728995/openxml-2-0-gets-wrong-cell-value) – Sepia Feb 21 '20 at 20:56
  • corrected = Convert.ToDouble(value).ToString() or if (Double.TryParse(value, out double d)){ corrected = d.ToString()} might help. See stackoverflow.com/a/39345761. – Sepia Feb 21 '20 at 21:33

3 Answers3

4

Excel stores values as a double precision floating point numbers, so many times what you type in to Excel isn't precisely storable as a floating point.

http://blogs.office.com/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/

If you really want to see how Excel is storing your values you can change the .xlsx extension to .zip, open it up, and peek around in the files. This is all of the information stored on your cell:

<sheetData>
    <row r="1" spans="1:1" x14ac:dyDescent="0.3">
        <c r="A1">
            <v>9.9499999999999993</v> 
        </c>
    </row>
</sheetData>

Excel could be designed to store the exact values you type in (by storing them as decimal instead of float) but arithmatic operations would be a lot slower since all calculations would be done in software rather than in hardware.

Seth Moore
  • 3,575
  • 2
  • 23
  • 33
  • 1
    I understand that it's stored as a floating point, but what I want to know if there's a way in OPENXML SDK to retrieve the value the way it is displayed to the user – Luis Ferrao Jun 27 '14 at 14:35
  • @Luis Ferrao: I don't believe that there is. The OpenXml SDK doesn't have logic built into it, it just provides a raw object that you can deserialize xlsx files into and manipulate them. You could probably figure it out yourself though by examining the column width and style applied to a cell to figure out how many digits should be displayed. – Seth Moore Jun 27 '14 at 15:10
1

You cannot retrieve the actual value entered for a numeric entry. I don't believe that is stored anyplace. The value you see is the value that is stored; you can also retrieve the value displayed (in VBA it would be the .Text propery of the cell), but I don't think you can get to the value entered.

The issue has to do with the inherent impossibility of representing certain decimal numbers in binary.

See also this Critique of Excel XML Format

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

As excel stores in floating point, try parsing from float:

string curSep = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.CurrencyDecimalSeparator;
string value = CellValue.InnerText.Replace(".", curSep);
value = value.Replace(",", curSep);
return float.Parse(value);

Does this work?

EluciusFTW
  • 2,565
  • 6
  • 42
  • 59
stefano
  • 1