easyexcel解析跨多行的数据
在使用easyexcel解析excel文件的时候，存在某列横跨多行，那么存在解析出的对象的某些属性是没有值的，那么我们要怎么处理呢？代码如下
- 定义实体对应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 insertTimepublic 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;}}
- 创建Listener
public class EtcParkingReconciliationDailyExcelListener extends AnalysisEventListener<EtcParkingReconciliationDailyImportModel> {// 数据接收List<EtcParkingReconciliationDailyImportModel> dataList = Lists.newArrayList();// 头行数量int headNum = 1;List<String> temp = new ArrayList<String>();@Overridepublic void doAfterAllAnalysed(AnalysisContext arg0) {logger.info("EXCEL解析完成，共有数据:{}", dataList.size());}@Overridepublic void invoke(EtcParkingReconciliationDailyImportModel model, AnalysisContext context) {// 业务处理}public List<EtcParkingReconciliationDailyImportModel> getDataList() {return dataList;}// 重点是这个，获取跨列，行的数据记录，后面在反射的时候会用到private List<CellExtra> cellExtraList = new ArrayList<>();@Overridepublic 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;}
- 定义方法解析跨列行的数据
/**
* 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;}
- 调用代码
// 根据自己的业务修改代码
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);