//  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();        
    }
}