I did a little more looking into this behavior you observed. In LibreOffice, "A1=1" evaluates to false when A1 is the text "1". You need to use "A1=\"1\"" if A1 is text. You can implicitly coerce A1 to a number when evaluating the formula by doing "A1+0=1" or "A1*1=0".
See bug 58591 [1] for example code and more information. [1] https://bz.apache.org/bugzilla/show_bug.cgi?id=58591 On Thu, Nov 5, 2015 at 8:20 PM, Javen O'Neal <javenon...@gmail.com> wrote: > (Using POI 3.13 final) > > Here's your problem: > > XSSFCell cell1 = row.createCell(0); //A1 > XSSFCell cell2 = row.createCell(1); //B1 > XSSFCell cell3 = row.createCell(2); //C1 > XSSFCell cell4 = row.createCell(3); //D1 > > cell4.setCellFormula("IF(A1=1, A2, A3"); > > A2 and A3 are 0! > > Also, just a note, you don't need to set the cell type before setting the > value. The cell type gets updated when you set the cell value. > > Changing your formula to "IF(A1=1, B1, C1)", I get 3.0--which is better, > but still not correct. I still get 3.0 even if I change cell1 to 1.0 > (numeric) and/or change the formula to IF(A1=1.0, B1, C1) > > Here's my code (Jython) > from org.apache.poi.xssf.usermodel import XSSFWorkbook > wb = XSSFWorkbook() > row = wb.createSheet().createRow(0) > A1, B1, C1, D1 = [row.createCell(c) for c in range(4)] > A1.setCellValue("1") > B1.setCellValue(2.0) > C1.setCellValue(3.0) > D1.setCellFormula("IF(A1=1,B1,C1)") > evaluator = wb.getCreationHelper().createFormulaEvaluator() > evaluator.evaluateFormulaCell(D1); D1.getNumericCellValue() # returns 3.0 > evaluator.evaluate(D1) returns org.apache.poi.ss.usermodel.CellValue [3.0] > e.evaluateAllFormulaCells(wb); D1.getNumericCellValue() # returns 2.0 > > I haven't used formula evaluation before, so I don't know if this is my > inexperience or a bug in POI. > >
