OpenXML 2.0获取错误的单元格值 [英] OpenXML 2.0 Gets wrong cell value

问题描述

从 Excel 2010工作表中读取值时，我遇到了一些问题.

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

在标准的Excel 2010工作表上，我有一个单元格，其货币格式带有两个小数位和值1270,14 €.

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

当我在OpenXML 2.0(C#代码)上读取此值时，得到的是1270.1400000000001而不是原始的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.

从单元格OpenXML代码获取价值:

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;
}

该特定值通过'if'数据类型不为空的条件，并使用以下行检索该值:

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

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

该如何解决?

为什么甚至可能出现此错误?

推荐答案

由于数字在Excel中存储为double，因此您可以首先将字符串解析为double，然后转换回字符串以获取显示的值:

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();

让微软自己解决问题.

这篇关于OpenXML 2.0获取错误的单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！