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