Apache POI - CellDataFormat Exception

Question

I'm trying to create an excel file(.xlsx) and fill it with data using Apache POI, and formatting some cells to an Accounting format, using:

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

It keeps throwing IllegalArgumentExceptions, stating that the format is invalid:

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)

However, my data is exporting fine to excell, everything is working as intended. The format is working perfectly (in Excel, in a file created and modified through Java, using this format).

Interestingly, the point at which the exception is thrown, MyClass line 325 is simply:

            spreadsheet.autoSizeColumn(1);

So maybe the error has nothing to do with writing the data, but it's just the resizing that isn't going over smoothly? I'm not well versed in excel, so I don't know how this data formatting works in excel on a programming level.

Could anyone help me resolve this, if there is a need for that at all? Maybe POI just isn't able to recognize such a (foreign language) format?

Cheers,

Answer 1

Excel itself is not using language tags like nl-NL in number formats. Instead it uses the Windows Language Code Identifier (LCID). This is 413 for nl-NL.

The Sheet.autoSizeColumn needs to know the number format to calculate the needed column widths. So it tries to parse your number format having [$€-nl-NL] in it and fails. Using [$€-413], which is the same, it will be successful.

The following works for me:

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);
  }

 }
}