POI导入时经常碰到字段类型和导入的类型冲突

我们在用poi导入时导入的Excel列的字段类型经常和实体类里的不一致

//导入类
public class ImportVo {
    @ExcelVOAttribute(name = "名称",column="A")
    private String mc;
    @ExcelVOAttribute(name = "数量",column="B")
    private Integer sl;
    @ExcelVOAttribute(name = "单价",column="C")
    private Double dj;
    @ExcelVOAttribute(name = "金额",column="D")
    private Double je;

    public String getMc() {
        return mc;
    }

    public void setMc(String mc) {
        this.mc = mc;
    }

    public Integer getSl() {
        return sl;
    }

    public void setSl(Integer sl) {
        this.sl = sl;
    }

    public Double getDj() {
        return dj;
    }

    public void setDj(Double dj) {
        this.dj = dj;
    }

    public Double getJe() {
        return je;
    }

    public void setJe(Double je) {
        this.je = je;
    }
    @Override
    public String toString() {
        return "ImportVo{" +
                "mc='" + mc + '\'' +
                ", sl=" + sl +
                ", dj=" + dj +
                ", je=" + je +
                '}';
    }
}

 public void importcs(HttpServletRequest request) throws Exception {
        MultipartHttpServletRequest mRequest = (MultipartHttpServletRequest) request;
        Iterator<String> files = mRequest.getFileNames();
        //只取第一个EXCEL文件，只取第一个sheel
        if (files.hasNext()) {
            MultipartFile multipartFile = mRequest.getFile(files.next());
            MyExcelUtils<ImportVo> myExcelUtils = new MyExcelUtils(multipartFile, ImportVo.class);
            List<ImportVo> list = myExcelUtils.importExcel(3);
            list.forEach(System.out::println);
        }
    }

MultipartFile 工具类

public class MyExcelUtils<T>{

    HSSFWorkbook workbook=null;// 工作薄对象
    HSSFSheet sheet=null;// 工作表对象
    ServletOutputStream output=null;//输出流
    HSSFRow row=null;//行
    HSSFCell cell=null;//单元格
    CellStyle style =null;//单元格样式
    String path;//文件路径
    InputStream input;//输入流
    MultipartFile file;//上传的文件
	Class<T> clazz;

    public MyExcelUtils() {
		super();
	}
    /*导出时候用的到*/
	public MyExcelUtils(Class<T> clazz) {
		super();
		this.clazz = clazz;
	}
    /*导出入时候用的到*/
	public MyExcelUtils(MultipartFile file, Class<T> clazz) throws Exception {
		super();
		input = file.getInputStream();
		this.clazz = clazz;
	}

    /** *导入数据* *//***/
    public List<T> importExcel(Integer beginRowIndex){
        String sheetName="";
        beginRowIndex=beginRowIndex==null?1:beginRowIndex;
        return importExcel( sheetName, beginRowIndex);
    }

