通用POI读取Excel封装成JavaBean

通用POI读取Excel封装成JavaBean

    工作中需要开发一个通过客户上传的Excel，读取数据并更新数据库。解析Excel有很多开源工具，由于项目中有了POI依赖，所以我使用POI来完成此次开发。考虑到直接固定针对此类业务Excel文件开发，虽然简单，但将来如果又有其它的业务同样是需要Excel来提供数据，那么就还需要做Excel的特定解析，显然这样做的话，以后有多少个业务，就需要针对性的写多少个解析代码，为了将来能够少写点代码，同时自己身为中级工程师，代码要显得有点逼格，故此写了这么一个工具类。

一、添加pom依赖

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

二、表头与实体属性关系映射实体

public class ExcelHead {
    private String excelName;             //Excel名
    private String entityName;            //实体类属性名
    private boolean required=false;      //值必填

    public String getExcelName() {
        return excelName;
    }

    public void setExcelName(String excelName) {
        this.excelName = excelName;
    }

    public String getEntityName() {
        return entityName;
    }

    public void setEntityName(String entityName) {
        this.entityName = entityName;
    }

    public boolean isRequired() {
        return required;
    }

    public void setRequired(boolean required) {
        this.required = required;
    }

    public ExcelHead(String excelName, String entityName, boolean required) {
        this.excelName = excelName;
        this.entityName = entityName;
        this.required = required;
    }

    public ExcelHead(String excelName, String entityName) {
        this.excelName = excelName;
        this.entityName = entityName;
    }

    public ExcelHead(){};
}

三、Excel解析工具类

public class ExcelUtils {
    private static final String FULL_DATA_FORMAT = "yyyy/MM/dd  HH:mm:ss";
    private static final String SHORT_DATA_FORMAT = "yyyy/MM/dd";


