通过注解解决合并单元格数据获取问题
1.解决方案
通过类反射获取@ExcelProperty注解信息;
通过复写@extra方法获取合并单元格的左上坐标和右下坐标;
通过左上坐标获合并单元格存储数据信息;
通过遍历方法将相关数据补全;
2.实现代码
excel读取方法体
EasyExcel.readSheet(0).head(GdzcDto.class).headRowNumber(2).registerReadListener(new ReadListener<GdzcDto>(){
/**
* 单次缓存的数据量
*/
public static final int BATCH_COUNT = 100;
/**
*临时存储对象
*/
private List<GdzcDto> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 临时存储合并单元格
*/
private List<CellExtra> cellExtraList = new ArrayList<>();
/**
* 临时存储数据行的行号
*/
private List<Integer> rowList = new ArrayList();
/**
* 获取合并单元格信息
*/
@Override
public void extra(CellExtra extra, AnalysisContext context) {
switch (extra.getType()) {
case MERGE:
System.out.println(
"额外信息是合并单元格,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}"+"+"+
extra.getFirstRowIndex()+"+"+extra.getFirstColumnIndex()+"+"+extra.getLastRowIndex()+"+"+
extra.getLastColumnIndex());
cellExtraList.add(extra);
break;
default:
}
}
@Override
public void invoke(GdzcDto data, AnalysisContext context) {
// 获取行号
ReadRowHolder readRowHolder = context.readRowHolder();
Integer rowIndex = readRowHolder.getRowIndex();
if (data.getCompanyName() != null) {
rowList.add(rowIndex);
cachedDataList.add(data);
}
if (cachedDataList.size() >= BATCH_COUNT) {
saveData(EasyExcelUtil.fillData(cellExtraList,rowList,cachedDataList));
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
rowList.clear();
cellExtraList.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData(EasyExcelUtil.fillData(cellExtraList,rowList,cachedDataList));
}
/**
* 加上存储数据库
*/
private void saveData(List<U> list) {
}
}).build();
补充合并单元格
public static <T, U> Object fillData(List<CellExtra> cellExtraList, List<Integer> rowList, List<U> cachedDataList) {
//匹配
cellExtraList.forEach(e ->{
//判断是否为数据行
int i1 = rowList.indexOf(e.getFirstRowIndex());
if (i1 >= 0) {
// 获取列位置
Integer cellIndex = e.getFirstColumnIndex();
// 获取含有数据的对象
U management = cachedDataList.get(i1);
// 获取这个合并格的数据和字段名
try {
Map<String, Object> dataByClass = getDataByClass(management,cellIndex);
int i2 = e.getLastRowIndex() - e.getFirstRowIndex();
System.out.println("i2:"+i2);
for (int i = 1; i <= i2; i++) {
U management1 = cachedDataList.get(i1 + i);
Field field = management1.getClass().getDeclaredField(dataByClass.get("name").toString());
field.setAccessible(true);
field.set(management1,dataByClass.get("value").toString());
}
} catch (IllegalAccessException | NoSuchFieldException | InstantiationException ex) {
throw new RuntimeException(ex);
}
}
});
return dataList;
}
获取注解信息方法
/**
* 可以通用
* 获取合并格的数据和字段名
*/
private static <U> Map<String,Object> getDataByClass(U management, Integer cellIndex) throws IllegalAccessException, NoSuchFieldException, InstantiationException {
Map map = new HashMap();
Field[] declaredFields = management.getClass().getDeclaredFields();
findNull:for (int i = 0; i < declaredFields.length; i++) {
Field f = declaredFields[i];
f.setAccessible(true);
//使用FieldDemo类的class对象生成 实例
Object obj = management.getClass().newInstance();
//字段值空判定
if(!Objects.isNull(f.get(obj))){
System.out.println("空");
continue findNull;
}
// 获取easyexcel注解
ExcelProperty anno2 = f.getAnnotation(ExcelProperty.class);
// 获取 ExcelProperty 这个代理实例所持有的 InvocationHandler
InvocationHandler h = Proxy.getInvocationHandler(anno2);
Field memberValue = null;
// 获取 AnnotationInvocationHandler 的 memberValues 字段
memberValue = h.getClass().getDeclaredField("memberValues");
// 因为这个字段是 private final 修饰,所以要打开权限
memberValue.setAccessible(true);
Map o = null;
// 获取 memberValues
o = (Map) memberValue.get(h);
//获取注解值
int o1 = (int) o.get("index");
System.out.println("注解"+o1);
// 获取字段名
String name = f.getName();
System.out.println("名称"+name);
if(Objects.equals(cellIndex, o1)){
map.put("value",f.get(management));
map.put("name",name);
System.out.println("获取到"+map);
}
}
return map;
}