JAVA集成easypoi，采用反射进行动态实体类生成，实现excel的导入导出

---

前言

• 本编文章是基于easypoi，javassist实现excel动态导入导出，导入导出采用easypoi的功能，实体类，采用javassist实现动态实体类生成（字段生成，get
  set方法生成，注解生成），通过动态生成的实体类进行excel的导入导出。
  参考资料：https://blog.csdn.net/AirOrange_qi/article/details/107355529

---

一、javassist是什么？

Javassist是可以动态编辑Java字节码的类库。它可以在Java程序运行时定义一个新的类，并加载到JVM中；还可以在JVM加载时修改一个类文件。Javassist使用户不必关心字节码相关的规范也是可以编辑类文件的。
在Javassist中每个需要编辑的class都对应一个CtCLass实例，CtClass的含义是编译时的类（compile time class），这些类会存储在Class Pool中（Class poll是一个存储CtClass对象的容器）。
CtClass中的CtField和CtMethod分别对应Java中的字段和方法。通过CtClass对象即可对类新增字段和修改方法等操作了。

二、easypoi是什么

easypoi是一个开源excel,word处理工具类，可以方便的进行Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言就能实现导入导出操作(熟悉的表达式语法)

三、代码实现

1.动态生成实体类

1.1 声明注解

public static final String CLASS_NAME_PREFIX = "eimsp.common.util.EasyPoiExcelVO@";
public static final String ANNOTATION_PACKAGE_NAME_EXCEL = "cn.afterturn.easypoi.excel.annotation.Excel";
public static final String ANNOTATION_PACKAGE_NAME_NOTNULL = "javax.validation.constraints.NotNull";
public static final String ANNOTATION_PACKAGE_NAME_NOTBLANK = "javax.validation.constraints.NotBlank";
public static final String ANNOTATION_PACKAGE_NAME_NOTEMPTY = "javax.validation.constraints.NotEmpty";
public static final String ANNOTATION_PACKAGE_NAME_PATTERN = "javax.validation.constraints.Pattern";
public static final String STRING_PACKAGE_NAME = "java.lang.String";
public static final String INTEGER_PACKAGE_NAME = "java.lang.Integer";
public static final String DOUBLE_PACKAGE_NAME = "java.lang.Double";
public static final String DATE_PACKAGE_NAME = "java.util.Date";
public static final String ANNOTATION_PACKAGE_NAME_EXCELTARGET = "cn.afterturn.easypoi.excel.annotation.ExcelTarget";
public static final String REGEXP_DATE = "^[0-9]{4}-(0[1-9]{1})|(1[1|2]{1})$";

1.2 获取动态生成的实体方法

public static Class<?> generatePrototypeClass(String className,List<ColumnVO> list) throws  CannotCompileException, NotFoundException,IOException {

//        String className = CLASS_NAME_PREFIX + RandomUtil.uuId();

  	ClassPool pool = ClassPool.getDefault();
    CtClass clazz = pool.makeClass(className);
    ClassFile ccFile = clazz.getClassFile();
    ConstPool constpool = ccFile.getConstPool();
    addExpressField(pool, clazz, constpool,list);
    return clazz.toClass();
}

private static void addExpressField(ClassPool pool, CtClass clazz, ConstPool constpool,List<ColumnVO> list) throws CannotCompileException, NotFoundException {
   // 将数据库查出动态附上property 属性
    for (ColumnVO columnVO : list) {
        addFieldAndAnnotation(pool, clazz, constpool, columnVO);
    }
}

1.3 给字段增加字段和注解

