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)

推荐答案

对于公式单元格， excel存储两件事。一个是公式本身，另一个是缓存值（论坛被评估为的最后一个值）

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屋！