Not Sure Not Sure - 5 months ago 78
Java Question

Apache POI date locale issue

Without using Apache POI (for example, you generate the xml manually to create the spreadsheet) you can set a css class with

mso-number-format: "General Date"


or

mso-number-format: "Short Date"


Using Apache POI this doesn't seem possible, you are locked into using a single defined date format which is always reliant on the locale of the physical server or some value you hardcode, not on the clients OS locale settings like above.

Here is my code now, users hitting a server in America but live in another country want to see dates in their locale so this is not good

protected CellStyle getDateCellStyle(SXSSFWorkbook wb)
{
CellStyle style = wb.createCellStyle();
style .setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("MM/dd/yyyy"));
return style;
}


Essentially I would like to replace "MM/dd/yyyy" with "Short Date" or "General Date" but these options do not work. Anyone have any ideas? I can't just get the locale based on where the server sits because another country could be hitting it (so I can't get the locale in the Java src as other answers suggested).

Anyone dealt with this before?

Answer

In Excel there are some built in data formats which does not have explicit data format strings. One of those is "Date" with number format id 14.

How this format is displayed in Excel, depends on the Excel language version and the locale of the Windows system. An en_US Excel will display m/d/yy. An en_GB Excel will display dd/mm/yyyy. An de_DE Excel will display dd.mm.yyyy...

So if that is the need, then use

style.setDataFormat((short)14);