How to set formula in Excel with POI?

I want to generate a Excel sheet with Apache POI. One cell should contain a formula. Unfortunately, it doesn’t work. I get an error in the Excel sheet.

Code

public static void main(String[] args) throws FileNotFoundException, IOException {

    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("Test");
    Row row = sheet.createRow(2);
    Cell cell = row.createCell(0);

    cell.setCellFormula("ZĂ„HLENWENN(A1:A2, \"X\")");

    workbook.write(new FileOutputStream(new File("d:\\tmp\\test.xlsx")));
}

Excel

The Excel sheet is created, but I see an error in the cell:

error

Although, I see the right formula in the input field (with converted separator):

input field

Environment

It is a legacy application, therefore I can’t update libraries’ major versions.

  • Java 8
  • Apache 3.17
  • Microsoft Excel 2016
  • German localization

Research

If I press enter in the input field, the error disappears and the value is calculated, but I don’t want to do that manually.

>Solution :

Microsoft Excel never stores localized formula syntax but always en_US formula syntax. The localization is done by GUI after read the file. An Apache POI creates the file. It is not a GUI. So the en_US formula syntax is needed when the formula gets set by Apache POI.

...
cell.setCellFormula("COUNTIF(A1:A2, \"X\")");
...

If the GUI of a German Excel reads the formula COUNTIF(A1:A2,"X") from the file, then it localizes it to German:

  • COUNTIF -> ZĂ„HLENWENN
  • Parameter delimiter comma -> semicolon
  • Decimal delimiter dot -> comma

Leave a Reply