如何使用 apache poi 3.1 获取公式单元格值(数据) [英] How to get the formula cell value(data) using apache poi 3.1

问题描述

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

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

我的单元格有公式(sheet2！C10)，这个单元格里面的数据是字符串类型(例如$3,456)...如何访问那个单元格也想显示公式.

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.

我的代码看起来像:

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() 抛出空指针异常.请帮忙！！！

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

推荐答案

好的，首先，Apache POI 3.1 相当老了.线索在 jar 名称中 - poi-3.1-FINAL-20080629 日期为 2008 年，所以是 6 年前！非常值得升级，从那时起修复的错误数量非常多......

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....

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

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

您可以使用三个选项，具体取决于您要对公式单元格执行的操作:

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

我想要公式字符串

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

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

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

当 Excel 写出文件时，它通常会将公式的最后评估值存储在缓存中，以便快速打开.您可以从 Apache POI 中阅读此内容(如果有).这只是一个缓存，所以它可能并不总是正确的，但通常是.

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.

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

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

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

我想让Apache POI帮我计算公式结果

这里最好的办法是获得一个 FormulaEvaluator 对象 并调用评估:

Your best bet here is to get a FormulaEvaluator object and call 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;
}   

Apache POI 公式评估页面有更多关于所有这些的内容

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

这篇关于如何使用 apache poi 3.1 获取公式单元格值(数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！