用公式读取单元格会给出不同于excel文件的结果-JavaApache POI

Question

我正在写一个程序：

• 打开excel文件

根据下面的代码片段，我可以成功地写下公式，检查excel文件将得到正确的总和。

            Row row = sheet.getRow(2); //which is row 3 at excel file
            Cell cell = row.createCell(0); //so that's cell A3
            cell.setCellFormula("SUM(A1:A2)");

可悲的是，当我读到公式的值时，它应该是“101.0”，它给了我一个“0.0”。

            row = sheet.getRow(2);
            Cell currentCell = row.getCell(0); //getting value of formula which we wrote above
            if (currentCell != null) {
                if (currentCell.getCellTypeEnum() == CellType.FORMULA) {
                    if (currentCell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
                        System.out.println("VALUE FROM PROGRAM: "+currentCell.getNumericCellValue());
                    }
                }
            }

我尝试了通过微软电子表格应用程序编写一个公式来比较程序读取时返回的值。结果，对于我的程序编写的公式，它是同一个不正确的值，但是对于使用微软电子表格编写的公式，这个值被完美地读取了。

我错过了什么？为什么第一个公式的答案不能正确解读？希望有人能帮忙。提前谢谢。

以下是我文件中的内容：

        |    A      |     B     |
    1   |    1      |     2     |
    2   |   100     |    100    |
    3   |=SUM(A1:A2)|=SUM(B1:B2)| //where A3 was written by java and B3 was written using MS Excel

以下是main中的完整代码：

    String surveyDirectory = "DATABASE.xlsx";
    XSSFWorkbook workbookx;
    FileOutputStream outputStream;
    FileInputStream fis;

    File file = new File(surveyDirectory);

    if (file.exists()) {
        try {
            fis = new FileInputStream(surveyDirectory);
            workbookx = new XSSFWorkbook(fis);
            Sheet sheet = workbookx.getSheetAt(0);

            //writing a formula to file

            Row row = sheet.createRow(2); //which is row 3 at excel file
            Cell cell = row.createCell(0); //so that's A3 in excel
            cell.setCellFormula("SUM(A1:A2)");

            outputStream = new FileOutputStream(surveyDirectory); //write it down the file
            workbookx.write(outputStream);
            outputStream.close();

            System.out.println("DONE WRITING");

            //reading the fomula file
            //we are accessing same sheet
            row = sheet.getRow(2);
            Cell currentCell = row.getCell(0); //getting value of formula which we wrote above
            System.out.println("FORMULA at A3: " + currentCell);
            if (currentCell != null) {
                if (currentCell.getCellTypeEnum() == CellType.FORMULA) {
                    if (currentCell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
                        System.out.println("VALUE FROM PROGRAM: "+currentCell.getNumericCellValue());
                    }
                }
            }

            row = sheet.getRow(2); //formula is =SUM(B1:B2)
            currentCell = row.getCell(1); //getting value of formula which was written vie MS Excel
            System.out.println("FORMULA at B3: " + currentCell);
            if (currentCell != null) {
                if (currentCell.getCellTypeEnum() == CellType.FORMULA) {
                    if (currentCell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
                        System.out.println("VALUE FROM MS EXCEL: "+currentCell.getNumericCellValue());
                    }
                }
            }

            System.out.println("DONE READING");
            fis.close();
            workbookx.close();
        } catch (IOException ex) {
            Logger.getLogger(TextHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

以下是我得到的结果：

    DONE WRITING
    FORMULA at A3: SUM(A1:A2)
    VALUE FROM PROGRAM: 0.0
    FORMULA at B3: SUM(B1:B2)
    VALUE FROM MS EXCEL: 102.0
    DONE READING
    BUILD SUCCESSFUL (total time: 1 second)

编辑：我使用的是ApachePOI3.17

编辑#2：以下是工作片段。

    Row row = sheet.getRow(2); 
    Cell cell = row.createCell(0); 
    cell.setCellFormula("SUM(A1:A2)");
    FormulaEvaluator evaluator = workbookx.getCreationHelper().createFormulaEvaluator();
    evaluator.evaluateFormulaCell(cell);

Answer 1

设置公式不会更新单元格中的关联值。您还需要评估单元，如下所述。你可以使用FormulaEvaluator。evaluate（）查看结果，或FormulaEvaluator。evaluateFormulaCell（）将结果保存到单元格中，就像Excel那样。