使用EasyExcel导出百万条数据

使用EasyExcel导出百万条数据

依赖:

<!--easyexcel依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

实例代码:

/**
     * 多线程分页查询，每页数据写入多个sheet
     */
    public void exportExcel4(HttpServletResponse response) throws IOException, InterruptedException {

        //查询数据库的总条数
        Integer count = salariesMapper.selectCount(null);
        //多少个sheet可以自定义
        Integer pages = 60;
        // 每个sheet的条数
        Integer size = count / pages;
        // 创建固定的线程数（多少个sheet就创建多少个线程）
        ExecutorService executorService = Executors.newFixedThreadPool(pages);
        CountDownLatch countDownLatch = new CountDownLatch(pages);

        Map<Integer, Page<Salaries>> pageMap = new HashMap<>();
        for (int i = 0; i < pages; i++) {
            int finalI = i;
            executorService.submit(new Runnable() {
                @Override
                public void run() {
                    Page<Salaries> page = new Page<>();
                    page.setCurrent(finalI + 1);
                    page.setSize(size);
                    Page<Salaries> selectPage = salariesMapper.selectPage(page, null);
                    pageMap.put(finalI, selectPage);
                    countDownLatch.countDown();
                }
            });
        }

        countDownLatch.await();
        //这是本地测试路径 , 服务器上面的需要更改路径
        String path = "E:\\Salaries.xls";
        ExcelWriter excelWriter = EasyExcel.write(path, Salaries.class).build();
        pageMap.forEach((key,value)-> {
            WriteSheet writeSheet = EasyExcel.writerSheet("Sheet" + key).build();
            excelWriter.write(value.getRecords(),writeSheet);
        });
        //关闭流很重要 , 一定要在循环外
        excelWriter.finish();
        }