There was a discussion on dev@poi.apache.org about how to use formula evaluation, and I realized we're a little sparse on unit tests. I wrote a few unit tests and discovered that POI's formula evaluation of A1=1 when A1's value is TRUE (boolean cell type) is inconsistent with LibreOffice's formula evaluation implementation. For the formula "IF(A1=1, B1, C1)", all other data types for A1 are consistent with LibreOffice's evaluation of the formula.
Created attachment 33262 [details] formula evaluation unit tests testIFEqualsFormulaEvaluation_Boolean currently fails with POI trunk (r1712907, 2015-11-05, post-POI 3.13, pre POI 3.14beta1) It has been annotated with an @Ignore until this bug is resolved.
Created attachment 33263 [details] formula evaluation unit tests I added in a few more tests: numeric coerced to string string coerced to numeric boolean simple (no IF statement) blank cell inverted with NOT (another failing case) blank cell inverted with NOT simple (no IF statement)
All of these formulas can probably be reduced down to an equality test between different data types that can be coerced to the same data types then compared with regular boolean logic. Thanks for Jason Tomforde for asking the question on user@[1] that triggered the finding of this bug. Added these unit tests in r1713080, with @Ignore annotations applied to failing tests. [1] https://mail-archives.apache.org/mod_mbox/poi-user/201511.mbox/%3CSNT150-W744ED91F30ADE685B52EF4C5290%40phx.gbl%3E Thread: http://thread.gmane.org/gmane.comp.jakarta.poi.user/21417