匹配Java中的Excel浮点数

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

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

The Excel UI displays:

Excel UI显示:

-130.98999999999

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.

这在公式栏中是可见的，即不受所包含单元格要显示的小数位数的影响。这是Excel显示的最准确的数字。

In the underlying XML, we have:

在底层XML中，我们有:

<v>-130.98999999999069</v>

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

当尝试使用Apache POI阅读工作簿时，它通过Double向XML提供数字。并提出:

-130.9899999999907

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中看到的数字不同。谁能给我指出一个算法来获得用户在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.

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

5 个解决方案

#1

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

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

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.

对于每个IEEE 754位二进制浮点数，有一系列的小数部分可以在输入中循环。从-130.98999999999069开始，最接近的可表示值是- 130.9899999999999999999068677425384521484375。在圆到最近的半偶数规则下，范围内的任何东西[- 130.989999999999999999907009858070098510851085604164164477424853515625，- 130.989999999999999067253299329325725775145146484375]轮到该值。(由于中心数的二进制表示是偶数，所以范围是封闭的。如果它是奇数，范围将是开放的)。-130.98999999999069和- 130.98999999999999999999907都在射程之内。

~~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相同的浮点数。你的浮点数和Excel的输入是一样的。不幸的是，进一步的实验表明，Excel 2007只会转换你输入的最重要的15位数字。我将- 130.9899999999999069粘贴到一个Excel单元格中。它不仅显示为-130.98999999999，使用它的算法与该值的最接近的两倍一致，- 130.989999999990004653227518665313720703125，而不是原始输入。

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.

要获得与Excel相同的效果，您可能需要使用BigDecimal来将其截断为15位小数，然后将其转换为double。

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.

Java对浮点值的默认字符串转换基本上选择小数部分，小数部分的位数最少，可以转换回原始值。-130.9899999999907的小数位数比- 130.989999999999999069要少。显然，Excel显示的数字更少，但Apache POI获得的数字表示与Java中相同。

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.

这是我用来得到这个答案中的数字的程序。注意，我使用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)));
  }
}

Here is its output:

这是它的输出:

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

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中看到的数字不同。谁能给我指出一个算法来获得用户在Excel中看到的相同的数字吗?

I don't think that it is using an algorithm here. Excel uses IEEE754 double internally and I'd guess that it is just using a printf style format when displaying the number:

我不认为这里用的是算法。Excel内部使用IEEE754 double，我猜它只是在显示数字时使用printf格式:

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

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

#3

You need to use BigDecimal for this (for not losing any precision).
E.g. read the value as a String, then construct a BigDecimal from it.

为此，您需要使用BigDecimal(不丢失任何精度)。例如，将值读为字符串，然后从中构造一个BigDecimal。

Here is an example where you don't lose any precision i.e. this
is the way to obtain exactly the same number which the user sees in Excel.

这里有一个例子，你不会失去任何精度，也就是说，这是获得用户在Excel中看到的相同数字的方法。

import java.math.BigDecimal;

public class Test020 {

    public static void main(String[] args) {
        BigDecimal d1 = new BigDecimal("-130.98999999999069");
        System.out.println(d1.toString());

        BigDecimal d2 = new BigDecimal("10.0");

        System.out.println(d1.add(d2).toString());
        System.out.println(d1.multiply(d2).toString());
    }

}

#4

As suggested by peter.petrov I would use BigDecimal for this. As mentioned it let's you import the data without loss and be always setting the scale to 15 you have the same behaviour as in Excel

所建议的彼得。彼得罗夫，我用BigDecimal。正如前面提到的，我们可以在不损失的情况下导入数据，并且总是将scale设置为15，您的行为与Excel相同。

#5

I use this for calculating the same 15 digit display value.

我用这个来计算相同的15位显示值。

private static final int EXCEL_MAX_DIGITS = 15;

/**
 * Fix floating-point rounding errors.
 *
 * https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel
 * https://support.microsoft.com/en-us/kb/214118
 * https://support.microsoft.com/en-us/kb/269370
 */
private static double fixFloatingPointPrecision(double value) {
    BigDecimal original = new BigDecimal(value);
    BigDecimal fixed = new BigDecimal(original.unscaledValue(), original.precision())
            .setScale(EXCEL_MAX_DIGITS, RoundingMode.HALF_UP);
    int newScale = original.scale() - original.precision() + EXCEL_MAX_DIGITS;
    return new BigDecimal(fixed.unscaledValue(), newScale).doubleValue();
}

#1