Bug 50271

Summary: POI will always use "M/d/yy" to format a cell when user try to format a cell to date but without specifying format exactly even my locale is set to Germany.
Product: POI Reporter: Wu, Fan <zjuwufan>
Component: POI OverallAssignee: POI Developers List <dev>
Status: CLOSED INVALID    
Severity: normal    
Priority: P2    
Version: 3.7-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows Server 2003   
Attachments: Excel file to reproduce this issue.
Patch to fix this issue

Description Wu, Fan 2010-11-15 02:13:00 UTC
I'm trying to format a cell to date on a German machine.
What I expected is that the cell is formatted with format string "d.M.yy", but what I got is that the cell is formatted with format string "M/d/yy".

As a example, for the cell A3 in the attachment, what I expected is "22.11.2010", what I got is "11/22/2010".
Comment 1 Wu, Fan 2010-11-15 02:15:38 UTC
Created attachment 26297 [details]
Excel file to reproduce this issue.
Comment 2 Wu, Fan 2010-11-15 05:05:18 UTC
Created attachment 26298 [details]
Patch to fix this issue

I found that when the format index of one cell is equal to 14, there is no formatCode associated with it. So it will used 'M/d/yy' as a default one. Instead we should take the current locale into account.

I provided this patch. The fix is not elegant. I hope it could help somehow anyway :-).
Comment 3 Nick Burch 2010-11-15 06:34:02 UTC
If you want a date format that's always going to look the same no matter where you open it, then you need to set an explicit date format on that cell. Otherwise, it'll open in the default date format for the copy of excel.

If you always want d.m.yy, then explicitly set that as your cell format!

If someone was willing to figure out for each locale what excel uses as it's default format rules, we could possibly put in optional logic to use that, but that would need quite a bit of work that no-one has so far volunteered to do.
Comment 4 Wu, Fan 2010-11-15 07:11:47 UTC
(In reply to comment #3)
> If you want a date format that's always going to look the same no matter where
> you open it, then you need to set an explicit date format on that cell.
> Otherwise, it'll open in the default date format for the copy of excel.
> 
> If you always want d.m.yy, then explicitly set that as your cell format!
> 
> If someone was willing to figure out for each locale what excel uses as it's
> default format rules, we could possibly put in optional logic to use that, but
> that would need quite a bit of work that no-one has so far volunteered to do.


On MS excel, if no format explictly provided then it will use default format rules. I think this feature is useful for those softwares support multilple locales. And actually to get the default format is not complicated. We only need one line of code to do this.

java.text.DateFormat.getDateInstance(
                         java.text.DateFormat.SHORT, aLocale);


Thank you for your patience.