Bug 68714 - Operator BETWEEN should work both ways
Summary: Operator BETWEEN should work both ways
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 5.2.3-FINAL
Hardware: PC Mac OS X 10.1
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2024-03-06 08:21 UTC by JD
Modified: 2024-03-06 08:21 UTC (History)
0 users

Reproducer with conditional formatting rules (28.08 KB, application/vnd.ms-excel.sheet.macroenabled.12)
2024-03-06 08:21 UTC, JD

Note You need to log in before you can comment on or make changes to this bug.
Description JD 2024-03-06 08:21:11 UTC
Created attachment 39610 [details]
Reproducer with conditional formatting rules

With Excel, the conditional formatting the rule "compare cell between 2 values" will evaluate the formula both ways:
 - val1 < cell.value < val2
 - val2 < cell.value < val1

There is no assumption what is the lowest value and the highest one. 

With Apache POI, the evaluation is done one way which suppose that the lowest value is the first one and the highest one is the second:
 - return cellValue.compareTo(v1) >= 0 && cellValue.compareTo(v2) <= 0;


As a consequence, each time the lowest value is passed as a second value, the expression is always false.

Code should look like:
 return (cellValue.compareTo(v1) >= 0 && cellValue.compareTo(v2) <= 0 ) ||
                 ((cellValue.compareTo(v1) <= 0 && cellValue.compareTo(v2) >= 0 )) ;