Apache POI localized Date into Excel cell

Question

I have a problem. I'm use Apache POI 3.8 and I needed set's Date into Excel cell. But I don't understand - how to set the date depending on the user locale. Because date in US format is month/day/year, in russian locale - day/year/month. I'm use next code for my task

CellStyle dateStyle = workbook.createCellStyle();
dateStyle.setDataFormat(dataFormat.getFormat("m/d/yy"));
cell.setCellStyle(dateStyle);

If anybody know - how to set's cell Date value depending on the user locale, please, answer. Note. If I'm use "m/d/yy h:mm", then I not see my date in Excel file(I see only "######" string in cells). Thank you.

Answer 1

Try and expand the cells. Sometimes if the cell width is too short ###### is seen as the output. To get the localized pattern you can see this reference

Answer 2

Excel shows dates and numbers according to locale settings of the machine on which document is opened. So you don't need to care about it.

In your code you use pattern "m/d/yy". Excel automatically will convert it to "dd.MM.yyyy" in "de_DE" locale, "dd/MM/yyyy" in "en_GB" locale and so on.

You can test it changing locale of your machine (for Windows: Control Panel -> Clock, Language and Region -> Language -> Change date, time, or number). As you will see the same Excel document will be displayed differently for different locales.

---

About "######", you can use Sheet#autosizeColumn. But be careful with it, because as it is described in the documentation:

"This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing"

Answer 3

Locale can be set on formatListener

MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
formatListener = new FormatTrackingHSSFListener(listener, Locale.UK);