无法使用JExcelAPI检索计算单元格的值

Question

这是我的第一篇文章，请多多包涵。我正在尝试做一些简单的事情:查看包含公式的电子表格单元格的值。似乎什么都不起作用，我得到的不是正确答案(579)，而是"4“。(输出如下)。这个程序创建一个简单的电子表格，用A1和A2表示数字，用A3表示总和。

   import java.io.*;
   import jxl.*;
   import jxl.Workbook;
   import jxl.biff.FormulaData;
   import jxl.biff.formula.FormulaException;
   import jxl.write.Number;
   import jxl.write.*;
   import jxl.write.Formula;
   import jxl.read.biff.CellValue;

   public class ReadCell {

     public static void main(String[] args) throws IOException,
                                     jxl.read.biff.BiffException,
                                     WriteException, FormulaException
     {
         String filename = "readcell.xls";
         WritableWorkbook workbook = Workbook.createWorkbook(new File(filename));
         WritableSheet ws = workbook.createSheet("Sheet1", 0);

         Number n = new Number(0,0,123);   // 123 in A1
         ws.addCell(n);
         n = new Number(0,1,456);          // 456 in A2
         ws.addCell(n);
         Formula f = new Formula(0,2, "A1+A2");  // formula in A3, yields 579
         ws.addCell(f);

         workbook.write();
         workbook.close();   

  //now try to retrieve the sum of A1 + A2, which is in A3 (value should be 579)
  Workbook wb = Workbook.getWorkbook( new File (filename));
  Sheet sheet = wb.getSheet(0);
  Cell cell = sheet.getCell("A3");

  System.out.println("\n\nCell A3 contents = " + cell.getContents());

  //that didn't work, maybe try it by casting to a NumberCell?
  NumberCell numbercell = (NumberCell)cell;
  System.out.println("NumberCell value = " + numbercell.getValue());
  System.out.println("NumberCell contents = " + numbercell.getContents());

  //that didn't work, maybe try it by casting to a FormulaCell?
  FormulaCell fc = (FormulaCell)cell;
  System.out.println("FormulaCell contents = " + fc.getContents());
  System.out.println("FormulaCell formula = " + fc.getFormula());

  //that didn't work, maybe try to get contents by casting to FormulaData?
  FormulaData fd = (FormulaData)cell;
  System.out.println("FormulaData contents = " + fd.getContents());
  System.out.println("FormulaData type = " + fd.getType());

  //that didn't work, maybe try to get contents by casting to NumberFormulaCell?
  NumberFormulaCell nfc = (NumberFormulaCell)cell;
  System.out.println("NumberFormulaCell value = " + nfc.getValue());
  System.out.println("NumberFormulaCell contents = " + nfc.getContents());
  System.out.println("NumberFormulaCell formula = " + nfc.getFormula());

  //that didn't work, try to get contents by casting to CellValue?
  CellValue cv = (CellValue)cell;
  System.out.println("cellValue contents = " + cv.getContents());

  //Nothing works and these casts won't compile:
  //Number nnn = (Number)cell;
  //NumberFormulaRecord nfr = (NumberFormulaRecord)cell; 

  wb.close();
  }
}

这是输出，“4”应该是“579”

单元格A3内容=4

NumberCell值= 4.0

NumberCell内容=4

FormulaCell内容=4

FormulaCell公式= A1+A2

FormulaData内容=4

FormulaData类型=数值公式

NumberFormulaCell值= 4.0

NumberFormulaCell内容=4

NumberFormulaCell公式= A1+A2

cellValue内容=4

感谢您能提供的任何帮助！

Answer 1

我运行了编写工作簿的代码。然后我在Excel中打开了工作簿。当我告诉它要关闭时，系统会提示我一条消息：“Microsoft Excel在打开上次由早期版本的Excel保存的文件时重新计算公式”。在选择保存之后，然后运行读取的代码(而不是编写工作簿的代码)，我得到了正确的单元格值579。当我删除文件并运行你发布的所有代码时，我得到了4。看起来公式需要先计算后才能读取。这篇文章似乎说没有办法修复它，并对原因做出了假设：Updated excel file still returns old values