Bug 66181 - Issue with VALUE function and how it handles empty text input
Summary: Issue with VALUE function and how it handles empty text input
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 5.2.2-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2022-07-27 08:17 UTC by sam.chen
Modified: 2022-11-23 10:36 UTC (History)
0 users

Value Function Of Blank (8.77 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2022-07-27 08:17 UTC, sam.chen

Note You need to log in before you can comment on or make changes to this bug.
Description sam.chen 2022-07-27 08:17:26 UTC
Created attachment 38347 [details]
Value Function Of Blank


FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

The CellValue object of A1. should be CellType.ERROR instead of CellType.NUMERIC.

The issue when this is that the generated Excel will not mark cell A1 in Red, resulting in possible escape.
Comment 1 PJ Fanning 2022-08-01 15:59:51 UTC
I added a test case - r1903170 

It looks like a bug alright.

One workaround is to use:

            Cell a1 = row.getCell(0);
            assertEquals(CellType.FORMULA, a1.getCellType());
            assertEquals(CellType.ERROR, a1.getCachedFormulaResultType());

These 2 assertions return the correct results. The issue is with evaluator.evaluate(...).
Comment 2 PJ Fanning 2022-08-01 16:07:23 UTC
I think r1903171 fixes this
Comment 3 sam.chen 2022-08-05 03:42:04 UTC
(In reply to PJ Fanning from comment #2)
> I think r1903171 fixes this

Hi, Which version will this be on?
Comment 4 PJ Fanning 2022-08-15 09:53:31 UTC
This will be in next release (5.2.3) which can be tracked at https://poi.apache.org/changes.html
Comment 5 Miłosz Rembisz 2022-11-14 07:54:11 UTC

I believe this change broke other related behaviour - referencing blank cell. Having empty cell A1 I whould expect formula "=VALUE(A1)" to evaluate to zero, instead it now returns "#VALUE!". Interestingly enough, chaining reference (empty A1, B1 = "=A1", C1 = "=VALUE(B1)") works well.

I don't know if this behaviour (value on reference to empty cell evaluating to zero) is desired - I could confirm that LibreOffice and Excel 365 does that.

I've created pull request https://github.com/apache/poi/pull/397 to illustrate the issue (and potential fix).
Comment 6 PJ Fanning 2022-11-14 17:41:56 UTC
I added r1905308