Java에서 Excel의 부동 소수점 일치

Java에서 Excel의 부동 소수점 일치

시트 1의 왼쪽 상단 셀에 숫자가 하나 있는 .xlsx 스프레드시트가 있습니다.

Excel UI에 다음이 표시됩니다.

-130.98999999999

이것은 공식 막대에서 볼 수 있습니다. 즉, 포함된 셀이 표시하도록 설정된 소수 자릿수에 영향을 받지 않습니다.이 셀에 대해 Excel이 표시하는 가장 정확한 숫자입니다.

기본 XML에는 다음이 있습니다.

<v>-130.98999999999069</v>

Apache POI를 사용하여 워크북을 읽으려고 하면 XML에서 Double.valueOf를 통해 번호를 입력하고 다음과 같은 정보를 제공합니다.

-130.9899999999907

안타깝게도 이 번호는 사용자가 Excel에서 볼 수 있는 번호와 다릅니다.사용자가 엑셀에서 보는 것과 같은 숫자를 얻을 수 있는 알고리즘을 알려줄 수 있는 사람이 있습니까?

지금까지 제가 조사한 바에 따르면 Excel 2007 파일 형식은 값 공간이 다른 약간 비표준 버전의 IEE754 부동 소수점을 사용합니다.저는 엑셀의 부동소수점을 믿으며, 이 숫자는 반올림 경계의 반대쪽에 있기 때문에 위로가 아니라 반올림된 것처럼 나옵니다.

저는 jmcnamara의 사전 답변에 동의합니다.이 대답은 거기에 확장됩니다.

각 IEEE 754 64비트 이진 부동 소수점 번호에 대해 입력 시 반올림할 수 있는 소수점 범위가 있습니다.-130.98999999999069부터 시작하는 가장 가까운 대표적인 값은 -130.9899999990686742538452144375입니다.반 짝수 규칙이 있는 반올림에서 가장 가까운 반올림 규칙에 따라, 범위가 [-130.989999999999999970098585604168447448224853515625, -130.989999999967256339913001283257146484375]인 모든 값은 해당 값으로 반올림됩니다.(중앙 번호의 이진수 표현이 짝수이므로 범위가 닫힙니다.홀수일 경우 범위가 열려 있습니다.-130.98999999999069 및 -130.9899999999907이 모두 범위 내에 있습니다.

Excel과 부동 소수점 번호가 같습니다.Excel에 입력된 것과 동일한 부동 소수점 번호를 가지고 있습니다.불행히도 추가 실험에 따르면 Excel 2007은 입력 내용 중 가장 중요한 15자리 숫자만 변환합니다.-130.98999999999069를 엑셀에 붙여넣었습니다.-130.989999999999로 표시되었을 뿐만 아니라, 이를 사용한 산술은 원래 입력이 아닌 해당 값에 가장 가까운 두 배인 -130.989999999900046532275155665313720703125와 일치했습니다.

Excel과 동일한 효과를 얻으려면 예를 들어 사용해야 합니다.BigDecimal - 십진수 15자리로 잘라낸 다음 두 배로 변환합니다.

부동 소수점 값에 대한 Java의 기본 문자열 변환은 기본적으로 원래 값으로 다시 변환할 소수점이 가장 적은 소수점 부분을 선택합니다. -130.9899999999907은 -130.989999999069보다 소수점이 더 적습니다.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

안타깝게도 이 번호는 사용자가 Excel에서 볼 수 있는 번호와 다릅니다.사용자가 엑셀에서 보는 것과 같은 숫자를 얻을 수 있는 알고리즘을 알려줄 수 있는 사람이 있습니까?

나는 그것이 여기서 알고리즘을 사용하고 있다고 생각하지 않습니다.은 내부적으로 를 이중으로 사용하고 Excel을 하고 있는으로 추측됩니다.printf숫자를 표시할 때 스타일 형식:

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

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

은 야합다니해를 사용해야 .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());
    }

}

피터가 제안한 대로.페트로프 나는 이것을 위해 빅 십진법을 사용할 것입니다.언급했듯이 데이터를 손실 없이 가져오고 항상 스케일을 15로 설정하면 Excel에서와 동일한 동작을 수행할 수 있습니다.

동일한 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();
}

이 함수는 공식 막대에 표시된 것과 동일한 것을 생성해야 합니다.

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

언급URL : https://stackoverflow.com/questions/28537614/matching-excels-floating-point-in-java