Apache POI日期区域设置问题 [英] Apache POI date locale issue

问题描述

无需使用Apache POI(例如，您手动生成xml来创建电子表格)，则可以使用

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"

或

mso-number-format: "Short Date"

似乎无法使用Apache POI，您只能使用单一定义的日期格式，该格式始终依赖于物理服务器的语言环境或您硬编码的某些值，而不依赖于上述客户端OS的语言环境设置.

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

基本上，我想将"MM/dd/yyyy"替换为"Short Date"或"General Date"，但是这些选项不起作用.有人有想法么?我不能仅根据服务器所在的位置来获取语言环境，因为可能会碰到另一个国家(因此，我无法像其他答案所建议的那样在Java src中获取语言环境).

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

有人处理过吗?

推荐答案

在Excel中，有些内置的数据格式没有显式的数据格式字符串.其中之一是数字格式为14的日期".

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.

此格式在Excel中的显示方式取决于Excel语言版本和Windows系统的语言环境.zh_CN Excel将显示 m/d/yy .zh_CN Excel将显示 dd/mm/yyyy .De_DE Excel将显示 dd.mm.yyyy ...

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

还有更多内置数字格式.参见 https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html .但是，并非所有人都可以使用所有语言的所有Excel版本.但是默认货币格式(5、6、7、8)也可以正常使用.

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.

示例:

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

这篇关于Apache POI日期区域设置问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！