    /**
     * Excel表头对应Entity属性 解析封装javabean
     *
     * @param classzz    类
     * @param in         excel流
     * @param fileName   文件名
     * @param excelHeads excel表头与entity属性对应关系
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName, List<ExcelHead> excelHeads) throws Exception {
        checkFile(fileName);    //是否EXCEL文件
        Workbook workbook = getWorkBoot(in, fileName); //兼容新老版本
        List<T> excelForBeans = readExcel(classzz, workbook, excelHeads);  //解析Excel
        return excelForBeans;
    }

    /**
     * 解析Excel转换为Entity
     *
     * @param classzz  类
     * @param in       excel流
     * @param fileName 文件名
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName) throws Exception {
        return readExcelToEntity(classzz, in, fileName,null);
    }

    /**
     * 校验是否是Excel文件
     *
     * @param fileName
     * @throws Exception
     */
    public static void checkFile(String fileName) throws Exception {
        if (!StringUtils.isEmpty(fileName) && !(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))) {
            throw new Exception("不是Excel文件！");
        }
    }

    /**
     * 兼容新老版Excel
     *
     * @param in
     * @param fileName
     * @return
     * @throws IOException
     */
    private static Workbook getWorkBoot(InputStream in, String fileName) throws IOException {
        if (fileName.endsWith(".xlsx")) {
            return new XSSFWorkbook(in);
        } else {
            return new HSSFWorkbook(in);
        }
    }

    /**
     * 解析Excel
     *
     * @param classzz    类
     * @param workbook   工作簿对象
     * @param excelHeads excel与entity对应关系实体
     * @param <T>
     * @return
     * @throws Exception
     */
    private static <T> List<T> readExcel(Class<T> classzz, Workbook workbook, List<ExcelHead> excelHeads) throws Exception {
        List<T> beans = new ArrayList<T>();
        int sheetNum = workbook.getNumberOfSheets();
        for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            String sheetName=sheet.getSheetName();
            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();
            Row head = sheet.getRow(firstRowNum);
            if (head == null)
                continue;
            short firstCellNum = head.getFirstCellNum();
            short lastCellNum = head.getLastCellNum();
            Field[] fields = classzz.getDeclaredFields();
            for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
                Row dataRow = sheet.getRow(rowIndex);
                if (dataRow == null)
                    continue;
                T instance = classzz.newInstance();
                if(CollectionUtils.isEmpty(excelHeads)){  //非头部映射方式，默认不校验是否为空，提高效率
                    firstCellNum=dataRow.getFirstCellNum();
                    lastCellNum=dataRow.getLastCellNum();
                }
                for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {
                    Cell headCell = head.getCell(cellIndex);
                    if (headCell == null)
                        continue;
                    Cell cell = dataRow.getCell(cellIndex);
                    headCell.setCellType(Cell.CELL_TYPE_STRING);
                    String headName = headCell.getStringCellValue().trim();
                    if (StringUtils.isEmpty(headName)) {
                        continue;
                    }
                    ExcelHead eHead = null;
                    if (!CollectionUtils.isEmpty(excelHeads)) {
                        for (ExcelHead excelHead : excelHeads) {
                            if (headName.equals(excelHead.getExcelName())) {
                                eHead = excelHead;
                                headName = eHead.getEntityName();
                                break;
                            }
                        }
                    }
                    for (Field field : fields) {
                        if (headName.equalsIgnoreCase(field.getName())) {
                            String methodName = MethodUtils.setMethodName(field.getName());
                            Method method = classzz.getMethod(methodName, field.getType());
                            if (isDateFied(field)) {
                                Date date=null;
                                if(cell!=null){
                                    date=cell.getDateCellValue();
                                }
                                if (date == null) {
                                    volidateValueRequired(eHead,sheetName,rowIndex);
                                    break;
                                }
                                method.invoke(instance, cell.getDateCellValue());
                            } else {
                                String value = null;
                                if(cell!=null){
                                    cell.setCellType(Cell.CELL_TYPE_STRING);
                                    value=cell.getStringCellValue();
                                }
                                if (StringUtils.isEmpty(value)) {
                                    volidateValueRequired(eHead,sheetName,rowIndex);
                                    break;
                                }
                                method.invoke(instance, convertType(field.getType(), value.trim()));
                            }
                            break;
                        }
                    }
                }
                beans.add(instance);
            }
        }
        return beans;
    }

    /**
     * 是否日期字段
     *
     * @param field
     * @return
     */
    private static boolean isDateFied(Field field) {
        return (Date.class == field.getType());
    }

    /**
     * 空值校验
     *
     * @param excelHead
     * @throws Exception
     */
    private static void volidateValueRequired(ExcelHead excelHead,String sheetName,int rowIndex) throws Exception {
        if (excelHead != null && excelHead.isRequired()) {
            throw new Exception("《"+sheetName+"》第"+(rowIndex+1)+"行:\""+excelHead.getExcelName() + "\"不能为空！");
        }
    }

    /**
     * 类型转换
     *
     * @param classzz
     * @param value
     * @return
     */
    private static Object convertType(Class classzz, String value) {
        if (Integer.class == classzz || int.class == classzz) {
            return Integer.valueOf(value);
        }
        if (Short.class == classzz || short.class == classzz) {
            return Short.valueOf(value);
        }
        if (Byte.class == classzz || byte.class == classzz) {
            return Byte.valueOf(value);
        }
        if (Character.class == classzz || char.class == classzz) {
            return value.charAt(0);
        }
        if (Long.class == classzz || long.class == classzz) {
            return Long.valueOf(value);
        }
        if (Float.class == classzz || float.class == classzz) {
            return Float.valueOf(value);
        }
        if (Double.class == classzz || double.class == classzz) {
            return Double.valueOf(value);
        }
        if (Boolean.class == classzz || boolean.class == classzz) {
            return Boolean.valueOf(value.toLowerCase());
        }
        if (BigDecimal.class == classzz) {
            return new BigDecimal(value);
        }
       /* if (Date.class == classzz) {
            SimpleDateFormat formatter = new SimpleDateFormat(FULL_DATA_FORMAT);
            ParsePosition pos = new ParsePosition(0);
            Date date = formatter.parse(value, pos);
            return date;
        }*/
        return value;
    }

    /**
     * 获取properties的set和get方法
     */
    static class MethodUtils {
        private static final String SET_PREFIX = "set";
        private static final String GET_PREFIX = "get";

        private static String capitalize(String name) {
            if (name == null || name.length() == 0) {
                return name;
            }
            return name.substring(0, 1).toUpperCase() + name.substring(1);
        }

        public static String setMethodName(String propertyName) {
            return SET_PREFIX + capitalize(propertyName);
        }

        public static String getMethodName(String propertyName) {
            return GET_PREFIX + capitalize(propertyName);
        }

    }
}

四、测试

Excel表格

创建实体类

public class Excel {
    private String name;
    private short age;
    private  byte code;
    private char sex;
    private int number;
    private long phone;
    private float stone;
    private double money;
    private BigDecimal total;
    private Date born;
    private Short age2;
    private Byte code2;
    private Character sex2;
    private Integer number2;
    private Long phone2;
    private Float stone2;
    private Double money2;
    private boolean diel;
    private Boolean diel2;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public short getAge() {
        return age;
    }

