poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jim Talbut <jtal...@spudsoft.co.uk>
Subject Re: Excel date formats
Date Thu, 15 Mar 2012 19:21:27 GMT
Yegor,

You are right about toLocalizedPattern(), I got nowhere with that.

But using toPattern() and replacing DateFormat tokens with Excel tokens 
where necessary gave me the attached results which look right, to my 
untrained eye.
I'm afraid I have the linguistic skills of a brick, so I don't know if 
it really is right, but I think it's an accurate interpretation of what 
the Java DateFormat would produce.

What it definitely doesn't do is handle non-arabic numerals, RTL script 
or any of that - how well does Excel handle them?

Jim

On 15/03/2012 18:56, Yegor Kozlov wrote:
> 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<jtalbut@spudsoft.co.uk>  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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>


Mime
View raw message