利用注解实现excel导入转对象（easyexcel 注解）

excel表头注解

/**
 * excel表头注解
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelMapper {

    int sheetNo() default 0;//默认sheet 默认为0即第一页

    int headerRowNo() default 1;//默认读取、写入行号 默认第一行（标题栏）

    int dataStartRow() default 2;//开始读取数据行号 默认第二行

    int maxRow() default 10000;//最大导入导出数据量
}

execl列注解

/**
 * execl列注解
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelDataMapper {

    int cellNo() default -1;//位置

    String name() default "";//列名
    
    String beanName() default "";//属性名

    int maxLength() default 100000;//最大长度

    int minLength() default 0;//最小长度

    int maxValue()  default 2147483647;//最大值

    int minValue() default -2147483648;//最小值

    boolean required() default false;//是否可以为空

    boolean skip() default false;//是否忽略

    RowValidateType type() default RowValidateType.STRING;//验证格式

    enum RowValidateType{
        //字符串
        STRING(1),
        // 手机号码验证,11位，不知道详细的手机号码段，只是验证开头必须是1和位数
        PHONE(2),
        // Date(yyyy-MM-dd HH:mm:ss)
        DATE(3),
        // Date(yyyy-MM-dd)
        DATE_YMD(4),
        //验证电话号码（不一定是手机）
        TELEPHONE(5),
        //检验金额
        SCORE(6),
        //检验整数
        INTEGER(7),
        //email
        EMAIL(8),
        //boolean
        BOOLEAN(9),
        //doluble
        DOUBLE(10),
        //网络地址
        URL(11),
        //数字字符串
        NUMBER_STR(12),
        //唯一的
        UNIQUE(99);

        private final int value;

        RowValidateType(int value) {
            this.value = value;
        }

        public int value() {
            return this.value;
        }

        public String toString() {
            return Integer.toString(this.value);
        }

        public static RowValidateType valueOf(int levelValue) {
            for (RowValidateType logLevel : values()) {
                if (logLevel.value == levelValue) {
                    return logLevel;
                }
            }
            throw new IllegalArgumentException("No matching constant for ["
                    + levelValue + "]");
        }
    }
}

主角来了，导出工具类

/**
 * excel导入工具类
 *
 */
public class ImportsExcel {

    private Log logger = LogFactory.getLog(getClass());

    /**
     * 获取excel返回的集合对象
     * @param clazz    类
     * @param filePath 导入的excel文件路径
     * @return ImportData
     */
    public ImportData getObjectList(Class clazz,String name, InputStream inputStream) {
        ImportData data = new ImportData();
        List<Object> objList = new ArrayList<>();//记录正确数据
        List<String> msgList = new ArrayList<>();//记录错误信息集合
        
        name = name.substring(name.lastIndexOf("."), name.length());
        //判断文件类型是否正确
        if (name.equals(".xls") || name.equals(".xlsx")) {
            Workbook workbook;
            try {
               //workbook = new XSSFWorkbook(inputStream);
                workbook = WorkbookFactory.create(inputStream);
                ExcelMapper excelMapper = (ExcelMapper) clazz.getAnnotation(ExcelMapper.class);
                //判断是否使用注解
                if (null != excelMapper) {
                    Sheet sheet = workbook.getSheetAt(excelMapper.sheetNo());
                    //超出最大导入条数则拒绝导入
                    if ((sheet.getLastRowNum() + 1) <= excelMapper.maxRow()) {
                        //加载所有注解导入项
                        List<ExcelDataMapper> excelDataMapperList = new ArrayList<>();
                        for (Field field : clazz.getDeclaredFields()) {
                            ExcelDataMapper excelDataMapper = field.getAnnotation(ExcelDataMapper.class);
                            if (null != excelDataMapper && !excelDataMapper.skip()) {//不忽略
                                excelDataMapperList.add(excelDataMapper);
                            }
                        }
                        if(excelDataMapperList.size()==0){
                            msgList.add("导入实例对象属性未使用注解");
                            data.setMsg(msgList);
                            return data;
                        }
                        //判断是否套用模板
                        Row headerRow = sheet.getRow(excelMapper.headerRowNo());//表头行
                        for (ExcelDataMapper excelDataMapper : excelDataMapperList) {
                            try {
                                if (headerRow.getCell(excelDataMapper.cellNo()).getStringCellValue().trim().equals(excelDataMapper.name())) {
                                    continue;
                                }
                            } catch (Exception e) {
                                logger.error(excelDataMapper.name() + "非指定模板");
                            }
                            msgList.add(excelDataMapper.name() + "列名不匹配,请确认是否套用了模版");
                            data.setMsg(msgList);
                            return data;
                        }
                        
                       // List<Object> errorList = new ArrayList<>();//记录错误数据对象
                        //验证及装载excel导入的数据
                        for (Row row : sheet) {
                            //从标题栏下一行开始
                            if (row.getRowNum() > excelMapper.headerRowNo()) {
                               
                               Object data1 = ImportValidator.vaildatorData(excelDataMapperList, row, clazz, msgList);
                               
                               objList.add(data1);
                            }
                        }
                    } else {
                        msgList.add("最大导入条数为" + excelMapper.maxRow() + "条,请拆分后导入");
                    }
                } else {
                    logger.error("导入实力对象未使用注解");
                    msgList.add("导入实力对象未使用注解");
                }
            } catch (Exception e) {
                logger.error(name +"请检查文件是否为exle文件"+e.getMessage());
                msgList.add("格式不正确");
            }
        } else {
            msgList.add("文件格式不符,请检查文件是否正确");
        }
        data.setMsg(msgList);
        data.setData(objList);
        return data;
    }

