如何使用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相当老.线索在罐子名称中-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 ，然后打开它，并使用普通的吸气剂读取值，例如

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公式评估页面包含所有这些内容

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