easyexcel解析跨多行的数据

在使用easyexcel解析excel文件的时候，存在某列横跨多行，那么存在解析出的对象的某些属性是没有值的，那么我们要怎么处理呢？代码如下

1. 定义实体对应excel文件

public class EtcParkingReconciliationDailyImportModel implements Serializable {

    /** 创建时间 */
    private String insertTime = LocalDateTime.now().toString();

    /** 名称 */
    @ExcelProperty(index = 0)
    private String name;

    /** 清分交易 */
    @ExcelProperty(index = 2)
    private String clearingTransaction;

    /** 正常交易 */
    @ExcelProperty(index = 3)
    private String normalTransaction;

    /** 确认记账交易 */
    @ExcelProperty(index = 4)
    private String acknowledgeTransactions;

    /** 确认不记账交易 */
    @ExcelProperty(index = 5)
    private String confirmUntransactions;

    @ExcelProperty(index = 1)
    private String projectName;

    /**
     * 解析清分时间
     */
    private String fillingTime;


    public String getProjectName() {
        return projectName;
    }

    public void setProjectName(String projectName) {
        this.projectName = projectName;
    }

    public String getFillingTime() {
        return fillingTime;
    }

    public void setFillingTime(String fillingTime) {
        this.fillingTime = fillingTime;
    }

    public EtcParkingReconciliationDailyImportModel() {
      
    }
    // Getter and Setter methods for insertTime


    public String getInsertTime() {
        return insertTime;
    }

    public void setInsertTime(String insertTime) {
        this.insertTime = insertTime;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getClearingTransaction() {
        return clearingTransaction;
    }

    public void setClearingTransaction(String clearingTransaction) {
        this.clearingTransaction = clearingTransaction;
    }

    public String getNormalTransaction() {
        return normalTransaction;
    }

    public void setNormalTransaction(String normalTransaction) {
        this.normalTransaction = normalTransaction;
    }

    public String getAcknowledgeTransactions() {
        return acknowledgeTransactions;
    }

    public void setAcknowledgeTransactions(String acknowledgeTransactions) {
        this.acknowledgeTransactions = acknowledgeTransactions;
    }

    public String getConfirmUntransactions() {
        return confirmUntransactions;
    }

    public void setConfirmUntransactions(String confirmUntransactions) {
        this.confirmUntransactions = confirmUntransactions;
    }

  
}

2. 创建Listener

public class EtcParkingReconciliationDailyExcelListener extends AnalysisEventListener<EtcParkingReconciliationDailyImportModel> {
   
	// 数据接收
    List<EtcParkingReconciliationDailyImportModel> dataList = Lists.newArrayList();
 	// 头行数量
    int headNum = 1;



    List<String> temp = new ArrayList<String>();

    @Override
    public void doAfterAllAnalysed(AnalysisContext arg0) {
        logger.info("EXCEL解析完成，共有数据:{}", dataList.size());
    }

    @Override
    public void invoke(EtcParkingReconciliationDailyImportModel model, AnalysisContext context) {
       // 业务处理
    }

  

    public List<EtcParkingReconciliationDailyImportModel> getDataList() {
        return dataList;
    }

 
	// 重点是这个，获取跨列，行的数据记录，后面在反射的时候会用到
    private List<CellExtra> cellExtraList = new ArrayList<>();

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

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

3. 定义方法解析跨列行的数据

/**
* excelDataList excel 解析出的数据
* cellExtraList 解析得到的跨行的数据
* headRowNum 头行数
*/
 private static void mergeExcelData(List<EtcParkingReconciliationDailyImportModel> 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 static void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List data) {
        EtcParkingReconciliationDailyImportModel object = (EtcParkingReconciliationDailyImportModel) 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) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

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

5. 调用代码

// 根据自己的业务修改代码
File file = new File("");
        EtcParkingReconciliationDailyExcelListener listener = new EtcParkingReconciliationDailyExcelListener();
        EasyExcel.read(file, EtcParkingReconciliationDailyImportModel.class, listener)
        // 重点需要添加CellExtraTypeEnum.MERGE
                .extraRead(CellExtraTypeEnum.MERGE)
                .sheet()
                .headRowNumber(1).doRead();
        List<EtcParkingReconciliationDailyImportModel> dataList = listener.getDataList();
        // 调用
        mergeExcelData(dataList,listener.getCellExtraList(),3);
        dataList.forEach(System.out::println);