用EasyExcel在Spring Boot中高效处理百万级大数据量的Excel导出
最编程
2024-02-08 14:36:12
...
@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException {
log.info("*********导出开始！**************");
String fileName ="downloadBig";
OutputStream outputStream =null;
//模拟的分页查询数据
List<URLModel> data =new ArrayList<>();
for (int i = 0; i < 20; i++) {
URLModel urlModel=new URLModel();
urlModel.setUrl("url"+i);
urlModel.setName("setName"+i);
urlModel.setWebCategoryName("setWebCategoryName"+i);
data.add(urlModel);
}
try {
int totalCount=10000;
//每一个Sheet存放100w条数据
int sheetDataRows = 100;
//每次写入的数据量20w,每页查询20W
int writeDataRows = 20;
//计算需要的Sheet数量
Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
//计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
//计算最后一个sheet需要写入的次数
Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));
outputStream = response.getOutputStream();
//必须放到循环外，否则会刷新流
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
//开始分批查询分次写入
for (int i = 0; i < sheetNum; i++) {
//创建Sheet
WriteSheet sheet = new WriteSheet();
sheet.setSheetName("Sheet"+i);
sheet.setSheetNo(i);
//循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(URLModel.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
excelWriter.write(data, writeSheet);
}
}
// 下载EXCEL，返回给前段stream流
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
excelWriter.finish();
outputStream.flush();
outputStream.close();
log.info("*********导出结束！**************");
} catch (Exception e) {
e.printStackTrace();
}finally {
if (outputStream != null) {
outputStream.close();
}
}