OpenXML 2.0 Gets wrong cell value

Question

I'm having a bit of a problem reading a value from an Excel 2010 worksheet.

On a standard Excel 2010 worksheet I have a cell with the currency format with two decimal places and the value 1270,14 €.

When I read this value on OpenXML 2.0 (C# code) I get 1270.1400000000001 instead of the original 1270.14.

The same happens with other values on any cell with the same formating.

Get value from cell OpenXML code:

private string GetCellValue(string column, int row)
{
    column = column.ToUpper();
    var targetCell = cells.Where(p => p.CellReference == (column + row)).SingleOrDefault();

    var value = String.Empty;

    if (targetCell.DataType != null && targetCell.DataType.Value == CellValues.SharedString)
    {
        var index = int.Parse(targetCell.CellValue.Text);
        value = cellValues[index].InnerText.Trim();
    }
    else
    {
        if (targetCell.CellValue != null)
        {
            value = targetCell.CellValue.Text.Trim();
        }
        else
        {
            value = null;
        }
    }

    return value;
}

The specific value passes over the 'if' DataType is not null condition and retrieves the value with the line:

value = targetCell.CellValue.Text.Trim();

How can this be fixed ?

Why is this error even possible ?

That has nothing to do with OpenXML - it is an inherent problem of using "floating point arithmetic" (on almost all platforms and with almost all languages)... Excel does format the value for display... you could do the same... — Yahia, Jan 04 '12 at 21:03
@Yahia - You could be right if that value was a result of some kind of calculation but no. '1270,14' was a keyed in number on a cell pre-formated as currency with Two decimal places. Since I'm not making any type of calculation\formula I don't think the problem of "floating point arithmetic" is present here. — Gil, Jan 10 '12 at 16:29
you just proved my point: the cell is *formatted* as decimal with 2 decimal places... but when you access it from .NET you get a double which in turn is prone to anyting "floating point arithmetic" is! — Yahia, Jan 10 '12 at 16:42

score 5 · Answer 1 · answered Sep 06 '16 at 09:43

5

As the number is stored as double in Excel, you can first parse the string as double, then convert back to string to get the value for display:

    string s = Convert.ToDouble(value).ToString();

Let Microsoft handle its own problem.

answered Sep 06 '16 at 09:43

SiuFay

333
3
8

score 1 · Answer 2 · answered Jan 04 '12 at 20:59

1

Though it may not fit your application, the easiest fix would be simply rounding off the value you get back and assigning it or returning it where applicable. somevarhere=Math.Round(Convert.ToDouble(value), 2)

answered Jan 04 '12 at 20:59

emd

1,173
9
21

That is a good workaround but the problem is that I don't really process any of the information coming from the excel file meaning I can be reading numbers or simply text and in that case your code will break. – Gil Jan 05 '12 at 09:53
1

If you want to match your display to that of Excel, look at the answer to http://stackoverflow.com/questions/4730152/what-indicates-an-office-open-xml-cell-contains-a-date-time-value You could format your strings based on the cell formatting. This seems like the most appropriate answer for your application – emd Jan 05 '12 at 21:21

OpenXML 2.0 Gets wrong cell value

2 Answers2

Linked