Java的POI：如何读取Excel单元格的值，而不是公式计算呢？ [英] Java POI : How to read Excel cell value and not the formula computing it ?

问题描述

我使用Apache POI API从一个Excel文件中获取价值。
除了包含公式的单元格一切伟大的工作。事实上，cell.getStringCellValue（）被返回的电池中使用的公式，而不是细胞的值。

I am using Apache POI Api to getting values from an Excel file. Everything is working great except with cells containing formulas. In fact, the cell.getStringCellValue() is returning the formula used in the cell and not the value of the cell.

我试图用evaluateFormulaCell（）方法，但它不工作，因为我使用GETPIVOTDATA Excel公式和这个公式是不是在API中实现的：

I tried to use evaluateFormulaCell() method but it's not working because I am using GETPIVOTDATA Excel formula and this formula is not implemented in the API:

Exception in thread "main" org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Landscape!K11
    at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:213)
    at fromExcelToJava.ExcelSheetReader.unAutreTest(ExcelSheetReader.java:193)
    at fromExcelToJava.ExcelSheetReader.main(ExcelSheetReader.java:224)
Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: GETPIVOTDATA
    at org.apache.poi.hssf.record.formula.functions.NotImplementedFunction.evaluate(NotImplementedFunction.java:42)

我希望你能帮助我在此。

I hope you can help me on this.

感谢你们。

推荐答案

有关公式的单元格，Excel将两件事情。之一是式本身，另一种是缓存的值（最后的值，该forumla评价为）

For formula cells, excel stores two things. One is the Formula itself, the other is the "cached" value (the last value that the forumla was evaluated as)

如果你想获得最后的缓存值（可能不再是正确的，但只要Excel中保存的文件，你没有改变它，它应该是），你会想是这样的：

If you want to get the last cached value (which may no longer be correct, but as long as Excel saved the file and you haven't changed it it should be), you'll want something like:

 for(Cell cell : row) {
     if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        System.out.println("Formula is " + cell.getCellFormula());
        switch(cell.getCachedFormulaResultType()) {
            case Cell.CELL_TYPE_NUMERIC:
                System.out.println("Last evaluated as: " + cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
                break;
        }
     }
 }

这篇关于Java的POI：如何读取Excel单元格的值，而不是公式计算呢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！