    public List<T> importExcel(String sheetName,Integer beginRowIndex) {
        int maxCol = 0;
        List<T> list = new ArrayList<T>();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(input);
            HSSFSheet sheet = workbook.getSheet(sheetName);
            if (!sheetName.trim().equals("")) {
                sheet = workbook.getSheet(sheetName);// 如果指定sheet名,则取指定sheet中的内容.
            }
            if (sheet == null) {
                sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet.
            }
            int rows = sheet.getPhysicalNumberOfRows();
            if (rows > 0) {// 有数据时才处理
                // Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
                List<Field> allFields = ExcelFragment.fltMappedFileds(ExcelFragment.getMappedFiled(clazz, null),2);
                Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();// 定义一个map用于存放列的序号和field.
                for (Field field : allFields) {
                    // 将有注解的field存放到map中.
                    if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
                        ExcelVOAttribute attr = field .getAnnotation(ExcelVOAttribute.class);
                        int col = ExcelFragment.getExcelCol(attr.column());// 获得列号
                        maxCol = Math.max(col, maxCol);
                        field.setAccessible(true);// 设置类的私有字段属性可访问.
                        fieldsMap.put(col, field);
                    }
                }
                for (int i = beginRowIndex; i < rows; i++) {// 从第4行开始取数据,默认第3行是表头.
                    HSSFRow row = sheet.getRow(i);
                    if(row==null) {
                        continue;
                    }
                    int cellNum = maxCol+1;
                    T entity = null;
                    for (int j = 0; j < cellNum; j++) {
                        HSSFCell cell = row.getCell(j);
                        Object cellval=null;
                        if (cell == null){
                            continue;
                        }

                        Field field = fieldsMap.get(j);// 从map中得到对应列的field.
                        if (field==null) {
                            continue;
                        }
                        entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.

                        Class<?> fieldType = field.getType();
                        if (String.class == fieldType) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cellval= cell.getStringCellValue();
                            field.set(entity, String.valueOf(cellval==null?"":cellval));
                        } else if ((Integer.TYPE == fieldType)  || (Integer.class == fieldType)) {
                            cellval=cell.getNumericCellValue();
                            field.set(entity, Double.valueOf((cellval==null?0.0:cellval).toString()).intValue());
                        } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                            cellval=cell.getNumericCellValue();
                            field.set(entity, Long.valueOf( cellval.toString()));
                        } else if ((Float.TYPE == fieldType)  || (Float.class == fieldType)) {
                            cellval=cell.getNumericCellValue();
                            field.set(entity, Float.valueOf(cellval.toString()));
                        } else if ((Short.TYPE == fieldType)   || (Short.class == fieldType)) {
                            cellval=cell.getNumericCellValue();
                            field.set(entity, Short.valueOf(cellval.toString()));
                        } else if ((Double.TYPE == fieldType)   || (Double.class == fieldType)) {
                            cellval=cell.getNumericCellValue();
                            field.set(entity, Double.valueOf((cellval==null?0.0:cellval).toString()));
                        } else if (Character.TYPE == fieldType) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cellval= cell.getStringCellValue();
                            String c=(String) cellval;
                            if ((c != null) && (c.length() > 0)){
                                field.set(entity, Character .valueOf(c.charAt(0)));
                            }
                        }
                        // 取得类型,并根据对象类型设置值.

                    }
                    if (entity != null) {
                        list.add(entity);
                    }
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
            log.error("EXCEL 导入失败:IO异常"+e.getMessage());
        } catch (InstantiationException e) {
            e.printStackTrace();
            log.error("EXCEL 导入失败:InstantiationException异常"+e.getMessage());
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            log.error("EXCEL 导入失败:IllegalAccessException异常"+e.getMessage());
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
            log.error("EXCEL 导入失败:IllegalArgumentException异常"+e.getMessage());
        }  catch( Exception e){
            e.printStackTrace();
            log.error("EXCEL 导入失败:其他异常"+e.getMessage());
        }
        return list;
    }
}

那我们可以新建一个导入的实体类全部设置成String类型的

//新的导入类
public class ImportCs {
    @ExcelVOAttribute(name = "名称",column="A")
    private String mc;
    @ExcelVOAttribute(name = "数量",column="B")
    private String sl;
    @ExcelVOAttribute(name = "单价",column="C")
    private String dj;
    @ExcelVOAttribute(name = "金额",column="D")
    private String je;

    public String getMc() {
        return mc;
    }

    public void setMc(String mc) {
        this.mc = mc;
    }

    public String getSl() {
        return sl;
    }

    public void setSl(String sl) {
        this.sl = sl;
    }

    public String getDj() {
        return dj;
    }

    public void setDj(String dj) {
        this.dj = dj;
    }

    public String getJe() {
        return je;
    }

    public void setJe(String je) {
        this.je = je;
    }
}

再次导入就可以