apache poi language version causes date format misinterpretation

Question

Edit: To avoid confusions, here's an (again) improved version of my original question:

QUESTION How can I read out the String representation of a cell just the way Microsoft Excel would display it, without knowing respective hard-coding the formatting-settings (but for example using the cell's own formatting settings to format the output).

ANSWER SO FAR This will be no problem if you are using the US-version of excel, for all other versions Apache POI will return strings equivalent to how the US-Version of excel will display it and there is no built-in way in Apache POI to change this. Only way I can confirm is "working" is - as Gagravarr suggested - to switch the language on my Mac to US/EN. Then Apache POI and Excel will deliver the same result. Which is kind of a inverse solution.

The problem is: A cell is displayed like 01.12.13 in the german version of excel, but since it's formatted as a Date

HSSFCell cell = (HSSFCell) cellIter.next();
HSSFDataFormatter formatter=new HSSFDataFormatter();
String val=formatter.formatCellValue(cell)

gives me a val of 21/01/13.

the original question was:

I am having a xlsx file containing date fields that are displayed in Excel like

01.12.13 using the format setting of the cell TT.MM.JJ

If I read the same file with apache poi, getting the cell and format à la

HSSFCell cell = (HSSFCell) cellIter.next();
HSSFDataFormatter formatter=new HSSFDataFormatter();
String val=formatter.formatCellValue(cell)

(with cellIter being a valid Iterator of course, simplified example)

my val turns out to be 21/01/13. As I understand the HSFDataFormatter's method formatCellValue() should return a String of the value formatted with the cell's format setting.

Does anybody know how I can get the val to contain the same format as in Excel?

I appreciate any help! Thank you!

I'm fairly sure your file won't actually have Ts and Js in the format string, I suspect that's the "magic" Excel localisation kicking in. If you open your file on a machine in a US locale, does it show there the same way that POI does? — Gagravarr
Yay! You are totally right when I switch my Mac to English the formatting in Excel is just the same as in Apache POI! For a workaround I just somehow need to know how to read out the format settings of the cell. — Moritz Walter
Excel has two kinds of format strings, those which "magically" localise themselves, and those that look the same everywhere. I'd suggest your format your cells explicitly as the latter, firstly so that POI is able to get what you want out, and secondly so anyone else you send the file to sees it the way you do too! — Gagravarr
The problem ist that my client gets those excel sheets by his client and it's not gonna happen that they're gonna format their sheets correctly. For most offices Excel-Formatting is still Voodoo. Just poke it until it looks right. Once I find an elegant solution for this I'll post it here. Thank you all for your input! — Moritz Walter

Sankumarsingh Sankumarsingh · Accepted Answer · 2013-08-26T08:50:01

You can customize your date format as following:

HSSFCell cell = (HSSFCell) cellIter.next();
SimpleDateFormat DtFormat = new SimpleDateFormat("dd.MM.YY");
Date date=cell.getDateCellValue();
System.out.println(DtFormat.format(date).toString());

This will print the value as 21.01.13.

apache poi language version causes date format misinterpretation

1 Answers