Соответствие плавающей запятой Excel в Java

Ответ 1

Я согласен с предыдущим ответом jmcnamara. Этот ответ расширяется.

Для каждого 64-битного двоичного числа с плавающей запятой IEEE 754 существует диапазон десятичных дробей, которые бы округляли его на входе. Начиная с -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

Ответ 2

К сожалению, это не тот номер, который пользователь может видеть в Excel. Может ли кто-нибудь указать мне на алгоритм, чтобы получить тот же номер, который пользователь видит в Excel?

Я не думаю, что здесь используется алгоритм. Excel использует внутренний IEEE754 внутри, и я предполагаю, что он просто использует формат стиля printf при отображении номера:

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

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

Ответ 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());
    }

}

Ответ 4

Как было предложено peter.petrov, я бы использовал BigDecimal для этого. Как уже упоминалось, вы можете импортировать данные без потерь и всегда устанавливать шкалу в 15, то же поведение, что и в Excel

Ответ 5

Я использую это для вычисления того же 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();
}