POI 对excel 引用表达式读取_excel poi读取表达式-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_45168162/article/details/121743683

该代码段展示了如何解析Excel单元格的内容，特别是处理数值和日期格式。当遇到日期格式时，它会转换为'yyyy-MM-dd'，对于时间格式则转换为'HH:mm'。此外，还涉及了公式求值和处理布尔值的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考：https://stackoom.com/question/3YJrt


    static FormulaEvaluator evaluator = null;
    static DataFormatter dataFormatter = new DataFormatter();

/**
     * 解析
     *
     * @param cell
     * @return
     */
    public static String parseCell(Cell cell) {
        String temp = "";
        if (ObjectUtil.isEmpty(cell)) {
            return temp;
        }
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            short format = cell.getCellStyle().getDataFormat();
            SimpleDateFormat sdf = null;
            if (format == 14 || format == 31 || format == 57 || format == 58
                    || (176 <= format && format <= 178) || (182 <= format && format <= 196)
                    || (210 <= format && format <= 213) || (208 == format)) { // 日期
                sdf = new SimpleDateFormat("yyyy-MM-dd");
            } else if (format == 20 || format == 32 || format == 183 || (200 <= format && format <= 209)) { // 时间
                sdf = new SimpleDateFormat("HH:mm");
            } else { // 不是日期格式
                return temp = dataFormatter.formatCellValue(cell);
//                    return String.valueOf(cell.getNumericCellValue());
            }
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
            if (ObjectUtil.isEmpty(temp)) {
                return temp;
            }
            try {
                temp = sdf.format(date);
            } catch (Exception e) {
                e.printStackTrace();
                return temp;
            }
            return temp;
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            temp = convertByTrim(cell.getStringCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            if (evaluator == null) {
                throw new RuntimeException("evaluator is null");
            }
            temp = dataFormatter.formatCellValue(cell, evaluator);

//             获取表达是的引用，再获取值
//            final Workbook workBook = WorkbookFactory.create(new FileInputStream(new File("config.xlsx")));
//            final FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();
//            final Sheet sheet = workBook.getSheetAt(0);
//            CellReference cellReference = new CellReference("B2");
//            Row row = sheet.getRow(cellReference.getRow());
//            Cell cell = row.getCell(cellReference.getCol());
//            CellValue cellValue = evaluator.evaluate(cell);
//            System.out.println(cellValue.getCellType());

//            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
//                Row ros = sheet.getRow(i);
//            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        }
        if (temp != null) {
            return temp.trim();
        }
        return temp;
    }