java - Apache POI - CellDataFormat 异常

Question

我正在尝试创建一个 excel 文件(.xlsx)并使用 Apache POI 用数据填充它，并将一些单元格格式化为会计格式，使用:

    CellStyle CellAccounting = spreadsheet.getWorkbook().createCellStyle();
    CreationHelper ch = workbook.getCreationHelper();
    CellAccounting.setDataFormat(ch.createDataFormat().getFormat("_ [$€-nl-NL] * #,##0.00_ ;_ [$€-nl-NL] * -#,##0.00_ ;_ [$€-nl-NL] * \"-\"??_ ;_ @_ "));

它不断抛出 IllegalArgumentExceptions，说明格式无效:

Feb 18, 2020 1:40:10 AM org.apache.poi.ss.format.CellFormat <init>
WARNING: Invalid format: "_ [$€-nl-NL] * "-"??_ ;"
java.lang.IllegalArgumentException: Unsupported [] format block '[' in '_ [$€-nl-NL] * "-"??_' with c2: null
    at org.apache.poi.ss.format.CellFormatPart.formatType(CellFormatPart.java:373)
    at org.apache.poi.ss.format.CellFormatPart.getCellFormatType(CellFormatPart.java:287)
    at org.apache.poi.ss.format.CellFormatPart.<init>(CellFormatPart.java:191)
    at org.apache.poi.ss.format.CellFormat.<init>(CellFormat.java:189)
    at org.apache.poi.ss.format.CellFormat.getInstance(CellFormat.java:163)
    at org.apache.poi.ss.usermodel.DataFormatter.getFormat(DataFormatter.java:343)
    at org.apache.poi.ss.usermodel.DataFormatter.getFormat(DataFormatter.java:309)
    at org.apache.poi.ss.usermodel.DataFormatter.getFormattedNumberString(DataFormatter.java:868)
    at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:1021)
    at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:971)
    at org.apache.poi.ss.util.SheetUtil.getCellWidth(SheetUtil.java:170)
    at org.apache.poi.ss.util.SheetUtil.getColumnWidthForRow(SheetUtil.java:300)
    at org.apache.poi.ss.util.SheetUtil.getColumnWidth(SheetUtil.java:254)
    at org.apache.poi.ss.util.SheetUtil.getColumnWidth(SheetUtil.java:233)
    at org.apache.poi.xssf.usermodel.XSSFSheet.autoSizeColumn(XSSFSheet.java:555)
    at org.apache.poi.xssf.usermodel.XSSFSheet.autoSizeColumn(XSSFSheet.java:537)
    at MyClass.createExcel(MyClass.java:325)
    at MyClass.saveFile(MyClass.java:82)
    at ClickListener.actionPerformed(ClickListener.java:95)
    at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1967)
    at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2308)
    at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405)
    at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
    at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
    at java.desktop/java.awt.Component.processMouseEvent(Component.java:6636)
    at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3342)
    at java.desktop/java.awt.Component.processEvent(Component.java:6401)
    at java.desktop/java.awt.Container.processEvent(Container.java:2263)
    at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:5012)
    at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2321)
    at java.desktop/java.awt.Component.dispatchEvent(Component.java:4844)
    at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4918)
    at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4547)
    at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4488)
    at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2307)
    at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2762)
    at java.desktop/java.awt.Component.dispatchEvent(Component.java:4844)
    at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:772)
    at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:721)
    at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:715)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
    at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
    at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:95)
    at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:745)
    at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:743)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
    at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
    at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:742)
    at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
    at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
    at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
    at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
    at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)

但是，我的数据可以很好地导出到 Excel，一切都按预期工作。该格式运行良好(在 Excel 中，在通过 Java 创建和修改的文件中，使用此格式)。

有趣的是，抛出异常的点 MyClass 第 325 行很简单:

            spreadsheet.autoSizeColumn(1);

所以也许这个错误与写入数据无关，但只是调整大小并没有顺利完成？我不精通excel，所以我不知道这种数据格式在编程级别的excel中是如何工作的。

如果有需要的话，谁能帮我解决这个问题？也许 POI 只是无法识别这种(外语)格式？

干杯，

Answer 1

Excel 本身没有使用像 nl-NL 这样的数字格式的语言标签。相反，它使用 Windows Language Code Identifier (LCID) 。这是 413 的 nl-NL 。
Sheet.autoSizeColumn 需要知道数字格式来计算所需的列宽。因此，它尝试解析您的数字格式，其中包含 [$€-nl-NL] 并失败。使用 [$€-413] ，也是一样的，会成功的。

以下对我有用:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class CreateExcelCellAccountingStyle {

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

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   Object[][] data = new Object[][] {
    new Object[] {"Text", "Value"},
    new Object[] {"Value 1", 123.456789},
    new Object[] {"Value 2", 1234.56789}
   };

   DataFormat dataFormat = workbook.createDataFormat();
   CellStyle cellAccountingStyle = workbook.createCellStyle();
   //cellAccountingStyle.setDataFormat(dataFormat.getFormat("_ [$€-nl-NL] * #,##0.00_ ;_ [$€-nl-NL] * -#,##0.00_ ;_ [$€-nl-NL] * \"-\"??_ ;_ @_ "));
   cellAccountingStyle.setDataFormat(dataFormat.getFormat("_ [$€-413] * #,##0.00_ ;_ [$€-413] * -#,##0.00_ ;_ [$€-413] * \"-\"??_ ;_ @_ "));

   Sheet sheet = workbook.createSheet(); 

   for (int r = 0; r < data.length; r++) {
    Row row = sheet.createRow(r);
    for (int c = 0; c < data[0].length; c++) {
     Cell cell = row.createCell(c);
     if (data[r][c] instanceof String) {
      cell.setCellValue((String)data[r][c]);
     } else if (data[r][c] instanceof Double) {
      cell.setCellValue((Double)data[r][c]);
      cell.setCellStyle(cellAccountingStyle);
     }
    }
   }

   for (int c = 0; c < data[0].length; c++) {
    sheet.autoSizeColumn(c);
   }

   workbook.write(fileout);
  }

 }
}