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

问题描述

Excel界面显示：

  -130.98999999999 
  

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

在底层XML中，我们有：

 < v> -130.98999999999069< / v> 
  

当尝试使用Apache POI读取工作簿时，它将XML中的数字通过Double.valueOf并提出：

  -130.9899999999907 
  

不幸的是，这与用户在Excel中看不一样。任何人都可以指出一个算法来获取用户在Excel中看到的相同数字？

我的研究至今表明Excel 2007文件格式使用了一些非标准版本的IEE754浮点，其中的值空间不同。我相信Excel的浮点数，这个数字落在四舍五入的边界的另一边，因此出来就好像四舍五入而不是向上。

解决方案

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

对于每个IEEE 754 64位二进制浮点数，在输入时都有一系列小数点。从-130.98999999999069开始，最接近的可表示值为-130.98999999999068677425384521484375。在圆形最接近圆形偶数规则的情况下，任意范围[-130.98999999998807009851085604168474674224853515625，-130.9899999999906725633991300128400325775146484375]回合到该值。 （范围是关闭的，因为中心数字的二进制表示是偶数，如果是奇数，范围将被打开）。 -130.98999999999069和-130.9899999999907都在范围内。

你有与Excel相同的浮点数。
你有与输入到Excel相同的浮点数。不幸的是，进一步的实验表明，Excel 2007只是转换您输入的最显着的15位数字。我将-130.98999999999069粘贴到Excel单元格中。不仅显示为-130.98999999999，算术使用它与该值最接近的一倍-130.989999999990004653227515518665313720703125，而不是原始输入。

要获得相同的作为Excel您可能需要使用例如BigDecimal要截断为15位十进制数字，然后转换为double。

对于浮点值，Java的默认字符串转换基本上选择小数位数小，将转换回原始值。 -130.9899999999907的小数位数比-130.98999999999069少。显然，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）））; 
} 
} 
  

这是它的输出：

 打印为双倍：-130.9899999999907 
下一个下一个：-130.989999999990715195963275618851184844970703125 
下半场：-130.9899999999907009851085604168474674224853515625 
原文：-130.98999999999068677425384521484375 
一半以上：-130.98999999906725633991300128400325775146484375 
下一个上一个：-130.9899999990658352544414810836315155029296875 
原件为十六进制：c0605fae147ae000 
  

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

The Excel UI displays:

-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.

In the underlying XML, we have:

<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:

-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?

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.

解决方案

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

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.

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.

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'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.

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)));
  }
}

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

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