How to set formula in Excel with POI?

Question

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.

Axel Richter · Accepted Answer · 2023-01-15T06:02:06.787

Microsoft Excel never stores localized formula syntax but always en_US formula syntax. The localization is done by GUI after read the file. And 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
...

If a German Excel GUI finds ZÄHLENWENN(A1:A2, \"X\") in the file, it expects a en_US function name. But ZÄHLENWENN is not a such. So localizing (translation) of the function name fails. It localizes the parameter delimiter and would localize the decimal delimiter too but the formula localization as whole fails because of an unknown (unexpected) function name. That's why the #NAME? error.

How to set formula in Excel with POI?

1 Answers1