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.
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.
Created attachment 28480 [details] Class file to convert from Java DateFormat to Excel custom number format
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
Created attachment 28483 [details] sample output with highlited diffs