private static void addFieldAndAnnotation(ClassPool pool, CtClass clazz, ConstPool constpool, ColumnVO columnVO) throws NotFoundException, CannotCompileException {
    //生成field
    CtField field = null;
    AnnotationsAttribute fieldAttr = new AnnotationsAttribute(constpool, AnnotationsAttribute.visibleTag);

    //加Hibernate Validator注解
    List<Annotation> annotations = new ArrayList<>();
    //增加excel注解
    List<AnnotationMember> annotationMembers = new ArrayList<>();
    annotationMembers.add(new AnnotationMember("name",columnVO.getProperty()));
    if("Date".equals(columnVO.getPropertyType())){
        String format = "yyyy/MM/dd";
        if(StringUtil.isNotEmpty(columnVO.getDateFormat())){
            format = columnVO.getDateFormat();
        }
        annotationMembers.add(new AnnotationMember("importFormat",format));
        annotations.add(getAnnotation(constpool,ANNOTATION_PACKAGE_NAME_EXCEL,annotationMembers));
    }else{
        annotations.add(getAnnotation(constpool,ANNOTATION_PACKAGE_NAME_EXCEL,annotationMembers));
    }
    //增加验证注解
    switch (columnVO.getPropertyType()){
        case "String":
            field = new CtField(pool.getCtClass(STRING_PACKAGE_NAME), columnVO.getProperty(), clazz);
            if(columnVO.isRequired()){
                annotations.add(getAnnotation(constpool,ANNOTATION_PACKAGE_NAME_NOTBLANK,new ArrayList<>()));
            }
            break;
        case "Integer":
            field = new CtField(pool.getCtClass(INTEGER_PACKAGE_NAME), columnVO.getProperty(), clazz);
            if(columnVO.isRequired()){
                annotations.add(getAnnotation(constpool,ANNOTATION_PACKAGE_NAME_NOTNULL,new ArrayList<>()));
            }
            break;
        case "Double":
            field = new CtField(pool.getCtClass(DOUBLE_PACKAGE_NAME), columnVO.getProperty(), clazz);
            if(columnVO.isRequired()){
                annotations.add(getAnnotation(constpool,ANNOTATION_PACKAGE_NAME_NOTNULL,new ArrayList<>()));
            }
            break;
//            case "Date":
//                field = new CtField(pool.getCtClass(STRING_PACKAGE_NAME), columnVO.getProperty(), clazz);
//                if(columnVO.isRequired()){
//                    //增加excel注解
//                    List<AnnotationMember> annotationMemberList = new ArrayList<>();
//                    annotationMemberList.add(new AnnotationMember("regexp",REGEXP_DATE));
//                    annotationMemberList.add(new AnnotationMember("message","日期格式错误！"));
//                    annotations.add(getAnnotation(constpool,ANNOTATION_PACKAGE_NAME_PATTERN,annotationMemberList));
//                }
//                break;
        default:
            field = new CtField(pool.getCtClass(STRING_PACKAGE_NAME), columnVO.getProperty(), clazz);
            if(columnVO.isRequired()){
                annotations.add(getAnnotation(constpool,ANNOTATION_PACKAGE_NAME_NOTEMPTY,new ArrayList<>()));
            }
           break;
    }
    field.setModifiers(Modifier.PUBLIC);
    //添加easypoi的注解
    field.getFieldInfo().addAttribute(fieldAttr);
    //注入注解
    annotations.forEach(fieldAttr::addAnnotation);
    //增加字段
    clazz.addField(field);
    //生成get,set方法
    clazz.addMethod(CtNewMethod.getter("get" + ExcelUtil.upperFirstLatter(columnVO.getProperty()), field));
    clazz.addMethod(CtNewMethod.setter("set" + ExcelUtil.upperFirstLatter(columnVO.getProperty()), field));
}
//给字段增加注解
private static Annotation getAnnotation(ConstPool constPool,String annotationName,List<AnnotationMember> annotationMembers) {
    Annotation annotation = new Annotation(annotationName, constPool);
    annotationMembers.forEach(item -> {
        annotation.addMemberValue(item.getName(), new StringMemberValue(item.getValue(), constPool));
    });
    return annotation;
}

2.easypoi导入

2.1 导入util方法

