You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2017/08/30 15:30:07 UTC
[Bug 61472] New: Unable to evaluate dependent cell formula
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
Bug ID: 61472
Summary: Unable to evaluate dependent cell formula
Product: POI
Version: 3.13-FINAL
Hardware: PC
Status: NEW
Severity: blocker
Priority: P2
Component: POI Overall
Assignee: dev@poi.apache.org
Reporter: amol.gawali01@gmail.com
Target Milestone: ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61472] Unable to evaluate dependent cell formula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
PJ Fanning <fa...@yahoo.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--- Comment #1 from PJ Fanning <fa...@yahoo.com> ---
Could you try POI 3.16 and 3.17-RC1 to see if the issue has been fixed in newer
versions?
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61472] Unable to evaluate dependent cell formula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |FIXED
Status|NEW |RESOLVED
--- Comment #7 from Yegor Kozlov <ye...@dinom.ru> ---
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
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61472] Unable to evaluate dependent cell formula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
--- Comment #4 from Amol Gawali <am...@gmail.com> ---
Tried with POI 3.16 and 3.17-RC1, but still issue is persist.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61472] Unable to evaluate dependent cell formula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
Amol Gawali <am...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |amol.gawali01@gmail.com
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61472] Unable to evaluate dependent cell formula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
--- Comment #2 from PJ Fanning <fa...@yahoo.com> ---
If the issue still appears in newer POI versions, would you be able to provide
a spreadsheet file that demonstrates the issue?
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61472] Unable to evaluate dependent cell formula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
Amol Gawali <am...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |Windows 7
Hardware|PC |HP
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61472] Unable to evaluate dependent cell formula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
--- Comment #6 from Dominik Stadler <do...@gmx.at> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61472] Unable to evaluate dependent cell formula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
--- Comment #5 from Amol Gawali <am...@gmail.com> ---
(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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61472] Unable to evaluate dependent cell formula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
--- Comment #3 from Amol Gawali <am...@gmail.com> ---
Created attachment 35270
--> https://bz.apache.org/bugzilla/attachment.cgi?id=35270&action=edit
Sample spreadsheet
Attached sample spreadsheet for same
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61472] Unable to evaluate dependent cell formula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
Amol Gawali <am...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Version|3.13-FINAL |3.17-FINAL
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org