springboot十万级Excel数据导入数据库

阿里出品，必属精品！网上的excel工具类用了一圈，老是报内存溢出。百度过一下原因后，发现果然是代码的问题，果断用上阿里的easyexcel这个插件，再加上多线程一跑，效果刚刚的，10万的数据大概用了100秒左右，虽然比不上大佬写的，但比我原来要好太多太多了。

            com.alibaba
            easyexcel
            2.1.6

首先附上这个插件的依赖。

public class SupplierExcelListener extends AnalysisEventListener {
//count,List这两个静态变量必不可少
    public static int count;
    private SupplierService supplierService;
    public static List supplierList = new ArrayList();

    public SupplierExcelListener(SupplierService supplierService) {
        this.supplierService = supplierService;
    }

    @Override
    public void invoke(Supplier supplier, AnalysisContext analysisContext) {
        supplierList.add(supplier);
        //count每读取一行excel数据，count+1
        count++;
        //数据总行数
        Integer a = analysisContext.getTotalCount()-1;
        if(a == count){
            try {
            //这个是我写得多线程跑的，稍后会附代码
                supplierService.ThreadSupplier(supplierList);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("supplier excel读取完毕");
    }
}

这个才是重头戏，这个加上了就不会爆内存了，其实爆内存大多要检查下代码的问题，是不是内存占用过多了。

    @Override
    public void ThreadSupplier(List suppliers) throws InterruptedException {
        //每个线程处理的数据量100
        int count = 100;
        //数据集合大小
        int listSize = suppliers.size();
        //开启的线程数
        int runSize = (listSize/count)+1;
        //存放每个线程的执行数据
        List newlist = null;
        Integer sum = 0;
        //线程池
        ExecutorService executor = Executors.newFixedThreadPool(runSize);
        CountDownLatch begin = new CountDownLatch(1);
        CountDownLatch end = new CountDownLatch(runSize);
        //循环创建线程
        for(int i=0;i 
这个是多线程的service实现层impl，这里照着写就行没啥问题
 
public class SupplierThread implements Runnable{

    SupplierMapper supplierMapper;
    private List supplierList;
    private CountDownLatch begin;
    private CountDownLatch end;
    private Integer count = 0;

    public Integer getCount() {
        return count;
    }

    public void setCount(Integer count) {
        this.count = count;
    }

    public SupplierThread(List supplierList, CountDownLatch begin, CountDownLatch end, SupplierMapper supplierMapper){
        this.supplierList = supplierList;
        this.begin = begin;
        this.end = end;
        this.supplierMapper = supplierMapper;
    }

    @Override
    public void run() {
        try {
            for(Supplier supplier:supplierList){
                supplierMapper.insertSupplier(supplier);
            }
            count = 1;
            begin.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }finally {
            end.countDown();
        }
    }
}
 
//controller调用
@RestController
public class SupplierController {
    @Autowired
    SupplierService supplierService;

    @PostMapping("/addSupplier")
    public String importSupplier(MultipartFile file) throws IOException {
        InputStream in = file.getInputStream();
        supplierService.readSupplierExcel(in,supplierService);
        return "success";
    }

}
 
最后postman接口一调，成功！
 
参考了两篇大佬的文章：
 多线程处理Excel导入数据入库
 https://blog.csdn.net/weixin_42228950/article/details/105308077
 
SpringBoot项目中集成EasyExcel实现Excel文件上传至MySQL
 https://blog.csdn.net/riemann_/article/details/103639254?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_title~default-8.essearch_pc_relevant&spm=1001.2101.3001.4242