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

问题描述

我有一个的.xlsx US preadsheet在表1的左上角单元一个数字。

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

Excel的UI显示：

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开始，最接近重presentable值-130.98999999999068677425384521484375。下一轮最近的圆形一半甚至规定，在什么范围内[-130.9899999999907009851085604168474674224853515625，-130.9899999999906725633991300128400325775146484375]发到该值。 （该范围被关闭，因为中央数目的二进制重新presentation是偶数。如果是奇数，则范围将是打开）。无论-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.989999999990004653227515518665313720703125一致，而不是原来的输入

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越来越相同数目的重新presentations之一，因为你在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

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