Hi, In Excel, I set A1 to be a text cell. I then set the value to 1. If I try IF(A1="1", B1, C1) in Excel, it always gives C1, even if A1 is the value 1. IF(A1=1, B1, C1) does the right thing. I was hoping that I could always copy the formula from Excel and use it as is as the input for the POI formula. But If I use the one that works in Excel, it does not do the right thing in POI. Jason.
> From: javenon...@gmail.com > Date: Thu, 5 Nov 2015 23:44:48 -0800 > Subject: Re: Formula not working as expected > To: user@poi.apache.org > > 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. > > > >
