【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("存储数据库成功!");
    }
}

  • 4
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 4
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序猿七度

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值