excel处理公式获得最终值--------------gxl

最新推荐文章于 2024-07-18 19:13:23 发布

勤读意现

最新推荐文章于 2024-07-18 19:13:23 发布

阅读量190

点赞数

文章标签： excel python 开发语言

本文链接：https://blog.csdn.net/u013154103/article/details/132509625

版权

/**
     * 读取单元格内容 包括计算公式的结果，引用公式的结果
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        System.out.println(cell);
        String value = null;
        if(cell != null){
            System.out.println(cell.getCellType());
            switch (cell.getCellType()){
                case BLANK:
                    value = "";
                    break;
                case BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    switch (cell.getCachedFormulaResultType()){
                        case NUMERIC:
                            if(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)){
                                Date date = cell.getDateCellValue();
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                                value = sdf.format(date);
                            }else{
                                BigDecimal n = new BigDecimal(cell.getNumericCellValue());
                                DecimalFormat decimalFormat = new DecimalFormat("0");
                                decimalFormat.setMaximumFractionDigits(18);
                                value = decimalFormat.format(n.doubleValue());
                            }
                         break;
                        case STRING:
                            value = String.valueOf(cell.getStringCellValue());
                            if(value != null){
                                value = value.trim();
                            }
                            break;
                        case BOOLEAN:
                            value = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case ERROR: value = "";
                        break;
                        default:
                            value = cell.getRichStringCellValue().getString();
                            break;
                    }
                    break;
                case NUMERIC:
                    if(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)){
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                        value = sdf.format(date);
                    }else{
                        BigDecimal n = new BigDecimal(cell.getNumericCellValue());
                        DecimalFormat decimalFormat = new DecimalFormat("0");
                        decimalFormat.setMaximumFractionDigits(18);
                        value = decimalFormat.format(n.doubleValue());
                    }
                    break;
                case STRING:
                    value = String.valueOf(cell.getStringCellValue());
                    if(value != null){
                        value = value.trim();
                    }
                    break;
                default:
                    value = cell.getRichStringCellValue().getString();
                    break;
            }
        }
        return value;
    }