JAVA集成easypoi，采用反射进行动态实体类生成，实现ex
2023-03-13 本文已影响0人
King斌
本编文章是基于easypoi，javassist实现excel动态导入导出，导入导出采用easypoi的功能，实体类，采用javassist实现动态实体类生成（字段生成，get
set方法生成，注解生成），通过动态生成的实体类进行excel的导入导出。
一、javassist是什么？
Javassist是可以动态编辑Java字节码的类库。它可以在Java程序运行时定义一个新的类，并加载到JVM中；还可以在JVM加载时修改一个类文件。Javassist使用户不必关心字节码相关的规范也是可以编辑类文件的。
在Javassist中每个需要编辑的class都对应一个CtCLass实例，CtClass的含义是编译时的类（compile time class），这些类会存储在Class Pool中（Class poll是一个存储CtClass对象的容器）。
CtClass中的CtField和CtMethod分别对应Java中的字段和方法。通过CtClass对象即可对类新增字段和修改方法等操作了。
image.png
二、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");
- 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提供了大量能使我们快速便捷地处理数据的函数和方法，请自行了解。
