需求背景:对于有些表头需要根据配置动态生成,做以下记录。
读取的文件,如下图:
动态导入模板
测试工具类
package com.alibaba.easyexcel.test.demo.read;
import com.alibaba.easyexcel.test.util.TestFileUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import org.junit.Test;
import java.io.File;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Proxy;
import java.util.Map;
import java.util.function.Consumer;
/**
* @description:
* @author: root
* @date: 2022-11-16
*/
public class DynamicReadTest {
/**
* 动态读取表头
* <p>
* 1. 创建excel对应的实体对象 参照{@link DynamicData}
* <p>
* 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link IndexOrNameDataListener}
* <p>
* 3. 直接读即可
*/
@Test
public void readDynamicHead() throws Exception {
reBuildExcelPropertyValue("string", "字符串标题2");
String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
// 这里默认读取第一个sheet
EasyExcel.read(fileName, DynamicData.class, new DynamicDataListener()).sheet().doRead();
}
/**
* 重建注解ExcelProperty中value属性的值
* @param fieldName
* @param value
* @return
* @author root
* @date 2022-11-16
*/
private void reBuildExcelPropertyValue(String fieldName, String value) throws Exception {
Field[] fields = DynamicData.class.getDeclaredFields();
for(Field field : fields) {
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if(annotation != null && field.getName().equals(fieldName)) {
modifyFiled(DynamicData.class, map -> {
map.put("value", new String[]{value});
}, field.getName());
}
}
}
/**
* 修改代理类
* @param clazz
* @param consumer
* @param fieldName
* @return
* @author root
* @date 2022-11-16
*/
public void modifyFiled(Class clazz, Consumer<Map> consumer, String fieldName) throws Exception {
//获取需要修改的属性
Field field = clazz.getDeclaredField(fieldName);
//获取注解
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
//生成代理类对象
InvocationHandler invocationHandler = Proxy.getInvocationHandler(annotation);
Field annotationValues = invocationHandler.getClass().getDeclaredField("memberValues");
annotationValues.setAccessible(true);
Map map = (Map) annotationValues.get(invocationHandler);
consumer.accept(map);
}
}
基础数据类
package com.alibaba.easyexcel.test.demo.read;
import java.util.Date;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
/**
* 基础数据类
*
* @author root
**/
@Getter
@Setter
@EqualsAndHashCode
public class DynamicData {
/**
* @ExcelProperty 可加可不加
*/
// @ExcelProperty("字符串标题")
private String string;
/**
* @ExcelProperty 可加可不加
*/
// @ExcelProperty("日期标题")
private Date date;
/**
* @ExcelProperty 可加可不加
*/
// @ExcelProperty("数字标题")
private Double doubleData;
}
模板的读取类
package com.alibaba.easyexcel.test.demo.read;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import java.util.List;
import java.util.Map;
/**
* 模板的读取类
*
* @author root
*/
@Slf4j
public class DynamicDataListener extends AnalysisEventListener<DynamicData> {
/**
* 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
private List<DynamicData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
@Override
public void invoke(DynamicData data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
cachedDataList.add(data);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
log.info("存储数据库成功!");
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
log.info("解析到一条表头数据:{}", JSON.toJSONString(headMap));
super.invokeHeadMap(headMap, context);
}
}