【EasyExcel】java通过easyExcel上传excel文件作异常处理

实体：（根据自己定的实体编写，加上注解@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("存储数据库成功！");
    }
}