CODEWITHSUNDEEP
c#.netexcelopenxml-sdk
Luis Ferrao
Luis Ferrao

Reputation: 1503

Reading decimal value from an Excel cell (in C#)

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.

Upvotes: 4

Views: 5962

Answers (3)

stefano
stefano

Reputation: 1

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?

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

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

Upvotes: 1

Seth Moore
Seth Moore

Reputation: 3545

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.

Upvotes: 4

Related Questions