如何使用Apache POI(SXSSF)设置特定单元格的数据(数字)格式语言环境? [英] How to set data (number) format locale for specific cell using Apache POI (SXSSF)?

问题描述

问题非常具体:使用Apache POI，我要创建单元格(完成)，为其分配数字格式(完成)，并设置格式的语言环境(贴在这里).

The problem is very concrete: using Apache POI, I want to create cell (done), assign number format to it (done), and set format's locale (stuck here).

代码如下:

SXSSFWorkbook workbook = new SXSSFWorkbook(100);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(1);
Cell cell = row.createCell(0);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(8); //currency with thousands separator and two decimal places after period
cell.setCellValue(123456.78);
//??? how to set custom locale for the cell's number format?

我要使用自定义语言环境解决的问题是自定义数千个分隔符char(法语的不间断空格对我来说是可以的).

The problem that I'm trying to solve with custom locale is customizing thousands separator char (French's non-breaking space is OK for me).

XLSX工作簿允许进行此类自定义(更新:我的意思是每个单元格设置格式区域设置)，而MS Office和OpenOffice均可实现.我想在代码中做同样的事情.

XLSX workbooks allow such customization (update: I mean setting format locale per cell), this is achievable with both MS Office and OpenOffice. I want to do the same in code.

(Apache POI 3.12)

(Apache POI 3.12)

推荐答案

在Offixe OpenXML( *.xlsx )中，对于货币数字格式，只能对货币符号进行本地化，而不能对小数点分隔符进行本地化.小数点分隔符来自运行Excel的Windows系统的Windows系统区域设置.并且千位定界符还默认为运行Excel的Windows系统的Windows系统区域设置.

In Offixe OpenXML (*.xlsx) for currency number format only the currency symbol can be localized but not the decimal separator. The decimal separator comes from Windows system locale settings of the Windows system the Excel is running on. And the thousands delimiter also defaults to the Windows system locale settings of the Windows system the Excel is running on.

在Excel中，它看起来像:

In Excel this looks like:

如您所见，只能对货币符号进行本地化.

As you see only the currency symbol can be localized.

至少可以使用格式字符串设置上千个定界符.因此格式字符串可能是

At least the thousands delimiter can be set using a format string. So a format string could be

#\\ ### \\ ## 0.00 \\ [$€-40C]; [RED] \\-#\\ ### \\ ## 0.00 \\ [$€-40C]".

这是具有本地化的法国欧元货币符号和空格作为千​​位分隔符的货币数字格式.因为我们伪装成千上万个定界符，所以我们必须在格式字符串中提供所需的数字.

This is currency number format having localized french Euro currency symbol and space as the thousands delimiter. Because we are faking the thousands delimiter, we have to give as much digits as needed in the format string.

小数点分隔符是默认值，这意味着它来自运行Excel的Windows系统的Windows系统区域设置.因此，格式字符串中的点.并不意味着始终使用点作为十进制定界符，而是使用十进制定界符，它来自运行Excel的Windows系统的Windows系统区域设置.如果我们将逗号，用作格式字符串中的千位分隔符，那么这也将使用千位分隔符，该分隔符来自运行Excel的Windows系统的Windows系统区域设置.然后，我们不需要在格式字符串中提供太多数字，因为这样一来，数千个定界符设置将每隔数千个数字重复一次.所以

The decimal separator is the default, which means it comes from Windows system locale settings of the Windows system the Excel is running on. So the dot . within the format string does not means to always use a dot as decimal delimiter but to use the decimal delimiter which comes from the Windows system locale settings of the Windows system the Excel is running on. And if we would had used comma , as the thousands delimiter in the format string, this also would had used the thousands delimiter which comes from the Windows system locale settings of the Windows system the Excel is running on. And then we would had not need giving so much digits in the format string because then the thousands delimiter settings would repeat every thousands digits. So

#，## 0.00 \\ [$€-40C]; [RED] \\-#，## 0.00 \\ [$€-40C]"

就足够了.

示例:

import java.io.FileOutputStream;

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

import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;

public class CreateExcelNumberFormat {

 public static void main(String[] args) throws Exception {
  SXSSFWorkbook workbook = new SXSSFWorkbook(100);

  DataFormat dataformat = workbook.createDataFormat();
  CellStyle cellStyleCurrency = workbook.createCellStyle();
  cellStyleCurrency.setDataFormat(dataformat.getFormat("#\\ ###\\ ##0.00\\ [$€-40C];[RED]\\-#\\ ###\\ ##0.00\\ [$€-40C]")); 

  Sheet sheet = workbook.createSheet();
  Row row = sheet.createRow(1);
  Cell cell = row.createCell(0);

  cell.setCellValue(123456.78);
  cell.setCellStyle(cellStyleCurrency);

  ((SXSSFSheet)sheet).trackColumnForAutoSizing(0);
  sheet.autoSizeColumn(0);

  workbook.write(new FileOutputStream("CreateExcelNumberFormat.xlsx"));
  workbook.close();
  workbook.dispose();
 }
}

但是，这与Libreoffice OpenDocument电子表格格式中可用的本地化货币格式不同.看起来像:

But this is not the same as the localized currency format which is usable in Libreoffice OpenDocument Spreadsheet format. This looks like:

正如您在此处看到的那样，货币符号和整个格式的语言都可以本地化.

As you see here both, the currency symbol and the language of the whole format, can be localized.

但是Office OpenXML( *.xlsx )无法存储本地化的货币编号格式.OpenDocument电子表格( *.ods )是OpenOffice/Libreoffice的本机格式，可以保存本地化的货币数字格式，但是如果Excel将打开此类文件，则本地化将丢失.

But it is that the Office OpenXML (*.xlsx) cannot store localized currency number formats. OpenDocument Spreadsheet (*.ods), which is the native format of OpenOffice/Libreoffice, can save localized currency number formats, but if Excel will open such a file, the localization will be lost.

OpenOffice/Libreoffice的语言"组合框的设置不能存储在 *.xlsx 中，也不能从OpenOffice/Libreoffice存储.在OpenOffice/Libreoffice中设置默认值以外的其他内容，将文件另存为 *.xlsx ，关闭OpenOffice/Libreoffice，在OpenOffice/Libreoffice中打开存储的 *.xlsx 文件再次.您将看到语言"被重置为默认值.

The settings of the "Language" combo-box of OpenOffice/Libreoffice cannot be stored in *.xlsx, also not from OpenOffice/Libreoffice. Set something else than the default there in OpenOffice/Libreoffice, save the file as *.xlsx, close OpenOffice/Libreoffice, open the stored *.xlsx file in OpenOffice/Libreoffice again. You will see the "Language" is reseted to the default.

这篇关于如何使用Apache POI(SXSSF)设置特定单元格的数据(数字)格式语言环境?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！