框架描述
使用Java代码封装的一个的Excel多线程批量导出框架,使用EasyExcel完成excel文件方面的交互,重在整合功能,仅需少量代码就可以在自己的项目中快速引入该功能。借助EasyExcel支持的功能,框架可以分批流式处理写入数据到磁盘,导出文件存放在服务器一个临时路径,下载后删除,避免OOM;并且使用多线程提高数据处理速度,异步线程处理便于前端查看导出进度与处理结果。
框架代码采取了设计模式中的策略模式,只需要实现其中的策略类DataGetter
(分页获取数据),就可以快速使用导出功能,并且给出了前端的配套代码示例(vue)。
demo项目地址:https://github.com/destinyol/excel-multi-export-progress
(demo项目包括数据库sql文件及假数据生成SpringbootTest
,以及全套测试代码,可以直接部署运行)
框架文件结构:(ExcelStyle
文件可更改表格样式)
效果展示
可调参数
ExcelExportMainTool
类中有一些可以根据实际情况自行调整的参数
// BATCH_COUNT 和 BATCH_COUNT_QUERY其中小的那个决定了进度条的粒度public static final int BATCH_COUNT = 5000; // 批量处理,每批插入Excel中的数据行数,可根据情况自行优化更改,建议5000或10000行public static final int BATCH_COUNT_QUERY = 1000; // 多线程分页查询,每页的数据行数,可自行优化更改(若 BATCH_COUNT_QUERY > BATCH_COUNT,则不是多线程,因为EasyExcel分批多次插入不能多线程)public static final int SHEET_CUNT_NUM = 100000; // 控制大约多少条数据分一个sheet,建议10万行public static final String FILE_SAVE_PATH = ensureEndsWithFileSeparator(System.getProperty("java.io.tmpdir")); // 临时excel文件存放位置,可自定义,默认是系统临时文件夹public static final Boolean DEBUG_LOG_RUNNING_TIMES = false; // 打印导出运行时间,true:打印, false:不打印
在执行过程中:
-
如果配置的每批插入Excel中的数据量
BATCH_COUNT
大于分页查询的一页的数据量BATCH_COUNT_QUERY
,就会自动多线程分页查询,直到积累到BATCH_COUNT
的量才会一次性批量插入Excel文件 -
如果
BATCH_COUNT
<BATCH_COUNT_QUERY
,就会一次性查询分页数据,然后循环EasyExcel写入文件。因为EasyExcel不支持多线程写入文件,所以就算放到多线程中,也要额外加锁,增加任务执行的开销,没必要。
性能测试
测试导出15万行数据,用时3.2s,文件大小7.8mb
测试导出100万行数据,用时18s,文件大小52mb
可以看到数据按照配置文件中的每10万行分了一个sheet,一共10个
-- 测试用例中DataGetter里的分页获取数据sql,作为导出速度的参考
select tt.serial_number,tt.user_name,tt.depart_name,tt.project_name,tt.project_code,tt.remarks,tt.occur_time,tt.amount
from td_travel ttwhere tt.id > #{offset}limit #{pageSize}
测试导出是本地自己的笔记本跑的服务,数据库是mysql,这个应该是测试的比较理想的情况,用了自增的主键id索引来分页,并且是单表查询,如果简单的用 limit #{offset},#{pageSize}
来分页,数据量大了之后深分页速度可想而知,但这是sql优化方面了,和本文关系不大。结论是实际用时和实现的分页获取处理数据的DataGetter
关系较大,也和磁盘性能cpu性能有关。
参数配置中BATCH_COUNT_QUERY
不建议太小,查询分的太散,整体性能就会降低,当然数据量小的话影响不大,所以还是要根据实际业务场景去考虑分析,在进度条刷新的粒度和整体性能之间权衡一下。
使用方法
1.创建数据实体类
首先创建一个EasyExcel的导出数据实体类,参考他的官网案例
@Getter
@Setter
@EqualsAndHashCode
public class DemoData {@ColumnWidth(15) // excel对应的列宽@ExcelProperty("编号") // excel对应的列名private String serialNumber;@ColumnWidth(8)@ExcelProperty("报销人")private String userName;@ColumnWidth(10)@ExcelProperty("部门")private String departName;// .................../*** 忽略这个字段*/@ExcelIgnoreprivate String ignore;
}
2.实现DataGetter接口
/*** 分页获取数据策略类*/
public interface DataGetter {/*** 分页读取数据(需实现)* @param pageSize 分页大小* @param pageNum 页码(从1开始: 1、2、3、4、5 ......)* @param sqlFilterClass 用户传入的用于筛选的类,没传则为null(每次回调该函数,该类都会初始化为一开始传入的样子)* @return*/public List<Object> readData(Integer pageSize,Integer pageNum, Object sqlFilterClass);/*** 分页数据总数(需实现)* @param sqlFilterClass 用户传入的用于筛选的类,没传则为null* @return*/public Long countDataTotal(Object sqlFilterClass);}
3.直接使用
// --------------------------启动阶段--------------------------
// 实例化自己的getter,具体怎么创建完全自定义,只需要实现接口功能即可
TravelDataGetter dataGetter = new TravelDataGetter(travelMapper);
// 启动异步任务,返回进度条结果ExportProgress,返回给前端(包含最终文件名和进度查询processKey)
ExportProgress progress = ExcelExportMainTool.build(TravelExpenseExtraInfo.class, dataGetter, redisTemplate) // 传入数据实体类.class,实例化getter、redisTemplate.setSheetName("费用统计") // sheet名,超过10万行之后的自动变:费用统计1、费用统计2......,不设置则默认“sheet1”.runAsync(travelSearchDto); // 查询传参实体类,会传入DataGetter的方法中
// --------------------------查询进度--------------------------
// 静态方法,传入redisTemplate实例和processKey,返回ExportProgress
ExportProgress progress = ExcelExportMainTool.getProgress(redisTemplate, processKey);
// --------------------------下载文件--------------------------
// 待进度状态已完成后,传入文件名调用该方法下载文件(到这一步可自定义自己的下载方法和逻辑,文件就放在配置文件中的目录下)
// 默认提供的方法,下载完成后自动删除磁盘上该文件
// 流式下载,用HttpServletResponse httpResponse的.getOutputStream()传输数据
ExcelExportMainTool.downloadExcel(httpResponse,fileName);
请求接口示例:
@Autowiredprivate RedisTemplate redisTemplate;@Autowiredprivate TravelMapper travelMapper;/*** 导出费用明细统计excel*/@PostMapping("exportExpenseExtraInfo")public Response startExportTravelExpenseExtraInfo(@RequestBody TravelSearchDto travelSearchDto){Response response = new Response();response.ret(0,"成功了");try {TravelDataGetter dataGetter = new TravelDataGetter(travelMapper);ExportProgress res = ExcelExportMainTool.build(TravelExpenseExtraInfo.class, dataGetter, redisTemplate).setSheetName("费用统计").runAsync(travelSearchDto);response.setData(res);} catch (Exception e) {e.printStackTrace();response.ret(111000,"出错了");}return response;}/*** 获取导出费用明细统计excel进度*/@GetMapping("getExportExpenseExtraInfoProgress")public Response getExportTravelExpenseExtraInfoProgress(String processKey){Response response = new Response();response.ret(0,"成功了");try {ExportProgress progress = ExcelExportMainTool.getProgress(redisTemplate, processKey);response.setData(progress);} catch (Exception e) {e.printStackTrace();response.ret(111000,"出错了");}return response;}/*** 下载导出的excel文件*/@GetMapping("downloadExportExcel")public Response downloadExportTravelExpenseExtraInfoExcel(HttpServletResponse httpResponse, String fileName){Response response = new Response();response.ret(0,"成功了");try {ExcelExportMainTool.downloadExcel(httpResponse,fileName);} catch (Exception e) {e.printStackTrace();response.ret(111000,"出错了");}return response;}
API文档
// ExcelExportMainTool 类,要用的方法都在这个类底下/*** 主要的run方法-异步启动* @return 返回存有进度key的ExportProgress,可返回给前端*/public ExportProgress runAsync();/*** 主要的run方法-异步启动* @param sqlFilterClass 执行sql筛选项,会传入DataGetter方法中* @return 返回存有进度key的ExportProgress,可返回给前端*/public ExportProgress runAsync(Object sqlFilterClass);/*** 主要的run方法-同步启动* 以文件流的形式写入HttpServletResponse.getOutputStream()*/public void runSync(HttpServletResponse response);/*** 主要的run方法-同步启动* 以文件流的形式写入HttpServletResponse.getOutputStream()* @param sqlFilterClass 执行sql筛选项,会传入DataGetter方法中*/public void runSync(HttpServletResponse response,Object sqlFilterClass);/*** 获取导出进度(静态方法)* @param redisTemplate 启动导出时候的那个redisTemplate* @param processKey 进度key* @return ImportProgress*/public static ExportProgress getProgress(RedisTemplate redisTemplate, String processKey);/*** 下载异步导出的excel文件,下载完成后自动删除* @param fileName 返回给前端的文件名* @return*/public static void downloadExcel(HttpServletResponse response, String fileName);/*** 设置sheet名* 不设置则默认 ”sheet1“* @param sheetName*/public ExcelExportMainTool setSheetName(String sheetName);