Bug 52349 - FormulaEvaluator formats month of dates different than Excel and cell.getStringCellValue()
Summary: FormulaEvaluator formats month of dates different than Excel and cell.getStri...
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: unspecified
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Blocks: 52389
  Show dependency tree
Reported: 2011-12-16 22:30 UTC by Michael L.
Modified: 2011-12-27 17:05 UTC (History)
0 users

Class + xlsx file that demonstrates bug described above. (6.30 KB, application/octet-stream)
2011-12-16 22:30 UTC, Michael L.

Note You need to log in before you can comment on or make changes to this bug.
Description Michael L. 2011-12-16 22:30:10 UTC
Created attachment 28080 [details]
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
Comment 1 Nick Burch 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)
Comment 2 Michael L. 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")
    =TEXT(A1, "MMddyyyy")
then the FormulaEvaluator works correctly.  Looking at Excel's help (under TEXT function - Guidelines for date and time formats):


it appears that Excel does not use the same formatting as Java's SimpleDateFormat
Comment 3 Nick Burch 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!)
Comment 4 Michael L. 2011-12-18 23:04:34 UTC
I'll try and run it through the debugger sometime this week & post results...
Comment 5 Michael L. 2011-12-19 16:49:32 UTC
Looks like the issue is probably in:

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.
Comment 6 Nick Burch 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