    /**
     * 通过Class得到对象
     *
     * @param clazz 类
     * @return Object
     */
    private Object getModelClass(Class clazz) {
        Object obj = null;
        try {
            obj = clazz.newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            System.out.println(e.getMessage());
        }
        return obj;
    }
}

/**
 * 导入数据对象
 */
public class ImportData {

    private List data;//数据集合

    private List errorList;//错误对象集合

    private List<String> msg;//消息列表

    public List getData() {
        return data;
    }

    public void setData(List data) {
        this.data = data;
    }

    public List getErrorList() {
        return errorList;
    }

    public void setErrorList(List errorList) {
        this.errorList = errorList;
    }

    public List<String> getMsg() {
        return msg;
    }

    public void setMsg(List<String> msg) {
        this.msg = msg;
    }
}

测试类（导出对象）

/**
 * user类对应excel导入列，和数据验证
 */
@ExcelMapper(headerRowNo = 0,maxRow = 1000)
public class UserImport {
    @ExcelDataMapper(cellNo=0,name = "姓名",beanName = "name",maxLength = 30,minLength = 0,required=true)
    private String name;
  
    @ExcelDataMapper(cellNo=1,name = "手机号",beanName = "phone",type=ExcelDataMapper.RowValidateType.PHONE)
    private String phone;

    @ExcelDataMapper(cellNo=2,name = "账号",beanName = "account",maxLength = 50,minLength = 0)
    private String account;


   public String getPhone() {
        return phone;
    }

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

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getAccount() {
        return account;
    }

    public void setAccount(String account) {
        this.account = account;
    }
}

测试接口

 @RequestMapping(value = "/userImport")
 @ResponseBody
 public String userImport(HttpServletRequest request, HttpServletResponse response,
                          @RequestParam(value = "uploadFile") MultipartFile uploadFile) {
     try {
         ImportsExcel importsExcel = new ImportsExcel();
         ImportData objectList = importsExcel.getObjectList(UserImport.class, uploadFile.getOriginalFilename(), uploadFile.getInputStream(), false);
         if (objectList.getMsg().size() > 0) {
             return JsonStrData(1, "错误信息", objectList.getMsg());
         }
         List<UserImport> data = objectList.getData();
         return "已处理" + data.size() + "条";
     } catch (Exception e) {
         e.printStackTrace();
         logger.error("Exception:" + e.getMessage());
         return "抱歉！出错了";
     }
 }