Bug 45418

Created attachment 22270 [details]
Test program showing problem

Attached is a simple test case that generates 4 cells

Cell B1 contains 2
Cell C1 contains 10
Cell D1 contains formula IF(B1=0,0,C1/B1)
Cell E1 contains forumula C1/B1

Ran this program with POI 3.0.2 and 3.1.

When opened, cell D1 shows 5 when using POI 3.0.2, with 3.1 it shows #VALUE!.

I have also tested with 2.5 and it seems to work.

---
FormulaTest.java
---

package test;

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * A Writer which writes to XLS file wit the #VALUE! problem
 * 
 */
public class FormulaTest {
    
    /**
     * Creates a new demo.
     */
    public FormulaTest() {
    }

    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, row2;
        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) 1);
        cell.setCellValue(2);

        cell = row.createCell((short) 2);
        cell.setCellValue(10);
        

        cell = row.createCell((short) 3);

        cell.setCellFormula("IF(B1=0,0,C1/B1)");
        
        cell = row.createCell((short)4);

        cell.setCellFormula("C1 / B1");


        return wb;
    }

    public static void main(String[] args) throws Exception {
        FormulaTest w = new FormulaTest();
        w.write();
    }
}