org.apache.poi.ss.util.NumberComparer Maven / Gradle / Ivy
Show all versions of poi Show documentation
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
package org.apache.poi.ss.util;
import java.util.Locale;
import static org.apache.poi.ss.util.IEEEDouble.*;
/**
* Excel compares numbers using different rules to those of java, so
* {@link Double#compare(double, double)} won't do.
*/
public final class NumberComparer {
/**
* This class attempts to reproduce Excel's behaviour for comparing numbers. Results are
* mostly the same as those from {@link Double#compare(double, double)} but with some
* rounding. For numbers that are very close, this code converts to a format having 15
* decimal digits of precision and a decimal exponent, before completing the comparison.
*
* In Excel formula evaluation, expressions like "(0.06-0.01)=0.05" evaluate to "TRUE" even
* though the equivalent java expression is {@code false}. In examples like this,
* Excel achieves the effect by having additional logic for comparison operations.
*
* Note - Excel also gives special treatment to expressions like "0.06-0.01-0.05" which
* evaluates to "0" (in java, rounding anomalies give a result of 6.9E-18). The special
* behaviour here is for different reasons to the example above: If the last operator in a
* cell formula is '+' or '-' and the result is less than 250 times smaller than
* first operand, the result is rounded to zero.
* Needless to say, the two rules are not consistent and it is relatively easy to find
* examples that satisfy
* "A=B" is "TRUE" but "A-B" is not "0"
* and
* "A=B" is "FALSE" but "A-B" is "0"
*
* This rule (for rounding the result of a final addition or subtraction), has not been
* implemented in POI (as of Jul-2009).
*
* @return {@code negative, 0, or positive} according to the standard Excel comparison
* of values {@code a} and {@code b}.
*/
public static int compare(double a, double b) {
long rawBitsA = Double.doubleToLongBits(a);
long rawBitsB = Double.doubleToLongBits(b);
int biasedExponentA = getBiasedExponent(rawBitsA);
int biasedExponentB = getBiasedExponent(rawBitsB);
if (biasedExponentA == BIASED_EXPONENT_SPECIAL_VALUE) {
throw new IllegalArgumentException("Special double values are not allowed: " + toHex(a));
}
if (biasedExponentB == BIASED_EXPONENT_SPECIAL_VALUE) {
throw new IllegalArgumentException("Special double values are not allowed: " + toHex(a));
}
int cmp;
// sign bit is in the same place for long and double:
boolean aIsNegative = rawBitsA < 0;
boolean bIsNegative = rawBitsB < 0;
// compare signs
if (aIsNegative != bIsNegative) {
// Excel seems to have 'normal' comparison behaviour around zero (no rounding)
// even -0.0 < +0.0 (which is not quite the initial conclusion of bug 47198)
return aIsNegative ? -1 : +1;
}
// then compare magnitudes (IEEE 754 has exponent bias specifically to allow this)
cmp = biasedExponentA - biasedExponentB;
int absExpDiff = Math.abs(cmp);
if (absExpDiff > 1) {
return aIsNegative ? -cmp : cmp;
}
if (absExpDiff == 1) {
// special case exponent differs by 1. There is still a chance that with rounding the two quantities could end up the same
} else {
// else - sign and exponents equal
if (rawBitsA == rawBitsB) {
// fully equal - exit here
return 0;
}
}
if (biasedExponentA == 0) {
if (biasedExponentB == 0) {
return compareSubnormalNumbers(rawBitsA & FRAC_MASK, rawBitsB & FRAC_MASK, aIsNegative);
}
// else biasedExponentB is 1
return -compareAcrossSubnormalThreshold(rawBitsB, rawBitsA, aIsNegative);
}
if (biasedExponentB == 0) {
// else biasedExponentA is 1
return +compareAcrossSubnormalThreshold(rawBitsA, rawBitsB, aIsNegative);
}
// sign and exponents same, but fractional bits are different
ExpandedDouble edA = ExpandedDouble.fromRawBitsAndExponent(rawBitsA, biasedExponentA - EXPONENT_BIAS);
ExpandedDouble edB = ExpandedDouble.fromRawBitsAndExponent(rawBitsB, biasedExponentB - EXPONENT_BIAS);
NormalisedDecimal ndA = edA.normaliseBaseTen().roundUnits();
NormalisedDecimal ndB = edB.normaliseBaseTen().roundUnits();
cmp = ndA.compareNormalised(ndB);
if (aIsNegative) {
return -cmp;
}
return cmp;
}
/**
* If both numbers are subnormal, Excel seems to use standard comparison rules
*/
private static int compareSubnormalNumbers(long fracA, long fracB, boolean isNegative) {
if(isNegative) {
return Long.compare(fracB, fracA);
} else {
return Long.compare(fracA, fracB);
}
}
/**
* Usually any normal number is greater (in magnitude) than any subnormal number.
* However there are some anomalous cases around the threshold where Excel produces screwy results
* @param isNegative both values are either negative or positive. This parameter affects the sign of the comparison result
* @return usually {@code isNegative ? -1 : +1}
*/
private static int compareAcrossSubnormalThreshold(long normalRawBitsA, long subnormalRawBitsB, boolean isNegative) {
long fracB = subnormalRawBitsB & FRAC_MASK;
if (fracB == 0) {
// B is zero, so A is definitely greater than B
return isNegative ? -1 : +1;
}
long fracA = normalRawBitsA & FRAC_MASK;
if (fracA <= 0x0000000000000007L && fracB >= 0x000FFFFFFFFFFFFAL) {
// Both A and B close to threshold - weird results
if (fracA == 0x0000000000000007L && fracB == 0x000FFFFFFFFFFFFAL) {
// special case
return 0;
}
// exactly the opposite
return isNegative ? +1 : -1;
}
// else - typical case A and B is not close to threshold
return isNegative ? -1 : +1;
}
/**
* for formatting double values in error messages
*/
private static String toHex(double a) {
return "0x" + Long.toHexString(Double.doubleToLongBits(a)).toUpperCase(Locale.ROOT);
}
}