实例数据
1.监听器:
@Slf4j
public class CustomAnalysisEventListener<T> extends AnalysisEventListener<T> {
private int headRowNum;
public CustomAnalysisEventListener(int headRowNum) {
this.headRowNum = headRowNum;
}
private List<T> list = new ArrayList<>();
private List<CellExtra> cellExtraList = new ArrayList<>();
@Override
public void invoke(T excelData, AnalysisContext analysisContext) {
// log.info(" data -> {}", excelData);
list.add(excelData);
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
CellExtraTypeEnum type = extra.getType();
switch (type) {
case MERGE: {
if (extra.getRowIndex() >= headRowNum) {
cellExtraList.add(extra);
}
break;
}
default:{
}
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
public List<T> getList() {
return list;
}
public List<CellExtra> getCellExtraList() {
return cellExtraList;
}
}
2.
@Slf4j
public class ExcelListener extends AnalysisEventListener {
/**
* 可以通过实例获取该值
*/
private List<Object> dataList = new ArrayList<>();
@Override
public void invoke(Object object, AnalysisContext context) {
//数据存储到list,供批量处理,或后续自己业务逻辑处理。
dataList.add(object);
handleBusinessLogic();
/*
//如数据过大,可以进行定量分批处理
if(dataList.size()>=200){
handleBusinessLogic();
dataList.clear();
}
*/
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//非必要语句,查看导入的数据
log.info("导入的数据条数为: " + dataList.size());
}
/**
* 根据业务自行实现该方法,例如将解析好的dataList存储到数据库中
*/
private void handleBusinessLogic() {
// TODO
}
public List<Object> getDataList() {
return dataList;
}
public void setDataList(List<Object> dataList) {
this.dataList = dataList;
}
}
3.
@Slf4j
@Component
public class ExcelRead {
private static final int HEAD_ROW_NUM = 1;
public static <T> List<T> list(MultipartFile file, Class<T> tClass) throws IOException {
List<T> excelDataList;
CustomAnalysisEventListener listener = new CustomAnalysisEventListener(HEAD_ROW_NUM);
EasyExcel.read(file.getInputStream(), tClass, listener).extraRead(CellExtraTypeEnum.MERGE).sheet().doRead();
excelDataList = listener.getList();
List<CellExtra> cellExtraList = listener.getCellExtraList();
if (cellExtraList != null && cellExtraList.size() > 0) {
mergeExcelData(excelDataList, cellExtraList, HEAD_ROW_NUM);
}
return excelDataList;
}
private static <T> void mergeExcelData(List<T> 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 <T> void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {
Object 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 static <T> Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {
Object filedValue = null;
Object 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;
}
}
4.调用示例
list = ExcelRead.list(file, OilStationImportDTO.class);
5.实体类
标签:读取,dataList,excel,void,List,private,int,easy,public From: https://www.cnblogs.com/zhuoneng/p/17732380.html