在 Java 中匹配 Excel 的浮点

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

Excel UI 显示:

-130.98999999999

这在编辑栏中可见,即不受包含单元格设置为显示的小数位数的影响。这是 Excel 将为此单元格显示的最准确的数字。

在底层 XML 中,我们有:

<v>-130.98999999999069</v>

当尝试使用Apache POI读取工作簿时,它会通过Double.valueOf从XML中获取数字,并得出:

-130.9899999999907

不幸的是,这不是用户可以在Excel中看到的相同数字。任何人都可以将我指向一种算法来获得用户在Excel中看到的相同数字吗?

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


答案 1

我同意jmcnamara先前的答案。这个答案扩展了它。

对于每个 IEEE 754 64 位二进制浮点数,都有一个在输入时舍入到的小数分数范围。从 -130.989999999999069 开始,最接近的可表示值为 -130.98999999999068677425384521484375。在舍入到最接近的半偶数规则下,范围 [-130.9899999999907009851085604168474674224853515625, -130.9899999999906725633991300128400325775146484375] 中的任何内容都将舍入到该值。(范围是封闭的,因为中心数的二进制表示形式是偶数。如果它是奇数,则范围将是开放的)。-130.989999999999069 和 -130.9899999999907 都在范围内。

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

要获得与Excel相同的效果,您可能需要使用例如BigDecimal截断为15个小数位,然后转换为双精度。

Java对浮点值的默认字符串转换基本上选择小数点数,小数位数最少,可以转换回原始值。-130.98999999999907 的小数位数少于 -130.989999999999069。显然,Excel显示的数字较少,但Apache POI获得的表示形式与Java中的数字相同。

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

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

答案 2

不幸的是,这不是用户可以在Excel中看到的相同数字。任何人都可以将我指向一种算法来获得用户在Excel中看到的相同数字吗?

我不认为它在这里使用算法。Excel在内部使用IEEE754双精度,我猜它只是在显示数字时使用样式格式:printf

$ python -c 'print "%.14g" % -130.98999999999069' 
-130.98999999999

$ python -c 'print "%.14g" % -130.9899999999907' 
-130.98999999999