Excel 2007中的数值-表示与基础xml文件中的存储 [英] Numerical values in Excel 2007 - representation vs storage in the underlying xml file

问题描述

此问题与.NET和OpenXml有关.

This question is related to .NET and OpenXml.

我已经看完以下文章，它有很好的解释，但不能回答我的问题: Excel 2007中数值的可视化不一致与基础xml文件

I've already gone through the following article, it has good explanation, but not an answer to my question: Inconsistent visualization of numerical values in Excel 2007 vs the underlying xml file

在我的应用程序中，用户正在上载Excel(.xls/.xlsx)，并且正在使用OpenXML SDK进行解析.

In my application, user is uploading an Excel (.xls/.xlsx) and I'm parsing using OpenXML SDK.

除一些值外，解析工作正常.Excel存储的值与在某些情况下输入的值不同.以下是用户输入内容与底层xml中存储内容的一些示例:

Parsing works fine, except a few values. Excel is storing different values than entered in some of the scenarios. Following are some examples of what user entered vs what is stored in underlying xml:

+--------------------+-----------------------+--------------------+
| User Entered Value | Excel Displayed Value | Stored XML Value   |
+--------------------+-----------------------+--------------------+
| 4.1                | 4.1                   | 4.0999999999999996 |
+--------------------+-----------------------+--------------------+
| 4.4                | 4.4                   | 4.4000000000000004 |
+--------------------+-----------------------+--------------------+
| 19.99              | 19.99                 | 19.989999999999998 |
+--------------------+-----------------------+--------------------+

当我使用OpenXML在.NET中解析此excel时，出现了我的问题.OpenXML读取这些值作为存储为XML值的值.因此，即使用户输入了4.1，OpenXML仍将其读取为4.09999999999999999996.现在，根据我在此excel上运行的验证之一，我需要检查小数点.我真的无法验证用户在这种情况下输入了什么.

My problem comes when I'm parsing this excel in .NET using OpenXML. OpenXML reads these values as what is stored as XML values. So even though user entered 4.1, OpenXML reads it as 4.0999999999999996. Now as per the one of the validations I'm running on this excel, I need to check for the decimal points. I cannot really validate what user entered in this kind of a situation.

任何帮助都将不胜感激！！！由于OpenXml是Microsoft提供的SDK，因此我没有尝试过其他Excel阅读器，但是欢迎提出任何建议.

Any help is greatly appreciated !! Since OpenXml is an SDK provided by Microsoft, I haven't tried any other Excel readers, but any suggestions are welcomed.

推荐答案

这是表示浮点类型数据的正确方法.您可以按照以下代码段进行验证.

This is the correct way to represent data of floating point type. You can verify that by following code snippet.

运行以下代码，

string s = "4.0999999999999996";

Console.WriteLine(float.Parse(s)); 
// output 4.1

string s1 = "4.4000000000000004";

Console.WriteLine(float.Parse(s1)); 
// output 4.4

string s2 = "19.989999999999998";

Console.WriteLine(float.Parse(s2)); 
// output 19.99

因此，尝试对这些值进行解析，您将获得原样的期望真实值.

So try to parse those values and you will get your expected true value as it is.

这篇关于Excel 2007中的数值-表示与基础xml文件中的存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！