以下代码可以用来处理带有合并单元格的数据导入
import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellExtra;
import lombok.extern.slf4j.Slf4j;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
@Slf4j
public class ImportExcelListener<T> extends AnalysisEventListener<T> {
private final List<T> data = new ArrayList<>();
private final Map<String, List<T>> dataMap = new HashMap<>();
private final Map<String, List<CellExtra>> mergeMap = new HashMap<>();
private final Integer headRowNumber;
public ImportExcelListener(Integer headRowNumber) {
this.headRowNumber = headRowNumber;
}
@Override
public void invoke(T data, AnalysisContext context) {
String sheetName = context.readSheetHolder().getSheetName();
dataMap.computeIfAbsent(sheetName, k -> new ArrayList<>());
dataMap.get(sheetName).add(data);
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
String sheetName = context.readSheetHolder().getSheetName();
switch (extra.getType()) {
case MERGE:
if (extra.getRowIndex() >= headRowNumber) {
mergeMap.computeIfAbsent(sheetName, k -> new ArrayList<>());
mergeMap.get(sheetName).add(extra);
}
break;
case COMMENT:
case HYPERLINK:
default:
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("Excel数据解析完成!");
}
public List<T> getData(InputStream in, Class<T> clazz) {
try {
EasyExcel.read(in, clazz, this)
.extraRead(CellExtraTypeEnum.MERGE)
.headRowNumber(headRowNumber)
.doReadAll();
} catch (Exception e) {
log.error("Excel读取异常:" + e);
}
convertDataMapToData();
return data;
}
private void convertDataMapToData() {
Iterator<Map.Entry<String, List<T>>> iterator = dataMap.entrySet().iterator();
while (iterator.hasNext()) {
Map.Entry<String, List<T>> next = iterator.next();
String sheetName = next.getKey();
List<T> list = next.getValue();
List<CellExtra> mergeList = mergeMap.get(sheetName);
if (CollUtil.isNotEmpty(mergeList)) {
list = explainMergeData(list, mergeList);
}
data.addAll(list);
}
}
private List<T> explainMergeData(List<T> list, List<CellExtra> mergeList) {
mergeList.forEach(item -> {
Integer firstRowIndex = item.getFirstRowIndex() - headRowNumber;
Integer lastRowIndex = item.getLastRowIndex() - headRowNumber;
Integer firstColumnIndex = item.getFirstColumnIndex();
Integer lastColumnIndex = item.getLastColumnIndex();
Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, list);
for (int i = firstRowIndex; i <= lastRowIndex; i++) {
for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
setInitValueToList(initValue, i, j, list);
}
}
});
return list;
}
private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> list) {
Object filedValue = null;
T object = list.get(firstRowIndex);
for (Field field : object.getClass().getDeclaredFields()) {
field.setAccessible(true);
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
if (annotation.index() == firstColumnIndex) {
try {
filedValue = field.get(object);
break;
} catch (IllegalAccessException e) {
log.error("获取合并单元格的初始值异常:" + e.getMessage());
}
}
}
}
return filedValue;
}
public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> list) {
T object = list.get(rowIndex);
for (Field field : object.getClass().getDeclaredFields()) {
field.setAccessible(true);
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
if (annotation.index() == columnIndex) {
try {
field.set(object, filedValue);
break;
} catch (IllegalAccessException e) {
log.error("设置合并单元格的值异常:" + e.getMessage());
}
}
}
}
}
}