Excel 2007 中数值的可视化与底层 xml 文件不一致
我试图从 Excel 外部读取 Excel 2007 文件 (xlsx)，但发现了无法解释的不一致之处。
如果您在单元格中输入值 19.99，然后查看基础 Xml 文档，它实际上存储为 19.989999999999998。这不是执行此操作的唯一值，但它是一个合理的示例。工作表中未应用任何格式。在我的示例中，我只是打开一个新的工作簿，在 A1 中输入 19.99 并保存文件。
我尝试在开放办公室和 Google 文档中打开这个简单的示例，加载文档时它显示 19.99。
我的问题是，如何确定何时将该值从 19.989999999999998 转换为 19.99 以在其他系统中使用？
I am attempting to read an Excel 2007 file (xlsx) from outside of Excel and I am finding an inconsistency that I cannot explain.
If you enter the value of 19.99 into a cell and then look at the underlying Xml document it is actually stored as 19.989999999999998. This is not the only value that does this, but it is a reasonable example. No formatting is applied in the sheet. In my example I just open a new Workbook, type in 19.99 in A1 and save the file.
I have attempted to open this simple example in both open office and Google docs and it shows 19.99 when the document is loaded.
My question is, how do I determine when to transform this value from 19.989999999999998 into 19.99 for use in other systems?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您输入的 19.99 和存储的 19.989999999999998 之间的变化是浮点变化...浮点数的二进制表示形式（Excel 内部使用）和用于显示的十进制（以及存储在xlsx 文件）。
即使您没有为单元格明确指定格式，Excel 也会应用默认格式“@”或“常规”，通常（对于数字）显示为 2dp，如果需要则应用科学格式。如果您查看该单元格的数字格式（无论是使用 MS Excel 前端，还是通过检查 xlsx 文件），您应该发现它实际上设置为默认值。
The variation between the 19.99 you entered and the 19.989999999999998 stored is the floating point variation... there will typically always be a slight discrepancy between the binary representation of a float (used internally by Excel) and the decimal used for display (and storage in the xlsx file).
Even if you haven't explicitly assigned a format to the cells, Excel applies a default formatting of "@" or "General", which typically (for numerics) displays to 2dp, applying scientific if needed. If you look at the number formatting for that cell (whether using theMS Excel front-end, or by examining the xlsx file), you should find that it is actually set to the default.