| 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> |
| Status: | RESOLVED FIXED | ||
| 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
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
|