如何使用apache poi 3.1获取公式单元格值（数据）

I am using Apache poi-3.1-FINAL-20080629 in my application. here, I have one problem using formula...

我在我的应用程序中使用Apache poi-3.1-FINAL-20080629。在这里，我有一个使用公式的问题......

My Cell has formula(sheet2!C10) and the data inside this cell is String type (e.g $3,456)...How to access that cell also want to display the formula.

我的Cell有公式（sheet2！C10），这个单元格内的数据是String类型（例如$ 3,456）...如何访问该单元格也想显示公式。

My code Looks like:

我的代码看起来像：

HSSFWorkbook wb = new HSSFWorkbook(file);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);

Iterator rows = sheet.rowIterator();
    while (rows.hasNext()) {
        HSSFRow row = (HSSFRow) rows.next();
        System.out.println("\n");
        Iterator cells = row.cellIterator();
        while (cells.hasNext()) {

            HSSFCell cell = (HSSFCell) cells.next();

            int cellType = cell.getCellType();

            if (HSSFCell.CELL_TYPE_NUMERIC == cellType)
                System.out.print(cell.getNumericCellValue() + "     ");
            else if (HSSFCell.CELL_TYPE_STRING == cellType)
                System.out.print(cell.getStringCellValue() + "     ");
            else if (HSSFCell.CELL_TYPE_BOOLEAN == cellType)
                System.out.print(cell.getBooleanCellValue() + "     ");
            else if (HSSFCell.CELL_TYPE_BLANK == cellType)
                System.out.print("BLANK     ");
            else if (HSSFCell.CELL_TYPE_FORMULA == cellType) {
                System.out.print(evaluator.evaluateInCell(cell).toString()  );              }   else 
                System.out.print("Unknown cell type " + cellType);

        }

    }

evaluator.evaluateInCell(cell).toString() is throwing null pointer exception. Please Help!!!

evaluateator.evaluateInCell（cell）.toString（）抛出空指针异常。请帮忙！！！

2 个解决方案

#1

OK, so first up, Apache POI 3.1 is rather old. The clue is in the jar name - poi-3.1-FINAL-20080629 dates from 2008, so 6 years ago! It's well worth upgrading, the number of bug fixes since then is pretty vast....

好的，首先，Apache POI 3.1相当陈旧。线索是在罐子名称 - poi-3.1-FINAL-20080629的日期，从2008年开始，所以6年前！这是非常值得升级的，从那以后错误修复的数量非常多......

As for your problem, I'm fairly sure that you don't want to be calling evaluator.evaluateInCell. That's almost never the right method to call

至于你的问题，我很确定你不想调用evaluateator.evaluateInCell。这几乎从来都不是正确的调用方法

You have three options available to you, depending on what you want to do with your formula cell:

您有三种选择，具体取决于您对公式单元格的处理方式：

I want the formula string

我想要公式字符串

Call Cell.getCellFormula and you'll get back the formula string

调用Cell.getCellFormula，你将得到公式字符串

I want the last formula value Excel calculated

我想要计算最后一个公式值Excel

When Excel writes out a file, it normally stores the last evaluated value for the formula, in a cache, to make opening nice and quick. You can read this, if present, from Apache POI. It's only a cache, so it might not always be correct, but it normally is.

当Excel写出文件时，它通常将公式的最后一个评估值存储在缓存中，以使开放变得美观和快速。您可以从Apache POI中读取此信息（如果存在）。它只是一个缓存，所以它可能并不总是正确的，但它通常是。

You need to call Cell.getCachedFormulaResultType, then switch on that, and read the values out using the normal getters, eg

你需要调用Cell.getCachedFormulaResultType，然后打开它，并使用普通的getter读取值，例如

int cellType = cell.getCellType();
handleCell(cell, cellType);

private void handleCell(Cell cell, int cellType) {
        if (HSSFCell.CELL_TYPE_NUMERIC == cellType)
            System.out.print(cell.getNumericCellValue() + "     ");
        else if (HSSFCell.CELL_TYPE_STRING == cellType)
            System.out.print(cell.getStringCellValue() + "     ");
        else if (HSSFCell.CELL_TYPE_BOOLEAN == cellType)
            System.out.print(cell.getBooleanCellValue() + "     ");
        else if (HSSFCell.CELL_TYPE_BLANK == cellType)
            System.out.print("BLANK     ");
        else if (HSSFCell.CELL_TYPE_FORMULA == cellType)
            handleCell(cell, cell.getCachedFormulaResultType());
        else 
            System.out.print("Unknown cell type " + cellType);
   }

I want Apache POI to calculate the formula result for me

我希望Apache POI为我计算公式结果

Your best bet here is to get a FormulaEvaluator object and call evaluate:

这里最好的选择是获取一个FormulaEvaluator对象并调用evaluate：

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

// suppose your formula is in B3
CellReference cellReference = new CellReference("B3"); 
Row row = sheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol()); 

CellValue cellValue = evaluator.evaluate(cell);

switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
    System.out.println(cellValue.getBooleanValue());
    break;
case Cell.CELL_TYPE_NUMERIC:
    System.out.println(cellValue.getNumberValue());
    break;
case Cell.CELL_TYPE_STRING:
    System.out.println(cellValue.getStringValue());
    break;
case Cell.CELL_TYPE_BLANK:
    break;
case Cell.CELL_TYPE_ERROR:
    break;

// CELL_TYPE_FORMULA will never happen
case Cell.CELL_TYPE_FORMULA: 
    break;
}

The Apache POI Formula Evaluation page has more on all of these

Apache POI公式评估页面包含所有这些内容

#2

Its better to do like this.

这样做更好。

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;
        }
     }
 }

Previously i used the solution with FormulaEvaluator and in that case i'm struck with some exceptions like given below.

以前我使用了FormulaEvaluator的解决方案，在这种情况下，我遇到了一些例外，如下所示。

org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Sheet1!BL190
    at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:332)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:273)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:205)
    at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:268)
    at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluate(XSSFFormulaEvaluator.java:121)

#1