|Summary:||Provide utility methods to convert Java Date formats into Excel date formats|
|Product:||POI||Reporter:||Jim Talbut <jim-apache>|
|Component:||POI Overall||Assignee:||POI Developers List <dev>|
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: 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