DO NOT REPLY [Bug 24925] New: - Nested IF Formula results in "#VALUE!" in Excel

[bu...@apache.org]

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG 
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://nagoya.apache.org/bugzilla/show_bug.cgi?id=24925>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND 
INSERTED IN THE BUG DATABASE.

http://nagoya.apache.org/bugzilla/show_bug.cgi?id=24925

Nested IF Formula results in "#VALUE!" in Excel 

           Summary: Nested IF Formula results in "#VALUE!" in Excel
           Product: POI
           Version: 2.0-pre3
          Platform: PC
        OS/Version: Windows 9x
            Status: NEW
          Severity: Normal
          Priority: Other
         Component: HSSF
        AssignedTo: poi-dev@jakarta.apache.org
        ReportedBy: avisar@hotmail.com


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.

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org