Apache POI 自定义数据格式修改 [英] Apache POI custom data format is modified

问题描述

我正在使用 Apache POI 更改 Excel 文件并将一些数据放入其中.我尝试的大部分内容都很顺利.但是我的货币格式没有 100% 正确...

I'm using Apache POI to alter an Excel file and put some data in it. Most of what I try goes well. However my currency formatting is not done 100% correctly...

我想要的是我的货币格式为 €1.000,43.所以左边的欧元符号，右边的金额，以点为千位分隔符，逗号为小数分隔符和两位小数.

What I want is my currencies to be format like € 1.000,43. So the Euro sign on the left, the amount on the right with dot as thousands separator, comma as the decimal separator and two decimals.

如果我在 Excel 中执行此操作并应用此自定义格式规则，它会起作用:"_ [$€-nl-BE] * #.##0,00_ ;_ [$€-nl-BE] *-#.##0,00_ ;_ [$€-nl-BE] * \"-\"??_ ;_ @_ "

If I do that in Excel and apply this custom formatting rule it works: "_ [$€-nl-BE] * #.##0,00_ ;_ [$€-nl-BE] * -#.##0,00_ ;_ [$€-nl-BE] * \"-\"??_ ;_ @_ "

所以我只是复制了该规则并通过 Apache POI 应用它:

So I just copied that rule and apply it through Apache POI:

XSSFDataFormat dataFormat = (XSSFDataFormat) workbook.createDataFormat();
cellStyle.setDataFormat(dataFormat.getFormat("_ [$€-nl-BE] * #.##0,00_ ;_ [$€-nl-BE] * -#.##0,00_ ;_ [$€-nl-BE] * \"-\"??_ ;_ @_ "));

然而，结果略有不同:€1.000,4300.你可以看到它运行良好，除了小数.有 4 个而不是 2 个...当我在 Excel 中查看自定义格式时，它也略有不同(注意 4 个零): _ [$€-nl-BE] * #.##0,00000_ ;_ [$€-nl-BE] * -#.##0,00000_ ;_ [$€-nl-BE] * "-"?????_ ;_ @_

However that results in something slightly different: € 1.000,4300. You can see that it goes well except for the decimals. There are 4 instead of 2... When I have a look in Excel at the custom formatting it is also slightly different (notice the 4 zeroes): _ [$€-nl-BE] * #.##0,00000_ ;_ [$€-nl-BE] * -#.##0,00000_ ;_ [$€-nl-BE] * "-"?????_ ;_ @_

所以我想知道某人(Apache POI 或 Excel)确实将 4 位小数而不是我定义的 2 位小数点放在了什么地方.请注意，如果我在 Excel 中将它改回 2 位小数，它会再次显示正确.即使在保存并关闭并重新打开文档之后.

So I wonder what could be wrong that somewhere someone (either Apache POI or Excel) do put 4 decimals instead of the 2 that I defined. Notice that if I change it in Excel back to 2 decimals that it showing correct again. Even after saven and closing and reopening the document.

推荐答案

Apache poi 创建 *.xlsx 文件作为 Excel 将存储它.并且在存储中，数字格式从不本地化，而是始终采用 en_US 格式.只有 Excel GUI 然后本地化数字格式.

Apache poi creates the *.xlsx file as Excel would store it. And in the storage the number formats never are localized but always are in en_US format. Only the Excel GUI then localizes the number format.

因此对于存储，您的数字格式必须是

So for the storage your number format would must be

"_ [$€-nl-BE] * #,##0.00_ ;_ [$€-nl-BE] * -#,##0.00_ ;_ [$€-nl-BE] * \"-\"??_ ;_ @_ "

注意十进制分隔符是 .，千位分隔符是 , 因为 en_US 语言环境.

Note decimal separator is . and thousands separator is , because of en_US locale.

如果在本地化的 Excel GUI 中打开，则会更改为区域设置特定的十进制分隔符和千位分隔符.

If opened in localized Excel GUI this changes then to the locale specific decimal separator and thousands separator.

但 Excel 本身也不以数字格式存储像 nl-BE 这样的语言标签.相反，它使用 Windows 语言代码标识符 (LCID).这是 nl-BE 的 813.所以使用与[$€-nl-BE]相同的[$€-813]会更好.

But Excel itself also is not storing language tags like nl-BE in number formats. Instead it uses the Windows Language Code Identifier (LCID). This is 813 for nl-BE. So using [$€-813], which is the same as [$€-nl-BE], will be better.

以下也适用于我的德语 Excel:

Following works for me also in my German Excel:

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

import java.io.FileOutputStream;

class CreateExcelCustomNumberFormat {

 public static void main(String[] args) throws Exception {

  Workbook workbook = new XSSFWorkbook();
  DataFormat dataFormat = workbook.createDataFormat();
  CellStyle cellStyle = workbook.createCellStyle();
  cellStyle.setDataFormat(dataFormat.getFormat("_ [$€-813] * #,##0.00_ ;_ [$€-813] * -#,##0.00_ ;_ [$€-813] * \"-\"??_ ;_ @_ "));

  Cell cell = workbook.createSheet().createRow(0).createCell(0);
  cell.setCellValue(1000.43);
  cell.setCellStyle(cellStyle);

  FileOutputStream out = new FileOutputStream("Excel.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

此代码使用当前的 apache poi 4.1.2 和 Excel 2016 进行测试.

This code is tested using current apache poi 4.1.2 and Excel 2016.

这篇关于Apache POI 自定义数据格式修改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！