1.EasyExcel.read 方法
这里在read的时候要注意，增加这个extraRead(CellExtraTypeEnum.MERGE)来解析合并单元格的信息
ImportListener listener = new ImportListener();
try {
EasyExcel.read(file.getInputStream(), ImportDto.class, listener)
.headRowNumber(1)
.extraRead(CellExtraTypeEnum.MERGE)
.ignoreEmptyRow(true)
.sheet()
.doRead();
} catch (IOException e) {
log.error("importAccountExcel-----EasyExcel.read-----error：", e);
throw new Exception(ResponseEnum.FAILURE.getCode(), e.getMessage());
}
List<ImportDto> dataList = listener.getDataList();
if (CollectionUtils.isEmpty(dataList)) {
throw new Exception(ResponseEnum.FAILURE.getCode(), "导入文件失败，获取数据为空");
}2.Listener解析方法
在invok解析完所有数据以后，先将所有解析的数据存储在tempDataList中，此时合并单元格是没数据的。
然后经过convertDataMapToData方法处理完合并单元格数据后，最后才得到完整的单元格数据
@Getter
@Slf4j
public class ImportListener implements ReadListener<ImportDto> {
private final static String[] excelTitle = {"日期", "姓名", "银行账号", "开户行", "证件号", "电话号码", "金额", "期数"};
private final List<ImportDto> dataList = ListUtils.newArrayList();
private final List<String> excelTitleList = Arrays.asList(excelTitle);
/**
* 正文起始行
*/
private Integer headRowNumber = 1;
/**
* 解析的临时数据
*/
private List<ImportDto> tempDataList = new ArrayList<>();
/**
* 合并单元格数据
*/
private List<CellExtra> mergeList = new ArrayList<>();
@Override
public void invoke(ImportDto data, AnalysisContext context) {
// log.info("解析到一条数据:{}", JSON.toJSONString(data));
checkData(data, context);
// dataList.add(data);
tempDataList.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("导入文件-所有数据记录：{}", JSON.toJSONString(dataList));
// 在invok解析完数据以后，才去处理合并单元格数据，最后得到合并完以后的数据
convertDataMapToData();
log.info("导入文件-所有数据解析完成！");
}
@Override
public void onException(Exception exception, AnalysisContext context) {
log.info("解析失败，但是继续解析下一行:{}", exception.getMessage());
// 如果是某一个单元格的转换异常 能获取到具体行号
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException dataException = (ExcelDataConvertException) exception;
String errData = CellDataTypeEnum.STRING.equals(
dataException.getCellData().getType()) ? dataException.getCellData()
.getStringValue() : dataException.getCellData().getNumberValue().toPlainString();
log.warn("第{}行，第{}列解析异常，数据为:{}", dataException.getRowIndex(), dataException.getColumnIndex(),
errData);
String errorMsg = "第" +
dataException.getRowIndex() +
"行，第" +
dataException.getColumnIndex() +
"列解析异常，数据为:" +
errData;
throw new BaseException(ResponseEnum.FAILURE.getCode(), errorMsg);
} else {
log.error("文件校验失败:", exception);
if (exception instanceof BaseException) {
BaseException base = (BaseException) exception;
if (StringUtils.isNotBlank(base.getErrorMsg())) {
throw new BaseException(ResponseEnum.FAILURE.getCode(), base.getErrorMsg());
}
throw new BaseException(ResponseEnum.FAILURE.getCode(), "获取异常出错");
} else {
throw new BaseException(ResponseEnum.FAILURE.getCode(), "获取异常出错");
}
}
}
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
if (CollectionUtils.isEmpty(headMap)) {
throw new BaseException(ResponseEnum.FAILURE.getCode(), "excel表头不能为空");
}
headMap.forEach((i, readCellData) -> {
readCellData.checkEmpty();
if (CellDataTypeEnum.EMPTY.equals(readCellData.getType())) {
return;
}
if (!excelTitleList.contains(readCellData.getStringValue())) {
throw new BaseException(ResponseEnum.FAILURE.getCode(), "第" + (i + 1) + "列Excel文件表头有误");
}
});
}
//上面写入的CellExtraTypeEnum.MERGE就会来调用这个方法
@Override
public void extra(CellExtra extra, AnalysisContext context) {
switch (extra.getType()) {
case MERGE: // 额外信息是合并单元格
if (extra.getRowIndex() >= headRowNumber) {
mergeList.add(extra); // 保存合并单元格信息
}
break;
case COMMENT:
case HYPERLINK:
default:
}
}
/**
* 将具有多个sheet数据的dataMap转变成一个data
*/
private void convertDataMapToData() {
if (!CollectionUtils.isEmpty(mergeList)) {
tempDataList = explainMergeData(tempDataList, mergeList);
}
dataList.addAll(tempDataList);
}
/**
* 处理有合并单元格的数据
*
* @param list 解析的sheet数据
* @param mergeList 合并单元格信息
* @return 填充好的解析数据
*/
private List<ImportDto> explainMergeData(List<ImportDto> list, List<CellExtra> mergeList) {
// 循环所有合并单元格信息
for (CellExtra item : mergeList) {
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;
}
/**
* 获取合并单元格的初始值
* rowIndex对应list的索引
* columnIndex对应实体内的字段
*
* @param firstRowIndex 起始行
* @param firstColumnIndex 起始列
* @param list 列数据
* @return 初始值
*/
private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<ImportDto> list) {
Object filedValue = null;
ImportDto 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;
}
/**
* 设置合并单元格的值
*
* @param filedValue 值
* @param rowIndex 行
* @param columnIndex 列
* @param list 解析数据
*/
public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<ImportDto> list) {
ImportDto 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());
}
}
}
}
}
}3.导入的DTO，这里要注意index，要设置值
@Data
public class ImportDto implements Serializable {
@ExcelProperty(value = "日期", converter = CustomDateConverter.class, index = 0)
private Date putoutDate;
@ExcelProperty(value ="姓名", index = 1)
private String name;
@ExcelProperty(value = "银行账号", index = 2)
private String bankNo;
@ExcelProperty(value = "开户行", index = 3)
private String bankAddress;
@ExcelProperty(value = "证件号", index = 4)
private String idNum;
@ExcelProperty(value = "电话号码", index = 5)
private String cellPhone;
@ExcelProperty(value = "金额", index = 6)
private BigDecimal limit;
}