CODEWITHSUNDEEP
javaexcelapache-poi
Jeff Baranski
Jeff Baranski

Reputation: 1269

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?

Upvotes: 1

Views: 7301

Answers (4)

nishant
nishant

Reputation: 985

try setting locale

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

Upvotes: -1

Axel Richter
Axel Richter

Reputation: 61880

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) {
  }
 }
}

Upvotes: 6

user180100
user180100

Reputation:

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)

Upvotes: 1

Marco A. Hernandez
Marco A. Hernandez

Reputation: 821

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

Upvotes: 1

Related Questions