使用CompletableFuture和EasyExcel实现多线程导出多sheet页记录降低耗时

最近优化一个导出功能，耗时比较长,下面实用CompletableFuture降低导出耗时。 导出记录如下

一、下面是实现代码

1、导出实体TestExportDTO

@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value = "Test信息导出", description = "Test信息导出")
@HeadRowHeight(24)
@ContentRowHeight(24)
public class TestExportDTO implements Serializable {

	@ColumnWidth(30)
	@ExcelProperty(value = "ID", index = 0)
	@ApiModelProperty(value = "ID")
	private Long id;

	@ColumnWidth(30)
	@ExcelProperty(value = "编码", index = 1)
	@ApiModelProperty(value = "编码")
	private String i18nSanctionCode;

	@ColumnWidth(30)
	@ExcelProperty(value = "名称", index = 2)
	@ApiModelProperty(value = "名称")
	private String projectName;
}

2、导出TestExportMapper

List<TestExportDTO> testExportCount();

List<TestExportDTO> testExportLimit(@Param("size") Integer size, @Param("pageSize") Integer pageSize);

3、导出TestExportMapper.xml

    <select id="testExportCount" resultType="com.houxiurong.TestExportDTO">
        select id, code, name
        from user
    </select>

    <select id="testExportLimit" resultType="com.houxiurong.TestExportDTO">
        select id, code, name
        from user
        limit #{size}, #{pageSize}
    </select>

4、定义个线程池

//定义个线程池
private final ThreadPoolExecutor threadPoolExecutor = new ThreadPoolExecutor(10, 10, 60L, TimeUnit.SECONDS,
	new LinkedBlockingQueue<Runnable>(2000), new ThreadFactory() {
	@Override
	public Thread newThread(Runnable r) {
		return new Thread(r, "CompletableFutureExport-" + r.hashCode());
	}
});

5、导出记录，方便处理，代码没有分Service

	@ApiOperation(value = "excelExport")
	@PostMapping("/excelExport")
	public String excelExport(HttpServletResponse response) throws LmsException {
		response.reset();
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		response.setCharacterEncoding(GlobalConstants.ENCODING_UTF8);
		try (OutputStream outputStream = response.getOutputStream()) {
			long start = System.currentTimeMillis();
			log.info("------------任务开始执行--------------{}", start);
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("UTF-8");

			// 防止文件名中文乱码
			String fileName = DataUtils.stringDateToDate(LocalDate.now().toString()) + "_多线程测试数据导出_" + System.currentTimeMillis();
			fileName = URLEncoder.encode(fileName, "UTF-8");
			response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

			//获取记录总数量
			List<TestExportDTO> testExportCount = testExportMapper.testExportCount();
			int count = testExportCount.size();
			//每页10个(如果要每个sheet写入10000)此处修改大小即可
			int pageSize = 10;
			//必须放到循环外,否则会刷新流
			ExcelWriter excelWriter = EasyExcel.write(outputStream).build();

			List<CompletableFuture> completableFutures = new ArrayList<>();
			for (int i = 0; i < (count / pageSize) + 1; i++) {
				int finalI = i;
				CompletableFuture<Void> completableFuture = CompletableFuture.runAsync(() -> {
					List<TestExportDTO> exportList = testExportMapper.testExportLimit(finalI * pageSize, pageSize);
					if (!CollectionUtils.isEmpty(exportList)) {
						WriteSheet writeSheet = EasyExcel.writerSheet(finalI, "数据记录" + (finalI + 1)).head(TestExportDTO.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
						 //加锁
                                                 synchronized (excelWriter) {
							excelWriter.write(exportList, writeSheet);
						}
					}
				}, threadPoolExecutor);
				completableFutures.add(completableFuture);
			}
			for (CompletableFuture completableFuture : completableFutures) {
				completableFuture.join();
			}
			//刷新流
			excelWriter.finish();
			log.info("------------任务执行结束,共消耗:{}ms--------------", (System.currentTimeMillis() - start));
			outputStream.flush();
			response.getOutputStream().close();
		} catch (IOException e) {
			log.info("导出异常", e);
		}
		return "导出结束";
	}

二、导出结果如下