POI导入

た入场券 2023-06-08 13:57 32阅读 0赞

String fileName = file.getOriginalFilename();
    Workbook workbook = null;
    ExcelUtil eu = new ExcelUtil();
    //判断文件类型
    if (fileName.endsWith("xls")) { 
        try { 
            log.info("这是一个后缀是xls版本的，调用HSSFWorkbook老版本");
            workbook = new HSSFWorkbook(file.getInputStream());// 2003版本
        } catch (IOException e) { 
            e.printStackTrace();
        }
    } else if (fileName.endsWith("xlsx")) { 
        try { 
            log.info("这是一个后缀是xlsx版本的，调用XSSFWorkbook老版本");
            workbook = new XSSFWorkbook(file.getInputStream());// 2007版本
        } catch (IOException e) { 
            e.printStackTrace();
        }
    //不是excel文件就提示错误
    } else { 
        try { 
            return  JsonResult.parameterError("文件不是Excel文件");
        } catch (Exception e) { 
            e.printStackTrace();
        }
    }
    
    //获取到当前sheet页数据
    Sheet sheet = workbook.getSheet("工作表");
    int rows = sheet.getLastRowNum();
    if (rows == 0) { 
        try { 
            return JsonResult.parameterError("数据为空请重新填写数据");
        } catch (Exception e) { 
            e.printStackTrace();
        }
    }
    
    //获取Excel文档中的第一个表单
    Sheet sht0 = workbook.getSheetAt(0);
    Row ro = sht0.getRow(1);
    boolean flag = false;
    Cell cell = ro.getCell(4);
    String cellName = null;
    if (cell != null) { 
        cellName = cell.getStringCellValue();
    }
    if (cellName != null && cellName.trim().length() > 0) { 
        if (!"*性别".equals(cellName)) { 
            throw new Exception("文件不是Excel文件");;
        }
    } else { 
        cell = ro.getCell(2);
        if (cell != null) { 
            cellName = cell.getStringCellValue();
        }
        if (cellName == null || (cellName.trim().length() > 0 && !"*性别".equals(cellName))) { 
           throw new Exception("文件不是Excel文件");;
        }
    }
    
    //对Sheet中的每一行进行迭代
    int count=0;
    for (Row r : sht0) { 
        //如果当前行的行号（从0开始）未达到2（第三行）则从新循环
        int rnum = r.getRowNum() + 1;
        if (r.getRowNum() < 2) { 
            continue;
        }
        eu.getCellValue(r.getCell(0));
        count++;
    }
    System.out.println("---:"+count);
    
    
    /** * 获取Excel单元格的值 * * @param cell * @return */
    public String getCellValue(Cell cell) { 
        String value = "";
        if (cell != null) { 
            switch (cell.getCellTypeEnum()) { 
                case NUMERIC:  //数字
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) { 
                        Date date = cell.getDateCellValue();
                        if (date != null) { 
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);//日期格式化
                        } else { 
                            value = "";
                        }
                    } else { 
                        //在解析cell的时候，数字类型默认是double的，但是想要的的整数的类型，需要格式化，很重要
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case STRING://字符串
                    value = cell.getStringCellValue();
                    break;
                case BOOLEAN://boolean类型
                    value = cell.getBooleanCellValue() + "";
                    break;
                case BLANK://空值
                    value = "";
                    break;
                case ERROR://错误类型
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
            }
        }
        return value.trim();
    }

public class JsonResult implements Serializable { 
    
        private String resultCode;
    
        private String resultMsg;
    
        private Object data;
    
        public JsonResult(String code, String msg) { 
            this.resultCode = code;
            this.resultMsg = msg;
        }
    
        public JsonResult(String code, String msg, Object data) { 
            this.resultCode = code;
            this.resultMsg = msg;
            this.data = data;
        }
    
    
        public static JsonResult build(ResultCode resultCode){ 
            return new JsonResult(resultCode.getCode(), resultCode.getMsg());
        }
    
        public static JsonResult build(ResultCode resultCode, String msg){ 
            return new JsonResult(resultCode.getCode(), resultCode.getMsg()+" "+msg);
        }
    
        public static JsonResult success() { 
            return JsonResult.build(ResultCode.SUCCESS);
        }
    
        public static JsonResult success(Object object) { 
            return new JsonResult(ResultCode.SUCCESS.getCode(), ResultCode.SUCCESS.getMsg(),object);
        }
    
        public static JsonResult parameterError(String msg){ 
            return JsonResult.build(ResultCode.ARGUMENTS_ERROR,msg);
        }
    
    }