Bug 45418 - Regression from POI 3.0.2 to 3.1 - IF forumula
Summary: Regression from POI 3.0.2 to 3.1 - IF forumula
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-07-17 05:40 UTC by john mcteague
Modified: 2008-07-17 12:05 UTC (History)
0 users



Attachments
Test program showing problem (1.51 KB, application/octet-stream)
2008-07-17 05:40 UTC, john mcteague
Details

Note You need to log in before you can comment on or make changes to this bug.
Description john mcteague 2008-07-17 05:40:40 UTC
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();
    }
}
Comment 1 Nick Burch 2008-07-17 05:45:02 UTC
Can you re-test with a recent svn checkout / nightly build? I think this might've been fixed already
Comment 2 john mcteague 2008-07-17 06:27:27 UTC
poi-3.2-alpha1-20080717.jar resolves the issue. Thanks. For reference, do you know the bug number this was fixed under?(In reply to comment #1)

> Can you re-test with a recent svn checkout / nightly build? I think this
> might've been fixed already
> 

Comment 3 Nick Burch 2008-07-17 06:32:58 UTC
I think it might've been bug #45348, but I'll let Josh confirm as he did the fix
Comment 4 Josh Micich 2008-07-17 12:05:45 UTC
(In reply to comment #2)
> poi-3.2-alpha1-20080717.jar resolves the issue. Thanks. For reference, do you
> know the bug number this was fixed under?(In reply to comment #1)

This bug did not exist in 3.1-beta2. It was introduced in 3.1-final probably due to the fix for bug 45060.  As Nick indicated, this problem was probably fixed due to bug 45348.  I think that was the only RVA work done between beta2 and final.