Have created bug
(https://issues.apache.org/bugzilla/show_bug.cgi?id=52928) and attached
files there.
First file is a zip containing 15 Excel files showing the result of the
code, and the second is the code.
The 15 Excel files had to be split into 15 files because otherwise it
uses too many custom number formats and Excel fails to load it :(
One problem with the code is that it always adds a locale prefix to the
number format, which is only necessary if the locale is different from
the Excel locale (but obviously I don't know the target Excel locale so
I don't see a choice).
The sample files were generated using this code (pulled from a JUnit test):
private void outputLocaleDataFormats( Date date, boolean dates,
boolean times, int style, String styleName ) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
String sheetName;
if( dates ) {
if( times ) {
sheetName = "DateTimes";
} else {
sheetName = "Dates";
}
} else {
sheetName = "Times";
}
Sheet sheet = workbook.createSheet(sheetName);
Row header = sheet.createRow(0);
header.createCell(0).setCellValue("locale");
header.createCell(0).setCellValue("DisplayName");
header.createCell(1).setCellValue(styleName);
header.createCell(3).setCellValue("Java");
header.createCell(5).setCellValue("Excel");
int rowNum = 1;
for( Locale locale : DateFormat.getAvailableLocales() ) {
Row row = sheet.createRow(rowNum++);
row.createCell(0).setCellValue(locale.toString());
row.createCell(1).setCellValue(locale.getDisplayName());
DateFormat dateFormat;
if( dates ) {
if( times ) {
dateFormat = DateFormat.getDateTimeInstance(style,
style, locale);
} else {
dateFormat = DateFormat.getDateInstance(style,
locale);
}
} else {
dateFormat = DateFormat.getTimeInstance(style, locale);
}
Cell cell = row.createCell(2);
cell.setCellValue(date);
CellStyle cellStyle =
row.getSheet().getWorkbook().createCellStyle();
String javaDateFormatPattern =
((SimpleDateFormat)dateFormat).toPattern();
String excelFormatPattern =
DateFormatConverter.convert(locale, javaDateFormatPattern);
DataFormat poiFormat =
row.getSheet().getWorkbook().createDataFormat();
cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
cell.setCellStyle(cellStyle);
row.createCell(3).setCellValue(javaDateFormatPattern);
row.createCell(4).setCellValue(excelFormatPattern);
}
File outputFile = createTempFile( "Locale" + sheetName +
styleName , ".XLSX");
FileOutputStream outputStream = new FileOutputStream(outputFile);
try {
workbook.write(outputStream);
} finally {
outputStream.close();
}
}
@Test
public void testJavaDateFormatsInExcel() throws Exception {
Date date = new Date();
outputLocaleDataFormats(date, true, false, DateFormat.DEFAULT,
"Default" );
outputLocaleDataFormats(date, true, false, DateFormat.SHORT,
"Short" );
outputLocaleDataFormats(date, true, false, DateFormat.MEDIUM,
"Medium" );
outputLocaleDataFormats(date, true, false, DateFormat.LONG,
"Long" );
outputLocaleDataFormats(date, true, false, DateFormat.FULL,
"Full" );
outputLocaleDataFormats(date, true, true, DateFormat.DEFAULT,
"Default" );
outputLocaleDataFormats(date, true, true, DateFormat.SHORT,
"Short" );
outputLocaleDataFormats(date, true, true, DateFormat.MEDIUM,
"Medium" );
outputLocaleDataFormats(date, true, true, DateFormat.LONG,
"Long" );
outputLocaleDataFormats(date, true, true, DateFormat.FULL,
"Full" );
outputLocaleDataFormats(date, false, true, DateFormat.DEFAULT,
"Default" );
outputLocaleDataFormats(date, false, true, DateFormat.SHORT,
"Short" );
outputLocaleDataFormats(date, false, true, DateFormat.MEDIUM,
"Medium" );
outputLocaleDataFormats(date, false, true, DateFormat.LONG,
"Long" );
outputLocaleDataFormats(date, false, true, DateFormat.FULL,
"Full" );
}
On 16/03/2012 08:44, Jim Talbut wrote:
Oh stupid me, now I see what I was missing - all my months and days
are in English and I'm too blinkered to have noticed!
Doh!
OK, so that locale prefix then...
On 15/03/2012 19:21, Jim Talbut wrote:
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<[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]
---------------------------------------------------------------------
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]
