EasyExcel导入合并单元格数据

1.EasyExcel.read 方法

这里在read的时候要注意，增加这个extraRead(CellExtraTypeEnum.MERGE)来解析合并单元格的信息

java"> 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方法处理完合并单元格数据后，最后才得到完整的单元格数据

java">@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，要设置值

java">@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;
}

easyexcel导入合并单元格解析（纵向合并，横向合并都支持）_easyexcel解析合并单元格-CSDN博客