Why doesn't Apache POI correctly evaluate Boolean functions?

user_10555797 0
Viewed 56

I have Java code that uses Apache POI 5.2.2. The program works fine, except that Apache POI does not evaluate Excel Boolean functions correctly. For example, I have an Excel file called “eje.xlsm” that looks like this:

code image

It only has the formula AND(A1="a";B1=0) in cell C1 and the values "a" in A1 and 1 in B1. The main function of my Java program reads this information and changes the value in B1 to 0, so that C1 should change to TRUE:

public static void main(String[] args){
    try{
        FileInputStream file = new FileInputStream(new File("./eje.xlsm"));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        System.out.println(sheet.getRow(0).getCell(2).getBooleanCellValue());
        sheet.getRow(0).getCell(1).setCellValue(0);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateFormulaCell(sheet.getRow(0).getCell(2));
        System.out.println(sheet.getRow(0).getCell(2).getBooleanCellValue());
        FileOutputStream  out =  new FileOutputStream("./eje.xlsm");
        workbook.write(out);
        out.close();
        workbook.close();
        file.close();
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}

I was expecting this output on the console:

false
true

But, instead, I get:

false
false

I do not know why. I have tried to reconfigure Apache POI in Eclipse, which I use for Java coding, but the problem persists. It only happens with Boolean functions, so with other functions it works fine.

apache-poi boolean excel java
1 Answers

As shown in your screenshot, the formula in your Excel sheet is =+AND(A1="a";B1=0). The + before the AND is superfluous.

The + in that formula does the following: Evaluation of AND(A1="a";B1=0) results in either boolean TRUE or FALSE. But +TRUE and/or +FALSE converts the boolean to numeric. +TRUE = 1 and +FALSE = 0.

You will see this if you do the following in your code:

...
sheet.getRow(0).getCell(1).setCellValue(0);
CellType cellType = evaluator.evaluateFormulaCell(sheet.getRow(0).getCell(2));
System.out.println(cellType); //NUMERIC
...

Excel itself ignores the superfluous + while evaluating the formula and remains the boolean type. But Apache POI does not. One could call this a bug, as Apache POI tries to act exactly like Excel. But the simplest solution is not to use that superfluous + in Excel formulas.

user_39154310
Related