最近项目中,用到导入excel数据,但里面存在公式,导入取到的值为0,这篇就解决excel导入后,获取公式后的值
public class ExcelFormulaTest {
public static void main(String[] args) throws IOException {
FileInputStream inputStream = new FileInputStream("C:\\Users\\xiaobear\\Desktop\\工作簿1.xls");
//创建Workbook对象
Workbook workbook = new HSSFWorkbook(inputStream);
//获取公式执行工具
FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
//加载Excel文档
//获取第一个工作表
Sheet sheet = workbook.getSheetAt(0);
int rowNum = sheet.getLastRowNum();
int cells = sheet.getRow(0).getPhysicalNumberOfCells();
for (int i = 0; i <= rowNum; i++) {
for (int j = 0; j < cells; j++) {
Cell cell = sheet.getRow(i).getCell(j);
//判断是否为公式
if (cell.getCellType().equals(CellType.FORMULA)) {
//执行公式
CellValue evaluate = formulaEvaluator.evaluate(cell);
//这里需要根据evaluate 的类型设置值,demo直接返回字符串了
System.out.println(evaluate.toString());
}
}
}
}
}