How to set formula in Excel with POI

Question

Using Apache POI, I want to produce an Excel spreadsheet. A formula should appear in one cell. Sadly, it is ineffective. In the Excel sheet, I receive a mistake.

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.

Answer 1

Microsoft Excel only stores en US formula syntax; it never stores localized formula syntax. After reading the file, the GUI does the localization. The file is created by Apache POI. No, it is not a GUI. Therefore, when the formula is specified by Apache POI, the en US formula syntax is required.

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

If ZHLENWENN(A1:A2, "X") is found in the file, a German Excel GUI anticipates an en US function name. ZHLENWENN, however, is not one. The function name's localization (translation) so fails. The formula localization as a whole fails, though it localises the parameter delimiter and would also localise the decimal delimiter. This explains the #NAME? error.