    public void setAge(short age) {
        this.age = age;
    }

    public byte getCode() {
        return code;
    }

    public void setCode(byte code) {
        this.code = code;
    }

    public char getSex() {
        return sex;
    }

    public void setSex(char sex) {
        this.sex = sex;
    }

    public int getNumber() {
        return number;
    }

    public void setNumber(int number) {
        this.number = number;
    }

    public long getPhone() {
        return phone;
    }

    public void setPhone(long phone) {
        this.phone = phone;
    }

    public float getStone() {
        return stone;
    }

    public void setStone(float stone) {
        this.stone = stone;
    }

    public double getMoney() {
        return money;
    }

    public void setMoney(double money) {
        this.money = money;
    }

    public BigDecimal getTotal() {
        return total;
    }

    public void setTotal(BigDecimal total) {
        this.total = total;
    }

    public Date getBorn() {
        return born;
    }

    public void setBorn(Date born) {
        this.born = born;
    }

    public Short getAge2() {
        return age2;
    }

    public void setAge2(Short age2) {
        this.age2 = age2;
    }

    public Byte getCode2() {
        return code2;
    }

    public void setCode2(Byte code2) {
        this.code2 = code2;
    }

    public Character getSex2() {
        return sex2;
    }

    public void setSex2(Character sex2) {
        this.sex2 = sex2;
    }

    public Integer getNumber2() {
        return number2;
    }

    public void setNumber2(Integer number2) {
        this.number2 = number2;
    }

    public Long getPhone2() {
        return phone2;
    }

    public void setPhone2(Long phone2) {
        this.phone2 = phone2;
    }

    public Float getStone2() {
        return stone2;
    }

    public void setStone2(Float stone2) {
        this.stone2 = stone2;
    }

    public Double getMoney2() {
        return money2;
    }

    public void setMoney2(Double money2) {
        this.money2 = money2;
    }

    public boolean isDiel() {
        return diel;
    }

    public void setDiel(boolean diel) {
        this.diel = diel;
    }

    public Boolean getDiel2() {
        return diel2;
    }

    public void setDiel2(Boolean diel2) {
        this.diel2 = diel2;
    }

}

测试代码

  @RequestMapping(value = "uploadExcel", produces = "application/json;charset=UTF-8")
    public Object uploadExcel(@RequestParam MultipartFile file, @RequestParam String userName) throws Exception {
        String name = file.getOriginalFilename();
        System.out.println(userName + "上传:" + name);
        List<Excel>list= ExcelUtils.readExcelToEntity(Excel.class,file.getInputStream(),name);
        return list;
    }

测试结果

结果：

五、Excel中文表头封装javabean测试

Excel表

实体类

public class ExcelUser {
    private String name;
    private Integer age;
    private String  address;
    private Character sex;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Character getSex() {
        return sex;
    }

    public void setSex(Character sex) {
        this.sex = sex;
    }
}

测试代码:

 public static void main(String[] args) {
        File file = new File("D:\\Excel (2).xlsx");
        FileInputStream in = null;
        try {
            in = new FileInputStream(file);
            List<ExcelHead> excelHeads = new ArrayList<ExcelHead>();
            ExcelHead excelHead = new ExcelHead("姓名", "name");
            ExcelHead excelHead1 = new ExcelHead("性别", "sex");
            ExcelHead excelHead2 = new ExcelHead("年龄", "age");
            ExcelHead excelHead3 = new ExcelHead("地址", "address", true);
            excelHeads.add(excelHead);
            excelHeads.add(excelHead1);
            excelHeads.add(excelHead2);
            excelHeads.add(excelHead3);
            List<ExcelUser> list = ExcelUtils.readExcelToEntity(ExcelUser.class, in, file.getName(), excelHeads);
            for (ExcelUser excelUser : list) {
                System.out.println(excelUser.getName() + ":" + excelUser.getSex() + ":" + excelUser.getAge() + ":" + excelUser.getAddress());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(in!=null) {
                try {
                    in.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

测试结果:

可以看到，我在创建表头关系映射的时候，默认姓名不进行非空校验，而对于地址则进行了非空校验，这里抛出了一个异常。

修改后继续测试

测试成功。

本次完成了通用的Excel解析并封装成javabean工具类，并自动适应各种类型。不足之处：可以看到每读取一行就会重新将每一列与实体类的属性进行遍历比对，感觉此处需要优化。我的想法是在读取第一行，也就是表头的时候就记录此列与实体类属性的对应下标，后面则不进行遍历，直接根据下标取值，欢迎大神提出不足之处，对我的代码进行优化。