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