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.

### >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
- …