public static ExcelImportResult<?> importExcel(File file, Integer titleRows, Integer headerRows, List<ColumnVO> columnVOS) throws IOException, CannotCompileException, NotFoundException, IllegalAccessException, InstantiationException {
    if (file == null){
        return null;
    }

    ImportParams params = new ImportParams();
//        params.setTitleRows(titleRows);
    params.setHeadRows(1);
    params.setStartRows(1);
    List<ColumnVO> columnVoList = new ArrayList<>();
    //清除下划线
//        columnVOS.forEach(item -> {
            item.setProperty(lowerFirstLatter(item.getProperty().replace("F_","")));
//            columnVoList.add(item);
//        });
    // 检验表的是否合法
    String[] importFields = columnVOS
            .stream()
            .map(ColumnVO::getProperty).toArray(String[]::new);
    params.setImportFields(importFields);
    // 开启Excel校验
    params.setNeedVerify(true);

    ExcelImportResult<?> list = null;
    String className = "eimsp.common.util.EasyPoiExcelVO@"+RandomUtil.uuId();
    //建立实体类
    Class<?>  clazz = EasyPoiDynamicGenerationClassUtil.generatePrototypeClass(className,columnVOS);
//        ExcelUtil excelUtil = new ExcelUtil();
//        Class<?> clazz =  excelUtil.defineClass(className,classBytes,0,classBytes.length);

//        System.out.println("注解:"+clazz.getFields()[0].getAnnotation(Excel.class));
//        System.out.println("类:"+JSONUtil.getObjectToString(clazz));
//        Field[] fields = PoiPublicUtil.getClassFields(clazz);
//        System.out.println("字段:" + JSONUtil.getObjectToString(fields));
//        for (Field field : fields) {
//            Annotation[] annotations2 = field.getAnnotations();
//            Annotation annotations = field.getAnnotation(Excel.class);
//            Annotation annotations1 = field.getAnnotation(NotNull.class);
//            System.out.println("注解:"+annotations+":"+annotations2.length);
//        }
//        Object object = clazz.newInstance();
//        System.out.println("对象:"+JSONUtil.getObjectToString(object));
//        Method[] methods = object.getClass().getMethods();
    list = ExcelImportUtil.importExcelMore(file, clazz, params);
    assert list != null;
    return list;
}

2.1 解析导入excel数据，进行表头关联查询，字段匹配映射

2.1.1 读取导入文件，缓存，读取导入字段

 if (Objects.requireNonNull(file.getOriginalFilename()).contains(".xlsx") ||
                Objects.requireNonNull(file.getOriginalFilename()).contains(".xls")) {
            String filePath = configValueUtil.getTemporaryFilePath();
            String fileName = RandomUtil.uuId() + "." + UpUtil.getFileType(file);
            //保存文件
            FileUtil.upFile(file, filePath, fileName);
            File temporary = new File(filePath + fileName);
            //获取配置字段
            Map<String, Object> formData = JSONUtil.StringToMap(visualdevEntity.getFormData());
            List<FieLdsModel> modelList = JSONUtil.getJsonToList(formData.get("fields").toString(), FieLdsModel.class);
            //获取表字段
            Map<String, Object> tableData = JSONUtil.StringToMap(visualdevEntity.getTables());
            List<eimsp.util.model.TableFields> fieldsList = JSONUtil.getJsonToList(tableData.get("fields").toString(), eimsp.util.model.TableFields.class);
   }        

2.1.2 获取标题数据构建实体信息，实现字段类型映射，关联字段查询映射

//读取标题数据
long start1 = System.currentTimeMillis();
List<?> titleList = ExcelUtil.importMapExcel(temporary, 0, 1, fieldsList);
System.out.println("读取标题：" + (System.currentTimeMillis() - start1) + "ms");
long start2 = System.currentTimeMillis();
if (titleList.size() <= 0) {
    //返回信息，无表头标题，无法导入
}
//获取表头信息形成实体，用于后续的文件导入和数据插入
Map<String, Object> map = (Map<String, Object>) titleList.get(0);
ImportSqlModel importSqlModel = new ImportSqlModel();

List<RelationSqlModel> relationSqlModels = new ArrayList<>();
List<ColumnVO> importFields = new ArrayList<>();

