easyexcel读取excel合并单元格数据-读取完成-自动补全

普通的excel列表，easyexcel读取是没有什么问题的。但是，如果有合并单元格，那么它读取的时候，能获取数据，但是数据是不完整的

 像这个，读取的第一行的”一级菜单“有值"A页面”，第二行的”一级菜单“就是null，被合并的表格只有左上角是有值的，需要手动补全

Dto

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@EqualsAndHashCode
public class ImportPermissionTableDto {

     //1、实体需要增加注解索引值：
    @ExcelProperty(value = "一级菜单",index = 0)
    private String 一级菜单;
    @ExcelProperty(value = "二级菜单",index = 1)
    private String 二级菜单;
    @ExcelProperty(value = "三级菜单",index = 2)
    private String 三级菜单;
}

 自定义读取监听器

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 com.xd.asset.admin.v2soft.dto.ImportPermissionTableDto;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class AdminPermissionImportTableAnalysisEventListener extends AnalysisEventListener<ImportPermissionTableDto> {


    public AdminPermissionImportTableAnalysisEventListener() {
    }

    private List<ImportPermissionTableDto> list = new ArrayList<>();

    private List<CellExtra> cellExtraList = new ArrayList<>();

    @Override
    public void invoke(ImportPermissionTableDto excelData, AnalysisContext analysisContext) {
        log.info(" data -> {}", excelData);
        list.add(excelData);
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        log.info(" extra -> {}", extra);
        CellExtraTypeEnum type = extra.getType();
        switch (type) {
            case MERGE: {
                if (extra.getRowIndex() >= HEAD_ROW_NUM) {
                    cellExtraList.add(extra);
                }
                break;
            }
            default:{
            }
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info(" doAfterAllAnalysed");

        //读取完成 填充合并过的单元格
        if (cellExtraList != null && cellExtraList.size() > 0) {
            mergeExcelData(list, cellExtraList, HEAD_ROW_NUM);
        }
    }

    public List<ImportPermissionTableDto> getList() {
        return list;
    }

    public List<CellExtra> getCellExtraList() {
        return cellExtraList;
    }



    private static final int HEAD_ROW_NUM = 1;

    private void mergeExcelData(List<ImportPermissionTableDto> excelDataList, List<CellExtra> cellExtraList, int headRowNum) {
        cellExtraList.forEach(cellExtra -> {
            int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNum;
            int lastRowIndex = cellExtra.getLastRowIndex() - headRowNum;
            int firstColumnIndex = cellExtra.getFirstColumnIndex();
            int lastColumnIndex = cellExtra.getLastColumnIndex();
            //获取初始值 合并单元格左上角的值
            Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, excelDataList);
            //设置值 把合并单元格左上角的值 设置到合并区域的每一个单元格
            for (int i = firstRowIndex; i <= lastRowIndex; i++) {
                for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
                    setInitValueToList(initValue, i, j, excelDataList);
                }
            }
        });
    }

    private void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<ImportPermissionTableDto> data) {
        ImportPermissionTableDto object = data.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());
                    }
                }
            }
        }
    }

    private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<ImportPermissionTableDto> data) {
        Object filedValue = null;
        ImportPermissionTableDto object = data.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;
    }
}

 使用

  @PostMapping("/importPermissionTableV1")
  @Transactional
  public List<ImportPermissionTableDto> importPermissionTableV1(@RequestParam("permissionTable") MultipartFile file) throws InvalidProtocolBufferException, ParseException {
    List<ImportPermissionTableDto> permissionList=new ArrayList<>();

    try {

      AdminPermissionImportTableAnalysisEventListener listener = new AdminPermissionImportTableAnalysisEventListener();
      EasyExcel.read(file.getInputStream(), ImportPermissionTableDto.class, listener).extraRead(CellExtraTypeEnum.MERGE).sheet().doRead();
      permissionList = listener.getList();

     //todo check-->do--->save--->result
    

    } catch (IOException e) {
      e.printStackTrace();
      throw new IllegalArgumentException(e.getMessage());
    }
    return permissionList;

  }
}