Bug 52928 - Provide utility methods to convert Java Date formats into Excel date formats
Summary: Provide utility methods to convert Java Date formats into Excel date formats
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: unspecified
Hardware: PC All
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-03-16 11:45 UTC by Jim Talbut
Modified: 2012-03-17 13:03 UTC (History)
0 users



Attachments
Example Excel document generated from the proposed code (160.03 KB, application/octet-stream)
2012-03-16 12:14 UTC, Jim Talbut
Details
Class file to convert from Java DateFormat to Excel custom number format (12.67 KB, text/plain)
2012-03-16 12:15 UTC, Jim Talbut
Details
sample output with highlited diffs (64.00 KB, application/vnd.ms-excel)
2012-03-17 13:03 UTC, Yegor Kozlov
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jim Talbut 2012-03-16 11:45:49 UTC
I need to convert java DateFormat patterns into Excel custom number formats.
This issue exists si that I can contribute what I end up with back to POI.
Comment 1 Jim Talbut 2012-03-16 12:14:00 UTC
Created attachment 28479 [details]
Example Excel document generated from the proposed code

This is a zip file containing a number of Excel files each of which demonstrates a particular Date/Time format for all available locales for the Sun Windows JVM.
Comment 2 Jim Talbut 2012-03-16 12:15:33 UTC
Created attachment 28480 [details]
Class file to convert from Java DateFormat to Excel custom number format
Comment 3 Yegor Kozlov 2012-03-17 12:39:35 UTC
Very cool, applied in r1301923 with some tweaks, junit included.

The testing is tricky. We should not rely on our eyes , you simply don't see  all the discrepancies if you don't know the locale language. 

To simplify testing I added two more columns: column D is the value returned by java.text.DateFormat and column E is a formula that returns TRUE if Excel formatted value in column C equals to its Java counterpart in column D.

The formula looks as follows:
TEXT(C6,G6)=D6

where C6 is date, not matter how it is formatted, excel takes the raw double value.
G6 is the format applied to the cell style, e.g. [$-41C]yyyy-MM-dd;@
and D6 is a string calculated in Java.

We expect that Excel and Java format equally and the formula in all rows will return TRUE. Unfortunately it is not so, but it is not a bug, rather MS Office and Java format locales slightly differently.  See attached example, not matched rows are marked in red. 

For the future, please consider enhancing this utility to support bi-directional conversion:
1. from java.text.DateFormat to Excel date pattern
and the other way around
2. from excel date pattern to java.text.DateFormat

The second option is important to properly format excel date cell as a string. 

Regards,
Yegor
Comment 4 Yegor Kozlov 2012-03-17 13:03:55 UTC
Created attachment 28483 [details]
sample output with highlited diffs