How to set formula in Excel with POI?-CodePudding

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:

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

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.

CodePudding user response：

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
...