Apache POI date locale issue

Question

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?

Axel Richter · Accepted Answer · 2016-06-09T15:04:31.190

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);

There are more built in number formats. See https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html. But not all will work with all Excel versions in all languages. But the default currency formats (5,6,7,8) will also work mostly.

Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;


class BuiltInFormats {

 public static void main(String[] args) {
  try {

    Workbook wb = new XSSFWorkbook();

    CellStyle builtInShortDate = wb.createCellStyle();
    builtInShortDate.setDataFormat((short)14);
    CellStyle builtInCurrency = wb.createCellStyle();
    builtInCurrency.setDataFormat((short)8);

    Sheet sheet = wb.createSheet();

    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue(new java.util.Date());
    cell.setCellStyle(builtInShortDate);

    row = sheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue(1234.56);
    cell.setCellStyle(builtInCurrency);

    FileOutputStream os = new FileOutputStream("BuiltInFormats.xlsx");
    wb.write(os);
    os.close();

  } catch (IOException ioex) {
  }
 }
}

style.setDataFormat((short) 14); is what I had to do. Otherwise this is the magic one I was looking for. Thank you! — jbaranski, Jun 09 '16 at 14:06
@Not Sure: You are welcome. Couldn't test until now. Casting short is necessary of course. — Axel Richter, Jun 09 '16 at 15:08

score 1 · Answer 2 · answered Jun 08 '16 at 16:17

1

You can get the user (broswer's) Locale from the request instead of get it from the Server.

answered Jun 08 '16 at 16:17

Marco A. Hernandez

791
4
15

What the Java locale expect and what navigator.language from the browser gives you don't match up. For example en-US vs en_US, so one would have to map the differences and check on it. This seems like a mess, have you done this before? – jbaranski Jun 08 '16 at 20:01

score 1 · Answer 3 · edited May 23 '17 at 12:09

1

When you have the user locale, you can convert into Excel format using DateFormatConverter:

final DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.SHORT, locale)
final String pattern = DateFormatConverter.convert(locale, dateFormat);
final short excelFormat = createHelper.createDataFormat().getFormat(pattern);

EDIT: The usual way to deal with client locale is (AFAIK):

detect the browser locale using javascript (see this for example) then translate into the equivalent "java" Locale (aka the hard way)
expose some way for the user to choose its locale (small clickable flags, preference screen, etc, see this for example) and use that to set the user "java" Locale. (preferred way if you need i18n)

edited May 23 '17 at 12:09

Community

1
1

answered Jun 08 '16 at 16:34

Getting the locale or making poi use the client OS locale settings is the hard part, once that is acheived you can do that or something similar which is already much documented elsewhere, thanks though... – jbaranski Jun 08 '16 at 22:14
The problem is that your question is more talking about POI and Excel, hence my answer. Will edit my answer – Jun 09 '16 at 05:32
This doesn't address the problem of having it fall back on the OS settings, In any event I think it's the way the question was worded, thanks. – jbaranski Jun 09 '16 at 14:09

score -1 · Answer 4 · answered Aug 05 '16 at 10:04

-1

try setting locale

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

answered Aug 05 '16 at 10:04

nishant

955
2
11
27

Apache POI date locale issue

4 Answers4