Bug 52928

Summary: Provide utility methods to convert Java Date formats into Excel date formats
Product: POI Reporter: Jim Talbut <jim-apache>
Component: POI OverallAssignee: POI Developers List <dev>
Severity: enhancement    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Example Excel document generated from the proposed code
Class file to convert from Java DateFormat to Excel custom number format
sample output with highlited diffs

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:

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. 

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