java - 无法使用 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

单元格编号内容 = 4

公式单元格内容 = 4

FormulaCell公式=A1+A2

公式数据内容 = 4

FormulaData 类型 = 数值公式

NumberFormulaCell 值 = 4.0

NumberFormulaCell 内容 = 4

NumberFormulaCell 公式 = A1+A2

单元格值内容 = 4

感谢您提供的任何帮助!

Answer 1

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