Bug 61532

Summary: XSSFFormulaEvaluator.evaluateFormulaCell() replaces the formula and changes the cell type
Product: POI Reporter: Dave Neuer <david.neuer.vendor>
Component: SXSSFAssignee: POI Developers List <dev>
Severity: major    
Priority: P2    
Version: 3.17-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: POM to bring down POI and jUNIT
Unit test demonstrating that POI 3.17 still has the bug

Description Dave Neuer 2017-09-18 15:28:44 UTC
XSSFFormulaEvaluator.evaluateFormulaCell(), at least when used with a formula of type VLOOKUP in an SXSSFWorkbook, behaves the way the documentation says that evaluateInCell() is supposed to work; it replaces the actual value of the cell with the results of running the formula, and changes the cell type to whatever the type of the result is. So, the two methods behave identically.

The correct behavior, according to the documentation, is to simply update the result value cache.
Comment 1 Nick Burch 2017-09-18 21:34:20 UTC
Are you able to try with Apache POI 3.17, released last week, and see how that behaves?

If the problem remains, would you be able to write a short junit unit test (or failing that short standalone program) demonstrating the issue, so we can reproduce it to take a look?
Comment 2 Dominik Stadler 2017-09-19 12:50:04 UTC

*** This bug has been marked as a duplicate of bug 61148 ***
Comment 3 Dave Neuer 2017-09-19 18:17:32 UTC
I don't believe this is a duplicate of the linked issue; the description of that issue is different, though they may share an underlying cause.

I'll work on a small test case demonstrating the issue in the coming days.
Comment 4 Dominik Stadler 2017-09-19 19:21:37 UTC
I have now tested it as far as I can and added a unit-test via r1808932 which shows that the two methods behave as documented and expected after fixing bug 61148. 

Please verify it with a build which contains these changes and reopen the bug if it is still not fixed for you with some special combination of formula/call-order.

*** This bug has been marked as a duplicate of bug 61148 ***
Comment 5 Dave Neuer 2017-12-08 20:03:52 UTC
It works as documented for some functions, but not others.

I have a unit test which shows that it succeeds for SUM but fails for VLOOKUP. I'll attach a maven POM and the test.
Comment 6 Dave Neuer 2017-12-08 20:04:59 UTC
Created attachment 35594 [details]
POM to bring down POI and jUNIT
Comment 7 Dave Neuer 2017-12-08 20:09:08 UTC
Created attachment 35595 [details]
Unit test demonstrating that POI 3.17 still has the bug
Comment 8 Jason Goris 2018-01-16 20:17:08 UTC
Have there been any updates into this issue?
Comment 9 Dominik Stadler 2018-12-28 21:45:50 UTC
Should be fixed via r1849880 now, in SXSSF applying rich text formula results did change the cell type. 

Also the pre-evaluated value was not correctly written into the .xlsx file, making reading the file in again fail.