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.
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?
*** This bug has been marked as a duplicate of bug 61148 ***
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.
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 ***
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.
Created attachment 35594 [details] POM to bring down POI and jUNIT
Created attachment 35595 [details] Unit test demonstrating that POI 3.17 still has the bug
Have there been any updates into this issue?
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.