Hi, I'm using POI HSSF version poi-bin-2.0-RC1-20031102, excel 97/XP I'm trying to use a nested if formula in excel which is used for writing a string based on a cell value. this cell value is also a formula of devision ("A2/A3"). When I'm using simple IF it works but when I use a nested IF then a #VALUE! comes up. When I enter the sheet using excel, go to the cell and just press enter on the formula line then I get the proper value. What am I doing wrong ? I have seen that sometimes it does not necessary connected to the fact that the devision cell is also a formula but to the fact that the cell is a float and not integer and sometimes when I use integer in the IF formula then it works. Moreover when using % in a formula parser fails. Source Code is: import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import java.io.FileOutputStream; /** * A Writer which writes to XLS file wit the #VALUE! problem * */ public class TestXLSWriter { public static final int COLUMN_A = 0; public static final int COLUMN_B = 1; public static final int COLUMN_C = 2; public static final int COLUMN_D = 3; /** * Creates a new demo. */ public TestXLSWriter() { } public void write() throws Exception { HSSFWorkbook wb = createTestWorkbook(); FileOutputStream out = new FileOutputStream("test.xls"); wb.write(out); out.close(); } private HSSFWorkbook createTestWorkbook() throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Test Sheet"); HSSFRow row; HSSFCell cell; // Create a row and put some cells in it. Rows are 0 based. row = sheet.createRow((short)0); // Create a cell cell = row.createCell((short)COLUMN_A); cell.setCellValue(50); cell = row.createCell((short)COLUMN_B); cell.setCellValue(100); cell = row.createCell((short)COLUMN_C); cell.setCellFormula("A1/B1"); // Although problem occurs with or without representing // fraction using precent style I use it in my // program and that's why I put it in the test. HSSFCellStyle style = wb.createCellStyle(); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0%")); cell.setCellStyle(style); cell = row.createCell((short)COLUMN_D); // Here is the problem : // basically I want 0 if C1 < 0.3, 2 if C1 > 0.8 and 1 if in between. // In real life I will turn 0,1,2 to "Failed", "OK", "GOOD" // why does this line produce #VALUE! ??? // However when I enter excel 97/XP and click inside this value // it works. // Moreover, I know that for sure the nested IF is the problem. cell.setCellFormula("IF(C1<0.3, 0, IF(C1>0.8, 2, 1))"); // Other setCellFormulas that work are : // without nested IF it works //cell.setCellFormula("IF(C1<0.3, 0, 1)"); // if I try with 30% the parser fails. // cell.setCellFormula("IF(C1<30%, 0, IF(C1>80%, 2, 1))"); return wb; } public static void main (String[] args) throws Exception { System.out.println("DEBUG: hello"); TestXLSWriter w = new TestXLSWriter(); w.write(); } } Thanks.
Created attachment 9248 [details] the test file in java
Created attachment 9249 [details] Resulted Excel File (Using Excel 97) the result xls with #VALUE!
Created attachment 10961 [details] Proposed fix for nested if statements #VALUE
Created attachment 10962 [details] The REAL proposed fix for nested if #VALUE problem
The problem is that cell references in the nested ifs were not getting their Token classes set properly because the recursion would bail out early when it reached nodes that were not AbstractFunctionPtgs. In fact, we want to recurse into the entire tree to push out this token munging stuff. NOTE: The second patch file i submitted is the one i am proposing. The other one is just cruft. Sorry.
hmm sounds and looks good enough :). gonna apply and check out unit tests.
Patch applied