for (Map.Entry<String, Object> entry : map.entrySet()) {
    ColumnVO columnVO = new ColumnVO();
    columnVO.setProperty(ExcelUtil.lowerFirstLatter(entry.getKey()));
    columnVO.setPropertyType("String");
    String value = entry.getValue().toString();
    //判断是否是关联字段
    if (StringUtil.isNotEmpty(value) && value.contains("#")) {
        RelationSqlModel relationSqlModel = new RelationSqlModel();
        String[] relationArr = value.split("#");
        //设置主表赋值字段
        relationSqlModel.setAssignField(entry.getKey());
        //是否存在3个条件：名称，关联表信息，关联字段信息
        if (relationArr.length > 1) {
            columnVO.setName(relationArr[0]);
            //设置查询条件字段
            relationSqlModel.setConditionField(relationArr[2]);
            //设置表
            String[] leftField = relationArr[1].split("@");
            //是否存在2个条件：表名，两个查询字段
            if (leftField.length > 1) {
                relationSqlModel.setTableName(leftField[0]);
                List<String> relationFields = new ArrayList<>();
                //仅支持两个字段
                relationFields.add(leftField[1]);
                relationFields.add(leftField[2]);
                relationSqlModel.setFields(relationFields);
                relationSqlModel.setRelaConditionField(leftField[2]);
            }
        }
        relationSqlModels.add(relationSqlModel);
    } else {
        columnVO.setName(value);
        if (value.contains("*")) {
            columnVO.setRequired(true);
        }
    }
    Optional<FieLdsModel> optional = modelList.stream().filter(model -> model.getVModel().equals(columnVO.getProperty())).findFirst();
    if (optional.isPresent()) {
//                    columnVO.setName(ExcelUtil.lowerFirstLatter(columnVO.getProperty()));
//                    columnVO.setProperty(columnVO.getName());
        //必填校验
//                    columnVO.setRequired(columnVO.isRequired());
        //类型校验属性设置
        if (optional.get().getConfig().getJnpfKey().contains("date")) {
            columnVO.setPropertyType("Date");
            //设置时间格式
            columnVO.setDateFormat(StringUtil.isEmpty(optional.get().getFormat()) ? "yyyy/MM/dd" : optional.get().getFormat());
        }
        //数值类型
        else if (optional.get().getConfig().getJnpfKey().contains("Amount")) {
            //判断数值精度
            if (optional.get().getPrecision() > 0) {
                columnVO.setPropertyType("Double");
            } else {
                columnVO.setPropertyType("Integer");
            }
        } else {
            //输入框
            columnVO.setPropertyType("String");
        }
    }
    importFields.add(columnVO);
}
importSqlModel.setFields(importFields);
importSqlModel.setRelationSqlModels(relationSqlModels);

2.1.3 读取excel数据，从数据库查询关联数据匹配赋值，批量导入数据到数据库

2.1.3.1 读取excel

ExcelImportResult excelImportResult = ExcelUtil.importExcel(temporary, 0, 1, importFields);
System.out.println("读取文件：" + (System.currentTimeMillis() - start2) + "ms");
long start3 = System.currentTimeMillis();
//成功读取的数据
List<?> successListData = excelImportResult.getList();

2.1.3.2 关联数据查询

 //获取关联的数据
