How to set formula in Excel with POI

0 votes

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:

error

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

input field

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.

Jan 15 in Others by Kithuzzz
• 28,700 points
65 views

1 answer to this question.

0 votes

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.
answered Jan 15 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 53,160 points
148 views
0 votes
1 answer

How to merge two cells in excel with same field name

Insert 2 new columns, G & H. Enter ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 53,160 points
168 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 53,160 points
125 views
0 votes
1 answer
0 votes
1 answer

Can anyone show how to modify an existing excel sheet using Apache POI?

Hello Prakash, to modify an existing excel ...READ MORE

answered Jul 17, 2019 in Selenium by Anvi
• 14,170 points
2,889 views
0 votes
1 answer

How can I read numeric data from an Excel sheet using Selenium Webdriver?

Hey Jignesh, for reading numeric data from ...READ MORE

answered Jul 17, 2019 in Selenium by Abha
• 28,120 points
6,796 views
0 votes
1 answer

How to create page borders using Apache POI in excel files with Java?

Microsoft Excel cannot do this. Libreoffice Calc ...READ MORE

answered Dec 13, 2022 in Others by narikkadan
• 53,160 points
90 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 53,160 points
234 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP