Bug 50271 - 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.
Summary: POI will always use "M/d/yy" to format a cell when user try to format a cell ...
Status: CLOSED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: 3.7-FINAL
Hardware: PC Windows Server 2003
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-11-15 02:13 UTC by Wu, Fan
Modified: 2010-11-15 07:11 UTC (History)
0 users



Attachments
Excel file to reproduce this issue. (9.26 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2010-11-15 02:15 UTC, Wu, Fan
Details
Patch to fix this issue (1.52 KB, patch)
2010-11-15 05:05 UTC, Wu, Fan
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
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.