List<Map<String, Object>> insertListMap = new ArrayList<>();
@Cleanup Connection conn = VisualUtils.getTableConn();
//查询需要关联的数据
if (successListData.size() > 0) {
    successListData.forEach(item -> {
        Map<String, Object> successObjectMap = JSONUtil.EntityToMap(item);
//                    Map<String,Object> successObjectMap1 = JSON.parseObject(JSON.toJSONString(item),Map.class);
        //存储成功导入Map数据
        insertListMap.add(successObjectMap);
    });
    if (importSqlModel.getRelationSqlModels().size() > 0) {
        for (RelationSqlModel rela : importSqlModel.getRelationSqlModels()) {
            //拼接查询SQL
            StringBuilder sql = new StringBuilder();
            sql.append("select ").append(StringUtil.join(rela.getFields(), ","))
                    .append(" from ").append(rela.getTableName()).append(" where ")
                    .append(rela.getRelaConditionField()).append(" in (");
            insertListMap.forEach(item -> {
                //拼接查询条件
                sql.append("'").append(item.get(ExcelUtil.lowerFirstLatter(rela.getConditionField()))).append("',");
            });
            sql.deleteCharAt(sql.length() - 1);
            sql.append(")");
            //查询的结果数据
            List<VisualdevModelDataEntity> visualdevModelDataEntities = VisualUtils.getTableDataList(conn, sql.toString(), null);
            //
            //                    relaDataMap.put(rela.getAssignField(), relaList);
            for (Map<String, Object> successData : insertListMap) {
                for (VisualdevModelDataEntity item : visualdevModelDataEntities) {
                    Map<String, Object> selectData = JSONUtil.StringToMap(item.getData());
                    Map<String, Object> upperSelectData = new HashMap();
                    for (String key : selectData.keySet()) {
                        upperSelectData.put(ExcelUtil.upperFirstLatter(key), selectData.get(key));
                    }
                    //获取需要赋值的数据
                    String assignFieldData = (String) upperSelectData.get(ExcelUtil.upperFirstLatter(rela.getFields().get(0)));
                    //需要比较的值
                    String selectDataValue = (String) upperSelectData.get(ExcelUtil.upperFirstLatter(rela.getRelaConditionField()));
                    if (successData.get(rela.getConditionField()).equals(selectDataValue)) {
                        successData.put(rela.getAssignField(), assignFieldData);
                        //匹配成功跳出这个循环
                        break;
                    }
                }
            }
        }
    }

2.1.3.3 批量导入数据

//插入数据库数据
StringBuilder insertSql = new StringBuilder();
//提取插入表字段
List<String> fields = importSqlModel.getFields().stream().map(ColumnVO::getProperty).distinct().collect(Collectors.toList());
//表字段类型
Map<String, String> fieldTypes = importSqlModel.getFields().stream().collect(Collectors.toMap(ColumnVO::getProperty, ColumnVO::getPropertyType, (key1, key2) -> key2));
if (insertListMap.size() > 0 && fields.size() > 0 && fieldTypes.size() > 0) {
    insertSql.append("insert into ").append(tableData.get("table")).append("  (").append(StringUtil.join(fields, ","))
            .append(") values ");
    insertListMap.forEach(item -> {
        insertSql.append("(");
        fields.forEach(field -> {
            //id赋值
            if ("f_id".equals(StringUtil.lowerCase(field))) {
                insertSql.append("'");
                insertSql.append(RandomUtil.uuId());
                insertSql.append("',");
                return;
            }
            //判断是否有值
            boolean isValue = false;
            for (Map.Entry<String, Object> entry : item.entrySet()) {
                if (field.equals(ExcelUtil.lowerFirstLatter(entry.getKey()))) {
                    isValue = true;
                    if ("Integer".equals(fieldTypes.get(entry.getKey())) ||
                            "Double".equals(fieldTypes.get(entry.getKey()))) {
                        //数值赋值
                        insertSql.append(entry.getValue());
                        insertSql.append(",");
                    } else {
                        //字符串赋值
                        insertSql.append("'");
                        insertSql.append(entry.getValue());
                        insertSql.append("',");
                    }
                }
            }
            if (!isValue) {
                //无数据赋值空
                insertSql.append("null,");
            }
        });
        //清空最后一个逗号
        insertSql.deleteCharAt(insertSql.length() - 1);
        insertSql.append("),");
    });
    //清空最后一个逗号
    insertSql.deleteCharAt(insertSql.length() - 1);
}
//插入数据
Map<String, Object> resultMap = JdbcUtil.customSql(conn, insertSql.toString());
Map<String, Object> resultCountMap = new HashMap<>();
resultCountMap.put("insertSuccess", (int) resultMap.get("resultCount") > 0 ? insertListMap.size() : (int) resultMap.get("resultCount"));
resultCountMap.put("insertSuccessMsg", resultMap.get("resultMsg"));
System.out.println("插入数据成功：" + resultCountMap.get("insertSuccess"));
excelImportResult.setMap(resultCountMap);
System.out.println("插入数据：" + (System.currentTimeMillis() - start4) + "ms");

3. easypoi导出

List<ExcelExportEntity> entitys = new ArrayList<>();
//循环表字段,生成导出实体
for (TableFields model : tableFields) {
    if (keys.length > 0) {
        for (String key : keys) {
            if (key.equals(model.getField())) {
                entitys.add(new ExcelExportEntity(model.getFieldName(), model.getField()));
            }
        }
    }
}
ExportParams exportParams = new ExportParams(null, "表单信息");
//根据生成的导出实体，导出list数据
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entitys, list);
String fileName = "表单信息" + DateUtil.dateNow("yyyyMMddHHmmss") + ".xls";
vo.setName(fileName);
vo.setUrl(UploaderUtil.UploaderFile(userInfo.getId() + "#" + fileName + "#" + "Temporary"));
path = path + fileName;
FileOutputStream fos = new FileOutputStream(path);
workbook.write(fos);
fos.close();

后续拓展

后续可以根据easypoi和javassist的特性，增加字典数据中文 code码映射，复杂正则校验，主子表导入等功能。

总结

以上就是今天要讲的内容，本文仅仅简单介绍了easypoi和javassist的简单使用，而easypoi和javassist提供了大量能使我们快速便捷地处理数据的函数和方法，请自行了解。