如何在Java中获取与Excel一致的未舍入精确除法结果？

Let's break down what's going on here and how to align your Java calculations with Excel's behavior:

First, Let's Clarify the Exact Mathematical Result #

First off, let's do the manual math to set a baseline:
8789700 ÷ 3200 = 87897 ÷ 32 = 2746.78125
This is the precise decimal result, and it can be represented perfectly as a double (since its fractional part is a sum of negative powers of 2). The value 2746.78125 you're seeing isn't a rounded outcome—it's the true unrounded calculation result. Your expected value 2746.78124999656 likely comes from an approximate input in Excel (e.g., the original number wasn't exactly 8789700, but a floating-point approximation of it).

Why Your Current Code Isn't Matching Your "Expected" Value #

When you use new BigDecimal(double, MathContext.DECIMAL64), you're starting with a double representation of your numbers. While 8789700 and 3200 are integers that can be stored exactly as doubles, if your source data in Excel was actually an approximate floating-point value (not the exact integer), the double would already carry that precision loss—and converting it to BigDecimal won't reverse that.

Solutions to Align with Excel's Calculations #

Excel uses IEEE 754 double-precision floating-point (same as Java's double) by default, but if you need precise decimal calculations or want to match Excel's behavior exactly, try these approaches:

1. Initialize BigDecimal with Strings (For Exact Decimal Values) #

Avoid using double as an intermediate step—create BigDecimals directly from strings to preserve exact decimal values:

public void testdivide_largenumber() { 
    BigDecimal number_BD = new BigDecimal("8789700"); 
    BigDecimal divisor_BD = new BigDecimal("3200"); 
    BigDecimal result_BD = number_BD.divide(divisor_BD); 
    double result = result_BD.doubleValue();
    
    System.out.println("double result: " + result); 
    System.out.println("BigDecimal result: " + result_BD); 
}

This code will output the exact value 2746.78125, which matches the true mathematical result.

2. Simulate Excel's Approximate Floating-Point Behavior #

If your Excel result 2746.78124999656 comes from an approximate double input (not the exact integer), you need to use that specific approximate double value to initialize your BigDecimal. For example, if the original number in Excel was stored as a double slightly less than 8789700, use that exact double instead of the integer literal.

3. Force Specific Precision and Rounding #

If you need to replicate a specific precision or rounding behavior (like Excel's display or calculation settings), specify the rounding mode and decimal places in your BigDecimal division:

// Example: Keep 11 decimal places and truncate (no rounding up)
BigDecimal result_BD = number_BD.divide(divisor_BD, 11, RoundingMode.DOWN);

This can produce a result like 2746.78124999656 if the underlying calculation has more decimal digits to work with.

Final Notes #

• The 2746.78125 you're getting now is the correct unrounded result of the exact division.
• If you truly need 2746.78124999656, double-check the original data in Excel—chances are it's not the exact integer 8789700, but a floating-point approximation.

内容的提问来源于stack exchange，提问作者Ice