实体:(根据自己定的实体编写,加上注解@ExcelProperty("")即可)
@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
详细代码:(easyExcel的listeren监听)
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import java.util.*;
/**
* @Author 三十七度
**/
@Slf4j
public class UploadDataListener extends AnalysisEventListener {
/**
* 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
private List<ItemUploadDTO> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private ItemService itemService;
private Long analyzingId;
/**
* @param itemService
*/
public UploadDataListener(ItemService itemService, Long analyzingId) {
this.analyzingId = analyzingId;
this.itemService = itemService;
}
/**
* 验证上传数据是否完整
* @param o
* @param analysisContext
*/
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(o));
cachedDataList.add((ItemUploadDTO) o);
for (ItemUploadDTO itemUploadDTO : cachedDataList) {
if (Objects.isNull(itemUploadDTO.getAsin()) || Objects.isNull(itemUploadDTO.getSku())
|| Objects.isNull(itemUploadDTO.getMarket()) || Objects.isNull(itemUploadDTO.getPlatform())
|| Objects.isNull(itemUploadDTO.getCompetitor())){
throw new BusinessException(305,"The data is incomplete. Please check and upload again"); //TODO 还可以优化(指出哪行数据不完整)
}
}
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 检查表头是否正确(验证上传的表是否为模版)
* @param headMap
* @param context
*/
@Override
public void invokeHeadMap(Map headMap, AnalysisContext context) {
if (!headMap.get(0).equals("Item ID") || !headMap.get(1).equals("SKU")
|| !headMap.get(2).equals("Platform") || !headMap.get(3).equals("Market") || !headMap.get(4).equals("IS Competitor")){
throw new BusinessException(300,"The uploaded file is incorrect. Please upload it again");
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
List<Item> list = BeanUtils.copyList(cachedDataList, m->{
Item item = BeanUtils.copyProperties(m, Item.class);
item.setAccountId(UserContext.get().getAccountId());
item.setAnalyzingId(this.analyzingId);
item.setCompetitor(m.getCompetitor().equals("Y")? 1 : 0);
return item;
});
itemService.batchSave(list);
log.info("存储数据库成功!");
}
}
2.(service层)
public Boolean importItems(MultipartFile file, Long analyzingId) throws IOException {
// 得到上传的文件名包括后缀
String fileNameTemp = file.getOriginalFilename();
System.out.println(fileNameTemp);
// 判断文件是否是excel文件 // TODO excel?
if(!fileNameTemp.endsWith("xls") && !fileNameTemp.endsWith("xlsx")){
throw new BusinessException(303,"The uploaded file format is incorrect");
}
//
EasyExcel.read(file.getInputStream(), ItemUploadDTO.class, new UploadDataListener(vocItemService, analyzingId)).sheet().doRead();
return Boolean.TRUE;
}
最新异常处理
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.read.metadata.holder.ReadRowHolder;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import java.util.*;
/**
* @Author 三十七度
**/
@Slf4j
public class UploadDataListener extends AnalysisEventListener {
/**
* 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
private List<ItemUploadDTO> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private itemService itemService;
private Long analyzingId;
private List failedMsg = new ArrayList();
/**
* @param itemService
*/
public UploadDataListener(itemService itemService, Long analyzingId) {
this.analyzingId = analyzingId;
this.itemService = itemService;
}
/**
* 验证上传数据是否完整
* @param o
* @param analysisContext
*/
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
// 获取行号
ReadRowHolder readRowHolder = analysisContext.readRowHolder();
Integer rowIndex = readRowHolder.getRowIndex();
log.info("解析到一条数据:{}", JSON.toJSONString(o));
cachedDataList.add((ItemUploadDTO) o);
// 匹配标题头
cachedDataList.forEach(itemUploadDTO -> {
if (Objects.isNull(itemUploadDTO.getAsin()) || Objects.isNull(itemUploadDTO.getSku())
|| Objects.isNull(itemUploadDTO.getMarket()) || Objects.isNull(itemUploadDTO.getPlatform())
|| Objects.isNull(itemUploadDTO.getCompetitor())){
// 因为标题占了一行 所以这里failedMsg位置+1
failedMsg.add(rowIndex+1);
}
});
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 检查表头是否正确(验证上传的表是否为模版)
* @param headMap
* @param context
*/
@Override
public void invokeHeadMap(Map headMap, AnalysisContext context) {
// 表头前五个标题相同,后面有别的标题
if ("5".equals(headMap.size())){
throw new BusinessException(300,"The uploaded file is incorrect. Please upload it again");
}
// 匹配标题信息一一对应
if (!headMap.get(0).equals("Item ID") || !headMap.get(1).equals("SKU")
|| !headMap.get(2).equals("Platform") || !headMap.get(3).equals("Market") || !headMap.get(4).equals("IS Competitor")){
throw new BusinessException(300,"The uploaded file is incorrect. Please upload it again");
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 判断是否存在不完整数据(去除重复不完整数据行数)
HashSet<Object> hashSet = new HashSet<>(failedMsg);
failedMsg.clear();
failedMsg.addAll(hashSet);
if (!Objects.isNull(failedMsg)){
throw new BusinessException(305, "The data in line "+ failedMsg +" is incomplete,Please check and upload again.");
}
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
List<Item> list = BeanUtils.copyList(cachedDataList, m->{
Item item = BeanUtils.copyProperties(m, Item.class);
item.setAccountId(UserContext.get().getAccountId());
item.setAnalyzingId(this.analyzingId);
item.setCompetitor(m.getCompetitor().equals("Y")? 1 : 0);
return item;
});
itemService.batchSave(list);
log.info("存储数据库成功!");
}
}