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

[bugzilla]

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=8757>.
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=8757

Formula results in "#VALUE!" in Excel

           Summary: Formula results in "#VALUE!" in Excel
           Product: POI
           Version: unspecified
          Platform: PC
        OS/Version: Windows NT/2K
            Status: NEW
          Severity: Normal
          Priority: Other
         Component: HSSF
        AssignedTo: [EMAIL PROTECTED]
        ReportedBy: [EMAIL PROTECTED]


//  Create a worksheet.xls with a formula C1 == (A1+B1).
//  Copy worksheet.xls to worksheet2.xls.

//  Open worksheet2.xls in Excel.
//  Notice, "#VALUE!" is in C1.
//  Click C1.
//  Click Formula Editor (text input next to equal sign).
//  Click Spreadsheet.
//  C1 changes to 5.
//  Save.

//  Open worksheet.xls in Excel.
//  Save.
//  (I did this because the worksheet.xls created/edited by POI
//   is around 5k and the one created by Excel is 13k.)

//  Performed diff of BiffViewer output on worksheet.xls and worksheet2.xls
//  and only difference appears to be the value of the formula (NaN vs. 5).

//  Did a pure hex compare (in emacs) against worksheet.xls and worksheet2.xls 
//  and saw other differences which are not reflected in BiffViewer output; 
//  those differences are not shown in the bfd mode in BiffViewer either.

import java.io.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.poifs.filesystem.*;


public class FormulaProblem implements Serializable
{
    public static void
    main(String args[])
        throws Exception
    {
        if (args[0].equals("create")) {
            create();
            return;
        }
        if (args[0].equals("edit")) {
            edit();
            return;
        }
        System.out.println("pass in either 'create' or 'edit'");
    }


    public static void
    create()
        throws Exception
    {
        System.out.println("create workbook.xls");

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = sheet.createRow((short) 0);
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue(2);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

        cell = row.createCell((short) 1);
        cell.setCellValue(3);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

        cell = row.createCell((short) 2);
        cell.setCellFormula("(A1+B1)");

        FileOutputStream out = new FileOutputStream("workbook.xls");
        workbook.write(out);
        out.close();
    }


    public static void
    edit()
        throws Exception
    {
        System.out.println("edit workbook.xls");

        POIFSFileSystem fileSystem =
            new POIFSFileSystem(new FileInputStream("workbook.xls"));

        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFRow row = sheet.getRow(0);
        HSSFCell cell = row.getCell((short) 2);
        cell.setCellFormula("(A1+B1)");

        FileOutputStream out = new FileOutputStream("workbook.xls");
        workbook.write(out);
        out.close();        
    }
}