多线程、分sheet实现100w数据导出

实际上，还是要将这100w的数据加载到内存中的。

导入EasyExcel的依赖：

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>3.2.0</version>
		</dependency>

造数据

测试表：

CREATE TABLE `student` (
	`id` BIGINT NOT NULL COMMENT '主键id',
	`stu_name` VARCHAR ( 25 ) CHARACTER 
	SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '学生名',
	`stu_score` TINYINT DEFAULT NULL COMMENT '分数',
	`stu_number` VARCHAR ( 32 ) CHARACTER 
	SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '学号',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

生成中文名字：

    /**
     * 生成中文名称
     * @return
     */
    public static String generateChineseName() {
        //百家姓
        String xing = "王李张刘陈杨黄吴赵周徐孙马朱胡林郭何高罗郑梁谢宋唐许邓冯韩曹曾彭萧蔡潘田董袁于余叶蒋杜苏魏程吕丁沈任姚卢傅钟姜崔谭廖范汪陆金石戴贾韦夏邱方侯邹熊孟秦白江阎薛尹段雷黎史龙陶贺顾毛郝龚邵万钱严赖覃洪武莫孔";
        String ming = "君豪子华龙鑫家娜媞火文喜英税金土木平小明伟洛阳洋羊威微觉峰凤封丰枫叶烨葉官荣蓉绒榕林锦娟卫东婵权冬玲桌富成国强韶昭肇铝海雄熊寿裕鱼雨三房车巨";
        Random random = new Random();
        StringBuilder builder = new StringBuilder();
        builder.append(xing.charAt(random.nextInt(xing.length())));
        builder.append(ming.charAt(random.nextInt(ming.length())));
        if (Math.random() > 0.5) {
            builder.append(ming.charAt(random.nextInt(ming.length())));
        }
        return builder.toString();
    }

造100w的数据

    /**
     * 造100w数据
     */
    @PostMapping("/create")
    public void createData() {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
        String date = LocalDateTime.now(ZoneOffset.of("+8")).format(formatter);

        List<Student> list = new LinkedList<>();
        for (int i = 0; i <= 1000000; i++) {

            Student student = new Student().setName(generateChineseName())
                    .setNumber(date + String.format("%0" + 8 + "d", i))
                    .setScore((int) (Math.random() * 101));
            list.add(student);

            if (list.size() == 10000) {
                studentService.saveBatch(list);
                list.clear();
            }
        }
    }

导出

StudentVo类

@Getter
@Setter
@EqualsAndHashCode
public class StudentVo {
    @ExcelProperty("学号")
    private String number;
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("分数")
    private Integer score;
}

@RestController
@Slf4j
public class Test100wDataExportController {

    @Resource
    private StudentService studentService;
    @Resource(name = "myThreadPool")
    private AsyncTaskExecutor executor;


    /**
     * 多线程 分sheet 导出百万数据
     * @param response
     * @throws IOException
     */
    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        long start = System.currentTimeMillis();

        log.info("开始进入导出方法。。");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=down_" + URLEncoder.encode(System.currentTimeMillis() + ".xlsx", StandardCharsets.UTF_8));
        OutputStream outputStream = response.getOutputStream();
        try {
            long count = studentService.count();
            long size = 10000;
            long pages = count/size;

            CountDownLatch countDownLatch = new CountDownLatch((int) pages);
            HashMap<Integer, List<StudentVo>> hashmap = new HashMap<>();

            for (int i = 0; i < pages; i++) {
                int finalI = i;
                CompletableFuture.runAsync(() -> {
                        Page<Student> page = new Page<>();
                        page.setSize(size);
                        page.setCurrent(finalI + 1);
                        Page<Student> studentPage = studentService.page(page, null);
                        List<Student> records = studentPage.getRecords();
                        List<StudentVo> copyToList = BeanUtil.copyToList(records, StudentVo.class);
                        hashmap.put(finalI + 1, copyToList);
                        countDownLatch.countDown();
                }, executor).join();

            }

            countDownLatch.await();

            ExcelWriter excelWriter = EasyExcel.write(outputStream, StudentVo.class).build();
            for (Map.Entry<Integer, List<StudentVo>> entry : hashmap.entrySet()) {
                Integer key = entry.getKey();
                List<StudentVo> studentVos = entry.getValue();
                WriteSheet writeSheet = EasyExcel.writerSheet(key, "学生成绩单-" + key).build();
                excelWriter.write(studentVos, writeSheet);
            }

            //刷新流
            excelWriter.finish();
            outputStream.close();

            log.info("导出完成，用时：{}s", (System.currentTimeMillis() - start) / 1000);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}