Jim,
It looks like a very promising contribution, but it is not that simple
as you think.
The string returned by SimpleDateFormat.toLocalizedPattern() is not
recognized by Excel in general case.
For example, let's take the Hebrew locale and LONG format,
SimpleDateFormat.toLocalizedPattern() returns "j nnnn aaaa" and it
is not the Excel syntax.
If you apply a Hebrew date format in Excel ( use the .xlsx format,
then unzip and examine styles.xml) then the format string looks like
this:
[$-101040D]d mmmm yyyy;@
It is close to what SimpleDateFormat.toPattern() returns ("d MMMM
yyyy"), except the prefix and suffix:
The prefix is the key to set locale. The best explanation of its
syntax I found in the OOXML spec in chapter 18.8.31 numFmts (Number
Formats):
Syntax is [$<Currency String>-<language info>]. Currency string is a
string to use as a
currency symbol. Language info is a 32-bit value entered in hexidecimal format.
Language info format (byte 3 is most significant byte):
Bytes 0,1: 16-bit Language ID (LID).
Byte 2: Calendar type. High bit indicates that input is parsed using
specified calendar.
Byte 3: Number system type. High bit indicates that input is parsed
using specified
number system.
So, [$-101040D] tells Excel to use the Hebrew locale.
I didn't find further explanation how to set Language ID and number
system, it should be in the spec I hope.
If I implemented such an utility, I'd use SimpleDateFormat.toPattern()
that uses syntax compatible with Excel (dd/mm/yy) and add a prefix
built based on the Locale .
Regards,
Yegor
On Thu, Mar 15, 2012 at 11:57 AM, Jim Talbut <[email protected]> wrote:
> Hi,
>
> Is there a function (or does anyone have one that they are prepared to
> share) for converting Java data formats into Excel?
>
> I need to be able to convert DEFAULT, SHORT, MEDIUM, LONG to formats that
> work in Excel for any locale.
> At the moment I am doing this:
> private void dumpSingleDateFormat( Row row, Locale locale, int style, int
> colnum, Date date ) {
>
> DateFormat dateFormat = DateFormat.getDateInstance(style, locale);
>
> Cell cell = row.createCell(colnum);
> cell.setCellValue(date);
> CellStyle cellStyle = row.getSheet().getWorkbook().createCellStyle();
>
> // Note that this cast may be Sun-JVM specific
> String dateFormatPattern =
> ((SimpleDateFormat)dateFormat).toLocalizedPattern();
>
> DataFormat poiFormat =
> row.getSheet().getWorkbook().createDataFormat();
> cellStyle.setDataFormat(poiFormat.getFormat(dateFormatPattern));
>
> cell.setCellStyle(cellStyle);
> }
> for every locale and DateFormatter style.
>
> This works for most locales, but for some the pattern used by Java differs
> from that used by Excel.
> So I want to run the pattern through a function to make it work.
>
> Q1. Does anyone have such a function that they can share?
> Q2. If not, do you want a copy of whatever I end up with?
> Not being a localization expert, if I write the function I can't guarantee
> that the output is correct, but at least it won't make Excel barf.
>
> Jim
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
