在 Java 中匹配 Excel 的浮点数 [英] Matching Excel's floating point in Java

问题描述

我有一个 .xlsx 电子表格，工作表 1 的左上角单元格中有一个数字.

I have an .xlsx spreadsheet with a single number in the top-left cell of sheet 1.

Excel 用户界面显示:

The Excel UI displays:

-130.98999999999

这在公式栏中可见，即不受包含单元格设置显示的小数位数的影响.这是 Excel 将为该单元格显示的最准确的数字.

This is visible in the formula bar, i.e. not affected by the number of decimal places the containing cell is set to show. It's the most accurate number Excel will display for this cell.

在底层 XML 中，我们有:

In the underlying XML, we have:

<v>-130.98999999999069</v>

当尝试使用 Apache POI 读取工作簿时，它通过 Double.valueOf 提供来自 XML 的数字并得出:

When trying to read the workbook with Apache POI, it feeds the number from the XML through Double.valueOf and comes up with:

-130.9899999999907

不幸的是，这与用户在 Excel 中看到的数字不同.任何人都可以指出一种算法来获得用户在 Excel 中看到的相同数字吗?

Unfortunately, this is not the same number the user can see in Excel. Can anyone point me to an algorithm to obtain the same number the user sees in Excel?

到目前为止，我的研究表明 Excel 2007 文件格式使用了一个稍微非标准的 IEE754 浮点版本，其中值空间不同.我相信 Excel 的浮点数，这个数字落在四舍五入边界的另一侧，因此看起来好像是向下舍入而不是向上舍入.

My research so far suggests that the Excel 2007 file format uses a slightly non-standard version of IEE754 floating point, where the value space is different. I believe in Excel's floating point, this number falls the other side of the boundary for rounding and hence comes out as though rounded down instead of up.

推荐答案

我同意 jmcnamara 的先前回答.这个答案对此进行了扩展.

I agree with jmcnamara's prior answer. This answer expands on it.

对于每个 IEEE 754 64 位二进制浮点数，有一个十进制小数范围会在输入时四舍五入.从 -130.98999999999069 开始，最接近的可表示值是 -130.9899999999068677425384521484375.在舍入到最接近和舍入半偶数规则下，范围内的任何值 [-130.9899999999907009851085604168474674224853515625, -130.989999999990672563399137007454537545454545454545453753753753753754535453754535470399913700454535351375353513700999999070604168474674224853515625(范围是封闭的，因为中心数的二进制表示是偶数.如果是奇数，范围将是开放的).-130.98999999999069 和 -130.9899999999907 都在范围内.

For each IEEE 754 64-bit binary floating point number, there is a range of decimal fractions that would round to it on input. Starting from -130.98999999999069, the closest representable value is -130.98999999999068677425384521484375. Under round to nearest with round half even rules, anything in the range [-130.9899999999907009851085604168474674224853515625, -130.9899999999906725633991300128400325775146484375] rounds to that value. (The range is closed because the binary representation of the central number is even. If it were odd, the range would be open). Both -130.98999999999069 and -130.9899999999907 are in range.

您确实拥有与 Excel 相同的浮点数.您确实具有与 Excel 输入相同的浮点数.不幸的是，进一步的实验表明 Excel 2007 只转换输入的最重要的 15 位数字.我将 -130.98999999999069 粘贴到 Excel 单元格中.它不仅显示为 -130.98999999999，而且使用它的算术与最接近该值的双精度值 -130.98999999990004653227515518665313720703125 一致，而不是原始输入.

You do have the same floating point number as Excel. You do have the same floating point number as was input to Excel. Unfortunately, further experiments suggest that Excel 2007 is only converting the most significant 15 digits of your input. I pasted -130.98999999999069 into an Excel cell. Not only was it displayed as -130.98999999999, arithmetic using it was consistent with the closest double to that value, -130.989999999990004653227515518665313720703125, rather than the original input.

要获得与 Excel 相同的效果，您可能需要使用例如BigDecimal 截断为 15 位十进制数字，然后转换为双精度.

To get the same effect as Excel you may need to use e.g. BigDecimal to truncate to 15 decimal digits, then convert to double.

Java 对浮点值的默认字符串转换基本上选择具有最少小数位的小数部分，以便转换回原始值.-130.9899999999907 的小数位数比 -130.98999999999069 少.显然，Excel 显示的数字较少，但 Apache POI 获得了与 Java 中相同数字的一种表示形式.

Java's default string conversion for floating point values basically picks the decimal fraction with the fewest decimal places that would convert back to the original value. -130.9899999999907 has fewer decimal places than -130.98999999999069. Apparently, Excel is displaying fewer digits, but Apache POI is getting one of the representations of the same number as you have in Java.

这是我用来获取此答案中数字的程序.请注意，我使用 BigDecimal 只是为了获得双打的精确打印输出，并计算两个连续双打之间的中点.

Here is the program I used to obtain the numbers in this answer. Note that I am using BigDecimal only to obtain exact printouts of doubles, and to calculate the mid point between two consecutive doubles.

import java.math.BigDecimal;

class Test {
  public static void main(String[] args) {
    double d = -130.98999999999069;
    BigDecimal dDec = new BigDecimal(d);
    System.out.println("Printed as double: "+d);
    BigDecimal down = new BigDecimal(Math.nextAfter(d, Double.NEGATIVE_INFINITY));
    System.out.println("Next down: " + down);
    System.out.println("Half down: " + down.add(dDec).divide(BigDecimal.valueOf(2)));
    System.out.println("Original: " + dDec);
    BigDecimal up = new BigDecimal(Math.nextAfter(d, Double.POSITIVE_INFINITY));
    System.out.println("Half up: " + up.add(dDec).divide(BigDecimal.valueOf(2)));
    System.out.println("Next up: " + up);
    System.out.println("Original in hex: "+Long.toHexString(Double.doubleToLongBits(d)));
  }
}

这是它的输出:

Printed as double: -130.9899999999907
Next down: -130.989999999990715195963275618851184844970703125
Half down: -130.9899999999907009851085604168474674224853515625
Original: -130.98999999999068677425384521484375
Half up: -130.9899999999906725633991300128400325775146484375
Next up: -130.989999999990658352544414810836315155029296875
Original in hex: c0605fae147ae000

这篇关于在 Java 中匹配 Excel 的浮点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！