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: [EMAIL PROTECTED] ReportedBy: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
