java - Java 逻辑无法在 Excel 中读取基于公式的单元格
问题描述
这是读取excel的方法:
@SuppressWarnings("deprecation")
public static void readFromExcel(String file) throws IOException {
workbook = new XSSFWorkbook(file);
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateAll();
XSSFSheet myExcelSheet = workbook.getSheetAt(2);
Iterator<Row> itr = myExcelSheet.iterator();
while (itr.hasNext()) {
Row row = itr.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
System.out.print("formula:" + cell.getCellFormula() + "\t\t\t");
String value = evaluator.evaluate(cell).getStringValue();
System.out.print(value + "\t\t\t");
break;
case HSSFCell.CELL_TYPE_NUMERIC: // field that represents
// numeric cell type
// getting the value of the cell as a number
System.out.print(cell.getDateCellValue() + "\t\t\t");
break;
case HSSFCell.CELL_TYPE_STRING: // field that represents string
// cell type
// getting the value of the cell as a string
System.out.print(cell.getStringCellValue() + "\t\t\t");
break;
}
}
System.out.println();
}
}
我得到以下输出:
日期 ME 余额
公式:'Tab1'!C1 null 公式:EOMONTH(A2,0) null 公式:'Tab1'!$D$3*1000 null
它正在读取单元格的公式,但不是值。我已经尝试了 FormulaEvaluator 的所有方法。有人可以帮忙吗?我附上了样品表图像。SampleScenario.xlsx Tab1.xlsx Tab2.xlsx