Apache POI - Common 5.3.0 · org.apache.poi.ss.util.NumberComparer

Access	Constructor and Description
public	NumberComparer()

Method Summary

Modifier and Type	Method and Description
public static int	Returns: `negative, 0, or positive` according to the standard Excel comparison of values `a` and `b`. compare(double a, double b) This class attempts to reproduce Excel's behaviour for comparing numbers.
private static int	Returns: usually `isNegative ? -1 : +1` compareAcrossSubnormalThreshold(long normalRawBitsA, long subnormalRawBitsB, boolean both values are either negative or positive. This parameter affects the sign of the comparison result isNegative) Usually any normal number is greater (in magnitude) than any subnormal number.
private static int	compareSubnormalNumbers(long fracA, long fracB, boolean isNegative) If both numbers are subnormal, Excel seems to use standard comparison rules
private static String	toHex(double a) for formatting double values in error messages

Inherited from java.lang.Object:: clone equals finalize getClass hashCode notify notifyAll toString wait wait wait

NumberComparer	back to summary
public NumberComparer()

Method Detail

compare back to summary

compare	back to summary
public static int compare(double a, double b) This class attempts to reproduce Excel's behaviour for comparing numbers. Results are mostly the same as those from `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 `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 2⁵⁰ 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). Returns:int `negative, 0, or positive` according to the standard Excel comparison of values `a` and `b`.

public static int compare(double a, double b)

This class attempts to reproduce Excel's behaviour for comparing numbers. Results are mostly the same as those from 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 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 2⁵⁰ 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).

Returns:int: negative, 0, or positive according to the standard Excel comparison of values a and b.

compareAcrossSubnormalThreshold back to summary

compareAcrossSubnormalThreshold	back to summary
private static int compareAcrossSubnormalThreshold(long normalRawBitsA, long subnormalRawBitsB, boolean isNegative) 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 Parameters isNegative:boolean both values are either negative or positive. This parameter affects the sign of the comparison result Returns:int usually `isNegative ? -1 : +1`

private static int compareAcrossSubnormalThreshold(long normalRawBitsA, long subnormalRawBitsB, boolean isNegative)

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

Parameters

isNegative:boolean: both values are either negative or positive. This parameter affects the sign of the comparison result

Returns:int

usually isNegative ? -1 : +1

compareSubnormalNumbers	back to summary
private static int compareSubnormalNumbers(long fracA, long fracB, boolean isNegative) If both numbers are subnormal, Excel seems to use standard comparison rules

toHex	back to summary
private static String toHex(double a) for formatting double values in error messages

public final Class NumberComparer

Constructor Summary

Method Summary

Constructor Detail

Method Detail