Bug 61472 - Unable to evaluate dependent cell formula
Summary: Unable to evaluate dependent cell formula
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: 3.17-FINAL
Hardware: HP All
: P2 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-08-30 15:30 UTC by Amol Gawali
Modified: 2019-03-16 15:43 UTC (History)
1 user (show)



Attachments
Sample spreadsheet (6.49 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-08-31 08:09 UTC, Amol Gawali
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Amol Gawali 2017-08-30 15:30:07 UTC
I am evaluating dependent cell formula using workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();

but while second formula evaluation, POI returns error 15 for cell.getErrorCellValue()

Example : 

Cell A2 contains formula =TEXT(NOW(), "hh:mm:ss") and
Cell B2 contains formula =TEXT(TIME(HOUR(A2),MINUTE(A2)+2,SECOND(A2)+2),"hh:mm:ss")

In POI, first formula evaluate successfully, but while second formula evaluation it return error cell type.
Comment 1 PJ Fanning 2017-08-31 07:52:26 UTC
Could you try POI 3.16 and 3.17-RC1 to see if the issue has been fixed in newer versions?
Comment 2 PJ Fanning 2017-08-31 07:54:11 UTC
If the issue still appears in newer POI versions, would you be able to provide a spreadsheet file that demonstrates the issue?
Comment 3 Amol Gawali 2017-08-31 08:09:06 UTC
Created attachment 35270 [details]
Sample spreadsheet

Attached sample spreadsheet for same
Comment 4 Amol Gawali 2017-08-31 08:10:13 UTC
Tried with POI 3.16 and 3.17-RC1, but still issue is persist.
Comment 5 Amol Gawali 2017-09-01 04:56:54 UTC
(In reply to PJ Fanning from comment #2)
> If the issue still appears in newer POI versions, would you be able to
> provide a spreadsheet file that demonstrates the issue?

Tried with latest version of POI, but still issue is persist.
Attached sample spreadsheet for same.
Comment 6 Dominik Stadler 2017-09-19 20:43:02 UTC
The problem happens because the first cell is a string-value via TEXT(...), but the second cell reads it as time for the HOUR() function, which Excel seems to somehow manage, but Apache POI does not. 

You might be able to change the first cell to be only "NOW()" and use formatting to get the desired format display, while still keeping the date-value as formula-result.
Comment 7 Yegor Kozlov 2019-03-16 15:43:43 UTC
Fixed in r1855662

The root cause was that OperandResolver#coerceValueToDouble didn't  recognize date/time strings and failed for inputs like "12:24" or "2019/01/13". 

If a string represents date or time Excel converts it to number automatically. The following examples are valid MS Excel formulas:

=TEXT(NOW(),"hh:mm:ss") + 0
=TEXT(NOW(),"H:mm AM/PM") + 0
="2005/01/01" + 0
="2018/01/01 12:33:54" + 0

Date strings are evaluated as a number of days since January 1, 1900, for example,
="1900/01/01" + 0   evaluates to 1  
="1900/01/02" + 0   evaluates to 2  
="2019/01/19" + 0   evaluates to 43483

Time is evaluated as a decimal number ranging from 0  to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
="00:00" + 0   evaluates to 0.0
="12:00" + 0   evaluates to 0.5
="23:59:59" + 0   evaluates to 0.99988426

Date and time can be used in combination, e.g.
="2019/1/18  3:43:00 PM"+0 evaluates to 43483.65486

The valid separator for date is '/' (slash) and for time is ':' (colon). Any other separators such as '-' result in #VALUE!, for example,
="1900-01-01" + 0   evaluates to #VALUE!

This fix also applies to the VALUE function which converts a text string that represents a number to a number . 
="2019/1/18  3:43:00 PM"+0 
is equivalent to 
=VALUE("2019/1/18  3:43:00 PM")+0 

You can use implicit conversion of VALUE, the result will be the same.

Regards,
Yegor