在Java中匹配Excel的浮点数

Question

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

Excel用户界面显示：

-130.98999999999

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

在底层XML中，我们有：

<v>-130.98999999999069</v>

尝试使用Apache POI读取工作簿时，它会通过Double.valueOf将XML中的数字提供，并得出以下结果：

-130.9899999999907

不幸的是，这不是用户在Excel中看到的相同数字。是否有人可以指向一个算法来获得用户在Excel中看到的相同数字？

到目前为止，我的研究表明，Excel 2007文件格式使用略微非标准的IEE754浮点数，其中值空间不同。我相信在Excel的浮点数中，这个数字落在舍入边界的另一侧，因此出现的结果是舍位而不是进位。

- David North

Answer 1

我同意jmcnamara之前的答案。这个答案在其基础上进行了扩展。

对于每个IEEE 754 64位二进制浮点数，存在一系列十进制小数，在输入时会舍入为该数字。从-130.98999999999069开始，最接近的可表示值是-130.98999999999068677425384521484375。使用四舍五入和“银行家舍入规则”，任何位于[-130.9899999999907009851085604168474674224853515625，-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)));
  }
}

以下是它的输出：

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

- Patricia Shanahan

Answer 2

很不幸，这不是用户在Excel中看到的相同数字。有人能指导我获取与用户在Excel中看到的相同数字的算法吗？

我认为这里没有使用算法。Excel在内部使用IEEE754 double，并且我猜它只是在显示数字时使用printf样式格式：

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

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

- jmcnamara

---

内部数值可能是-130.98999999999068677425384521484375，是最接近-130.9899999999907的IEEE 754 64位二进制浮点数值。 - Patricia Shanahan

Answer 3

你需要使用BigDecimal（以保留精度）。例如，将值作为String读取，然后从中构建一个BigDecimal。

以下是一个例子，你不会失去任何精度，也就是说这是获取用户在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());
    }

}

- peter.petrov

---

我的问题不是准确性，而是如何模拟 Excel 对数字的解释。使用 BigDecimal 可以保留文件格式中报告的数字，但它并不能让我更接近匹配 Excel 的行为。 - David North

@DavidNorth 好的，一旦您将数字作为 BigDecimal，您可以将其舍入到任意数量的小数位（这似乎是 Excel 向用户呈现的方式；在您的特定示例中有 11 个小数位）。总的来说，我认为试图“模拟 Excel 的行为”不是正确的方法 ;) - peter.petrov

我需要添加更多的例子，但我们遇到的困难是使用BigDecimal找不到与Excel在所有情况下匹配的单个舍入算法。至于不尝试模拟Excel的行为：困难在于我的用户可以在Excel中看到一个数字，并希望我在使用电子表格时显示他们相同的数字。即使微软正在尽力使它成为不可能，这也是他们合理的要求... - David North

可能无法使用字符串构造函数。我认为POI会将值作为double类型返回。 - Thilo

Answer 4

我使用这个来计算相同的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();
}

- plajko

Answer 5

根据peter.petrov的建议，我会使用BigDecimal。正如提到的那样，它允许您在不丢失数据的情况下导入数据，并且始终将比例设置为15，您就可以获得与Excel相同的行为。

- Fabian Tschachtli

Answer 6

这个函数应该产生与公式栏中看到的相同的内容：

    private static BigDecimal stringedDouble(Cell cell) {
            BigDecimal result =  new BigDecimal(String.valueOf(cell.getNumericCellValue())).stripTrailingZeros();
            result = result.scale() < 0 ? result.setScale(0) : result;
            return result;
    }

- mrsandtron