Java 如何使用 apache poi 3.1 获取公式单元格值（数据）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22992758/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the formula cell value(data) using apache poi 3.1
提问by user2097135
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.
我的单元格有公式（sheet2！C10），并且该单元格内的数据是字符串类型（例如 $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!!!
evaluator.evaluateInCell(cell).toString() 抛出空指针异常。请帮忙！！！
采纳答案by Gagravarr
OK, so first up, Apache POI 3.1 is rather old. The clue is in the jar name - poi-3.1-FINAL-20080629dates 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
至于你的问题，我很确定你不想打电话给evaluator.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.getCellFormulaand 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 objectand call evaluate:
最好的办法是获取一个FormulaEvaluator 对象并调用评估：
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 pagehas more on all of these
在Apache的POI公式评估页有更多关于所有这些
