先看sql语句:查询全部数据时间14秒
分页查询10万数据时间1秒左右
连接点,分组都加了索引,没加索引可能要10来分钟,
sql详情:
对于百万级的数据如果一下查出来是会内存溢出的,所以需要分页查出来,分页放到excel表格中
直接上我写的代码:excel表头类就不沾出来了,每个人的都不一样
/**
* 导出excel
* */
@RequestMapping("/export")
@RequiresPermissions("b2b:contract:export")
public void export(HttpServletResponse response) throws IOException {
OutputStream outputStream = response.getOutputStream();
String time = new SimpleDateFormat("yyyy-MM-dd-hh-mm-ss").format(new Date());
//添加响应头信息
response.setHeader("Content-disposition", "attachment; filename=" + "contract"+time+".xlsx");
//设置类型
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//设置头
response.setHeader("Pragma", "No-cache");
//设置头
response.setHeader("Cache-Control", "no-cache");
//设置日期头
response.setDateHeader("Expires", 0);
//获取总数据量
Integer count = contractService.getCount(null);
//如果总数据量多余10万,分页导出
if(count>100000){
//每页多少个
int max=100000;
//必须放到循环外,否则会刷新流
ExcelWriter excelWriter=EasyExcel.write(outputStream).build();;
for (int i=0;i<(count/max)+1;i++){
List<ContractAndCompanyExcel> exportList = contractService.export(i *max,max);
WriteSheet writeSheet = EasyExcel.writerSheet(i, "合同" + (i + 1)).head(ContractAndCompanyExcel.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
excelWriter.write(exportList,writeSheet);
}
//刷新流
excelWriter.finish();
}else {
List<ContractAndCompanyExcel> exportList = contractService.export(null,null);
EasyExcel.write(outputStream,ContractAndCompanyExcel.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("合同").doWrite(exportList);
}
outputStream.flush();
response.getOutputStream().close();
}
实测:导出用时105秒左右
每个表10万条