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 2010/01/12 06:01:06 UTC
DO NOT REPLY [Bug 48528] New: An Error in Date() function
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
Summary: An Error in Date() function
Product: POI
Version: 3.5-FINAL
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: major
Priority: P2
Component: XSSF
AssignedTo: dev@poi.apache.org
ReportedBy: jaibabus@gmail.com
Hai Experts,
While creating an Excel Template with POI 3.5-FINAL API the DATE()
function works abnormally. I used the following function
DATE(YEAR(A20),MONTH(A20)-1,DAY(A20))
ie., DATE(YEAR(previouscolumn value),MONTH(PreviousColumn Value)-1,
DAY(PreviousCOlumn
Value))
The above formula is working for some date ranges. If the previous column
value is "February-09" it is correctly calculating the next value as
"January-09"
using the above formula. But it fails after that. After "January-09" the next
evaluation of the formula is not working properly saying "#Value" in the column
with out any error such as "Not Yet Implemented". If i keep the cursor in the
particular cell and i press enter without changing the value or formula then
the excel calculates the value correctly. Hope this is a bug in POI 3.5-Final
API. How can i overcome this.
Thanks
Jaibabu
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
Nick Burch <ni...@alfresco.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #10 from Nick Burch <ni...@alfresco.com> 2012-01-05 02:08:45 UTC ---
Could someone please re-test with POI 3.8 beta 5 (the latest beta release), and
confirm if the problem is fixed there or not?
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
Jaibabu <ja...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Priority|P2 |P1
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
--- Comment #7 from Jaibabu <ja...@gmail.com> 2010-01-24 20:00:20 UTC ---
Hai Experts,
I hope the explanation i gave was enough. Please have a look in this issue.
Thanks
Jaibabu.J
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
Re: DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by MSB <ma...@tiscali.co.uk>.
Nothing of any real import to add to this discussion, but I do have a
question. Assuming that it is the -1 that is causing the problem and
accepting that it is possible to add one to the value, what happens if you
add minus one to the value? Something like this;
DATE(YEAR(B6), (MONTH(B6)+(-1)), DAY(B6))
That is assuming of course that Excel, and POI, can understand negative
numbers and I cannot see why they would not.
Yours
Mark B
PS Do not know if Excel will accept that bracketting; I have done it to make
the example more readable.
Bugzilla from bugzilla@apache.org wrote:
>
> https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
>
> --- Comment #6 from Jaibabu <ja...@gmail.com> 2010-01-21 00:08:41 UTC
> ---
> Hai Josh,
>
> Thanks for your response. I herewith attaching the Java file i used
> for
> writing Excel file using POI. (The java code reads a template excel file
> and
> generates the new Excel file with new values.)
>
> I am also attaching the "DemoTemplateFile.xlsx" which is used as a
> template
> file(from which the POI reads the format and formula) and
> "sampledemo.xlsx"
> file that POI generates using java code.
>
> In sampledemo.xlsx file please check the column "B12-B19" the Date
> formula
> is not evaluated correctly. But it evaluates the Date formula above that
> cell
> (B7-B11) correctly.
> The formula used in that column is DATE( YEAR( B6 ), MONTH( B6 )-1,
> DAY( B6
> ) )
> Assuming -1 is the problem in evaluation.
>
> If suppose if i use +1 instead it is evaluating correctly. Please
> refer
> Column (A7-A19).
>
> I used formula Evaluator to evaluate the formula cells in the Sheet.
> But i
> get the same result.
>
> Hope this may be useful for you.
>
> Thanks
> Jaibabu.J
>
> --
> Configure bugmail:
> https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
> ------- 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
>
>
>
--
View this message in context: http://old.nabble.com/DO-NOT-REPLY--Bug-48528--New%3A-An-Error-in-Date%28%29-function-tp27122697p27303208.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
--- Comment #6 from Jaibabu <ja...@gmail.com> 2010-01-21 00:08:41 UTC ---
Hai Josh,
Thanks for your response. I herewith attaching the Java file i used for
writing Excel file using POI. (The java code reads a template excel file and
generates the new Excel file with new values.)
I am also attaching the "DemoTemplateFile.xlsx" which is used as a template
file(from which the POI reads the format and formula) and "sampledemo.xlsx"
file that POI generates using java code.
In sampledemo.xlsx file please check the column "B12-B19" the Date formula
is not evaluated correctly. But it evaluates the Date formula above that cell
(B7-B11) correctly.
The formula used in that column is DATE( YEAR( B6 ), MONTH( B6 )-1, DAY( B6
) )
Assuming -1 is the problem in evaluation.
If suppose if i use +1 instead it is evaluating correctly. Please refer
Column (A7-A19).
I used formula Evaluator to evaluate the formula cells in the Sheet. But i
get the same result.
Hope this may be useful for you.
Thanks
Jaibabu.J
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
Jaibabu <ja...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--- Comment #5 from Jaibabu <ja...@gmail.com> 2010-01-21 00:06:56 UTC ---
Hai Josh,
Thanks for your response. I herewith attaching the Java file i used for
writing Excel file using POI. (The java code reads a template excel file and
generates the new Excel file with new values.)
I am also attaching the "DemoTemplateFile.xlsx" which is used as a template
file(from which the POI reads the format and formula) and "sampledemo.xlsx"
file that POI generates using java code.
In sampledemo.xlsx file please check the column "B12-B19" the Date formula
is not evaluated correctly. But it evaluates the Date formula above that cell
(B7-B11) correctly.
The formula used in that column is DATE( YEAR( B6 ), MONTH( B6 )-1, DAY( B6
) )
Assuming -1 is the problem in evaluation.
If suppose if i use +1 instead it is evaluating correctly. Please refer
Column (A7-A19).
I used formula Evaluator to evaluate the formula cells in the Sheet. But i
get the same result.
Hope this may be useful for you.
Thanks
Jaibabu.J
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
Nick Burch <ni...@alfresco.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |RESOLVED
Resolution|--- |FIXED
--- Comment #12 from Nick Burch <ni...@alfresco.com> ---
Fixed in r1331796.
--
You are receiving this mail because:
You are the assignee for the bug.
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
--- Comment #8 from Jaibabu <ja...@gmail.com> 2010-02-03 01:20:24 UTC ---
Hai,
Is there any thing need to explain about the problem more?
Please reply.
Thanks
Jaibabu.J
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
--- Comment #4 from Jaibabu <ja...@gmail.com> 2010-01-21 00:06:33 UTC ---
Created an attachment (id=24873)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=24873)
Excel file created Using POI3.5
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
brezelman@yahoo.de changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |brezelman@yahoo.de
--- Comment #9 from brezelman@yahoo.de 2012-01-04 12:43:47 UTC ---
This bug is still present in poi 3.7 final. Excel interprets carries in the
date formula function, e.g. DATE(2012;MONTH(TODAY())-1;1) is evaluated to
2011-12-01 in January; DATE(2012;MONTH(TODAY())-13;1) is evaluated to
2010-12-01 then. It's the same with positive values and for carries at the date
value.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
--- Comment #2 from Jaibabu <ja...@gmail.com> 2010-01-21 00:03:45 UTC ---
Created an attachment (id=24871)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=24871)
Java file to generate Excel using POI3.5
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
--- Comment #3 from Jaibabu <ja...@gmail.com> 2010-01-21 00:05:23 UTC ---
Created an attachment (id=24872)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=24872)
Template Excel file that POI reads
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #1 from Josh Micich <jo...@gildedtree.com> 2010-01-20 08:54:01 UTC ---
You seem to be describing unexpected behaviour from Excel, but we're going to
need explicit details on how POI produced this workbook. Please upload some
simple java code to produce this error (If relevant please add example
input/output files).
Another observation that may be useful: Does Excel fix the value if you force a
recalculation of the whole sheet (instead of re-parsing the single cell as you
described)?
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
DO NOT REPLY [Bug 48528] An Error in Date() function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48528
--- Comment #11 from brezelman@yahoo.de 2012-01-05 07:34:14 UTC ---
I checked the SVN code. Implementation of Date function doesn't deal with any
carry. SVN history (log) of DateFunc.java only mentions elimination of
dependency on HSSF and reduction of eclipse warnings after 3.7 release.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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