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 2011/12/16 23:30:10 UTC
DO NOT REPLY [Bug 52349] New: FormulaEvaluator formats month of
dates different than Excel and cell.getStringCellValue()
https://issues.apache.org/bugzilla/show_bug.cgi?id=52349
Bug #: 52349
Summary: FormulaEvaluator formats month of dates different than
Excel and cell.getStringCellValue()
Product: POI
Version: unspecified
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
AssignedTo: dev@poi.apache.org
ReportedBy: mdlandon@gmail.com
Classification: Unclassified
Created attachment 28080
--> https://issues.apache.org/bugzilla/attachment.cgi?id=28080
Class + xlsx file that demonstrates bug described above.
If you have a spreadsheet containing date formatting formulas such as:
=TEXT(A1, "mmddyyyy")
the value displayed using a FormulaEvaluator will be different for the Month
than what Excel and cell.getStringCellValue().
For example, if you have "12/16/2011" in A1, the formula above will return
"00162011" using a FormulaEvaluator while Excel and cell.getStringCellValue()
will return "12162011".
Attached is a simple class and example Excel file that demonstrates the issue.
Found under java version "1.6.0_24" and "1.7.0" using poi-3.8-beta4
--
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 52349] FormulaEvaluator formats month of dates
different than Excel and cell.getStringCellValue()
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52349
--- Comment #3 from Nick Burch <ni...@alfresco.com> 2011-12-18 03:49:42 UTC ---
Looks like we'll need to add some extra logic to handle the differences in
cases for month vs minute
Are you able to track down (possibly using a debugger) where the code in
question is? (That'll speed up the process of fixing it quite a bit!)
--
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 52349] FormulaEvaluator formats month of dates
different than Excel and cell.getStringCellValue()
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52349
Nick Burch <ni...@alfresco.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |RESOLVED
Resolution| |FIXED
--- Comment #6 from Nick Burch <ni...@alfresco.com> 2011-12-20 07:21:38 UTC ---
As of r1221126, the TEXT function delegates the work to DataFormatter, which
already has code in it to handle the date formatting rules
As part of this, the fraction support in the TEXT function has been pulled out
to DataFormatter, which didn't previously support that
--
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 52349] FormulaEvaluator formats month of dates
different than Excel and cell.getStringCellValue()
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52349
--- Comment #2 from Michael L. <md...@gmail.com> 2011-12-17 15:28:56 UTC ---
Doesn't matter which month you pick, it does the same thing.
I originally tested both with and without a time component, but the time I used
had 0 minutes (and I got the same response in both cases). You are probably
right about it being a mix-up between month & minute, because if you adjust the
formula from:
=TEXT(A1, "mmddyyyy")
to
=TEXT(A1, "MMddyyyy")
then the FormulaEvaluator works correctly. Looking at Excel's help (under TEXT
function - Guidelines for date and time formats):
http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx?CTT=5&origin=HA010277524
it appears that Excel does not use the same formatting as Java's
SimpleDateFormat
--
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 52349] FormulaEvaluator formats month of dates
different than Excel and cell.getStringCellValue()
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52349
--- Comment #4 from Michael L. <md...@gmail.com> 2011-12-18 23:04:34 UTC ---
I'll try and run it through the debugger sometime this week & post results...
--
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 52349] FormulaEvaluator formats month of dates
different than Excel and cell.getStringCellValue()
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52349
--- Comment #5 from Michael L. <md...@gmail.com> 2011-12-19 16:49:32 UTC ---
Looks like the issue is probably in:
org.apache.poi.ss.formula.functions.TextFunction.TEXT.evaluate()
As the javadoc indicates, it's just using SimpleDateFormat:
* An implementation of the TEXT function
* TEXT returns a number value formatted with the given
* number formatting string. This function is not a complete implementation
of
* the Excel function. This function implements decimal formatting
* with the Java class DecimalFormat. For date formatting this function
uses
* the SimpleDateFormat class.
--
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 52349] FormulaEvaluator formats month of dates
different than Excel and cell.getStringCellValue()
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52349
Nick Burch <ni...@alfresco.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #1 from Nick Burch <ni...@alfresco.com> 2011-12-17 08:40:20 UTC ---
Are you able to try with a few other months? And also try for a cell which has
a time in it?
(I'm not sure if it's a december specific bug, or if there's confusion between
Month and Minute, or something else)
--
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 52349] FormulaEvaluator formats month of dates
different than Excel and cell.getStringCellValue()
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52349
Jeremy <rp...@yahoo.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Blocks| |52389
--
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