使用poi准确读取excel单元格数据的方法

    /**
     * 读取完整单元格数据
     */
    public static Object getCellValue(Cell cell, FormulaEvaluator evaluator){
        if (cell == null){ return null;}
        //函数格式处理
        if(Objects.equals(cell.getCellType(), CellType.FORMULA)){
            try{
                //这一行可能因为有跨文件引用或其他问题导致计算报错
                CellValue value = evaluator.evaluate(cell);
                if(value.getCellType() == CellType.NUMERIC){
                    return NumberToTextConverter.toText(value.getNumberValue());
                }else if(value.getCellType() == CellType.BOOLEAN){
                    return String.valueOf(value.getBooleanValue());
                }else{
                    return value.getStringValue();
                }
            }catch (Exception e){
                //使用缓存的单元格格式和数据读取
                CellType cacheType = cell.getCachedFormulaResultType();
                return getCellValue(cell,cacheType);
            }
        }else{
            return getCellValue(cell,cell.getCellType());
        }
    }

    private static Short[] EXCEL_DATE_FORMAT ={14,31,57,58};
 
    private static final List EXCEL_DATE_FORMAT_LIST = Arrays.asList(EXCEL_DATE_FORMAT);
    /**
     * 读取单元格数据（函数类不读取）
     */
    public static Object getCellValue(Cell cell, CellType cellType){
        if(Objects.equals(cellType, CellType.STRING)){
            return cell.getStringCellValue();
        }else if(Objects.equals(cellType, CellType.NUMERIC)){
            if(DateUtil.isCellDateFormatted(cell)){
                return cell.getDateCellValue();
            }else if (EXCEL_DATE_FORMAT_LIST.contains( cell.getCellStyle().getDataFormat())) {
                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是14,31,57,58)
                double cellValue = cell.getNumericCellValue();
                return DateUtil.getJavaDate(cellValue);
            }else {
                return NumberToTextConverter.toText(cell.getNumericCellValue());
            }
        }else if(Objects.equals(cellType, CellType.BLANK)){
            return "";
        }else if(Objects.equals(cellType, CellType.BOOLEAN)){
            return cell.getBooleanCellValue();
        }
        return "";
    }

调用

    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        if(row != null){
             for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if(cell != null)
                Object o = getCellValue(cell, evaluator);
 
            }
        }
 
    }