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屋！