在使用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 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;
}
}
- 创建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;
}
- 定义方法解析跨列行的数据
/**
* 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);
