POI Formula Evaluation Failed

Question

I'am using POI in order to read excels files from my users. In one of the cells, i've got this formula :

IF(H24>0,((D24*(Q24))/((3600*H24)/I24))+IF(AND(N24>0,L24>0),(D24*L24*(Q24)/N24),0),0)

The problem arrive when i've got a 0 value for N24. In this case, POI returns me an DIV/0 error, but i don't understand why .. because of the IF (N24>0) test.

Anyway, I tried changing the formula and keep only the latest part like this : IF (N24>0,D24*L24*(Q24)/N24),0) => still doesn't work.

To Evaluate the formula I use : FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); switch (evaluator.evaluateFormulaCell(cell))

.....

I am using POI 3.10, same pb with 3.11

Any idea ?

Answer 1

We found a Bypass. When using the method evaluateFormulaCell, it was impossible to get the catchedValue.

By using the valuator.evaluate(cell), we still have the error but we can get the catchedValue.

Answer 2

Found the prog error; In order to get an long, the developer coded this :

Thanks for your response and sorry ...

I found an error of programation.

To get a long, the developer used this method :

public static Object getCellValue(Row row, int index) {
    if (row == null || row.getCell(index) == null) {
        return null;
    }
    Object retour = null;
    Cell cell = row.getCell(index);
    cell.setCellType(Cell.CELL_TYPE_STRING);
    String val = cell.getStringCellValue().trim();
    if (val != null && !"".equals(val)) {
        retour = cell.getStringCellValue();
    }

    return retour;
}

The gulty is : cell.setCellType(Cell.CELL_TYPE_STRING); ....