如何使用 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?

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

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 Spreadsheet (*.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屋！