目录
前言
以前就想记录下关于导出的东西，正好最近又遇到了数据量偏大，需要优化导出的一个项目，这里稍微记录下思路，以及一些垃圾代码~
poi导出问题
- 查询数据量过大，导致内存溢出或者时间耗时比较严重（通过多线程分批查询）
- 最后下载的时候excel转换的输出流内存溢出（可通过新版的SXSSFWorkbook解决，可通过其构造函数指定在内存中缓存的行数，剩余的会自动缓存在硬盘的临时目录上）
优化背景
项目中的导出实际上已经优化过一版了，原先就是最常见的poi导出方式，随着数据增大，上述1，2问题越来越明显，后来前人改过一版，通过缓存方式实现，即程序启动时异步导出所有数据放到服务器，当提交数据或修改数据时异步调整服务器上的文件，用户点击导出时直接从服务器上下载文件，可想而知速度还是很快的。
使用缓存简单方便，奈何该实现存在并发的情况，当该业务使用的人过多，会出现频繁修改该excel，而且每个线程修改时耗时也比较慢，导致线程阻塞，使得内存压力不断增大，最终内存溢出，程序直接gg
优化思路
- 当数据量很大或者查询逻辑很复杂导致最后的耗时一直很严重，那么可以调整导出的逻辑，后台提供三个接口，一个接口直接异步使用多线程进行导出，同时将导出进度存入redis，另外一个接口每3s循环调用查询导出进度，当进度为100时，直接下载即可
- 数据量不是特别大时，那么可以直接使用多线程进行excel读写导出
代码实现
代码贴的是第二种思路，写的很垃圾，权当记录，主旨就是通过多线程查询数据然后各自封装到poi中，其实也可以使用 fork-join ，以前写过一篇文章介绍：并发编程介绍
我们的场景是数据需要以月份进行分离，因此sheet页需要按月份进行排序，原先每个sheet页中的数据也需要按时间排序，后来与产品讨论为了节省时间去掉该逻辑，可在导出后通过excel自身的排序满足
/**
* 每个线程处理的普危货数量
*/
private static final int THREAD_THRESHOLD = 300;
/**
* 计数器
*/
private CountDownLatch countDownLatch;
/**
* sheet页锁对象
*/
private static Map<Sheet, Object> lockMap = new ConcurrentHashMap<>();
public void realTimeExport(HttpServletResponse response, Integer type) {
long startTime = System.currentTimeMillis();
List<String> monthList = orderPassApplyMapper.listMonthList(type);
if (CollectionUtils.isEmpty(monthList)) {
throw new AsocoSealingParkException("当前普危货无数据，无法导出");
}
Workbook workbook = null;
try {
// 首先创建sheet页，确保顺序
workbook = new SXSSFWorkbook(100);
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFillForegroundColor(IndexedColors.TAN.getIndex());
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
String sheetSuffix = type == 0 ? "危化无单" : "普货进园";
for (String month : monthList) {
Sheet sheet = workbook.createSheet(month + sheetSuffix);
// 创建标题行
Row row = sheet.createRow(0);
String[] title = new String[] {"卸货企业名称", "装货企业名称", "运输意向", "申请时间", "牵引车车牌", "挂车车牌", "司机姓名", "司机身份证号", "司机联系方式", "装货企业联系人", "装货企业联系方式", "卸货企业联系人",
"卸货企业联系方式", "申请备注", "申请进园时间开始", "申请进园时间截止", "允许入园时间开始", "允许入园时间截止", "企业审核时间", "企业审核意见", "企业审核人", "企业审核状态"};
for (int i =0; i< title.length; i++) {
sheet.setColumnWidth(i, (int) (35.7 * 200));
Cell cell0 = row.createCell(i);
cell0.setCellValue(title[i]);
cell0.setCellStyle(titleStyle);
}
}
Wrapper<OrderPassApplyDo> wrapper = new EntityWrapper<>();
wrapper.eq("type", type);
int total = orderPassApplyMapper.selectCount(wrapper);
// 确定线程数量
int threadNums = total % THREAD_THRESHOLD == 0 ? (total / THREAD_THRESHOLD) : (total / THREAD_THRESHOLD) + 1;
log.info("sheet页创建耗时:{}", (System.currentTimeMillis() - startTime));
long time = System.currentTimeMillis();
countDownLatch = new CountDownLatch(threadNums);
for (int i = 0; i < threadNums; i++) {
ThreadPoolManager.getInstance().getExecutorService().submit(new ExportTask(workbook, i * THREAD_THRESHOLD, sheetSuffix, type));
}
countDownLatch.await();
log.info("多线程数据封装耗时:{}", (System.currentTimeMillis() - time));
response.setHeader("content-Type", "application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + ExcelUtil.processFileName(sheetSuffix + ".xlsx"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
log.error("导出普/危货数据出错，{}", e.getMessage());
} finally {
try {
if (null != workbook) {
workbook.close();
}
} catch (Exception e) {
log.error("关闭 workbook 失败，{}", e.getMessage());
}
}
log.info("普危货导出总耗时:{}", (System.currentTimeMillis() - startTime));
}
/**
* 普危货导出任务
*/
class ExportTask implements Runnable {
/**
* poi对象
*/
private Workbook workbook;
/**
* 查询偏移量
*/
private Integer offset;
/**
* sheet页后缀
*/
private String sheetSuffix;
/**
* 类型，0危化无单 1普货进园
*/
private Integer type;
public ExportTask(Workbook workbook, Integer offset, String sheetSuffix, Integer type) {
this.workbook = workbook;
this.offset = offset;
this.sheetSuffix = sheetSuffix;
this.type = type;
}
@Override
public void run() {
List<OrderPassApplyDo> list = orderPassApplyMapper.listExportDataByPage(type, offset, THREAD_THRESHOLD);
list.forEach(orderPassApplyDo -> {
Sheet sheet = workbook.getSheet(orderPassApplyDo.getCreatedMonth() + sheetSuffix);
Row row = createRow(sheet);
packageExportData(row, orderPassApplyDo);
});
// 计数减一
countDownLatch.countDown();
}
}
private Row createRow(Sheet sheet) {
Row row;
synchronized (lockMap.computeIfAbsent(sheet, k -> new Object())) {
int rowNum = sheet.getLastRowNum();
row = sheet.createRow(rowNum + 1);
}
return row;
}
private void packageExportData(Row row, OrderPassApplyDo orderPassApplyDo) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
int cellIndex = 0;
Cell cell0 = row.createCell(cellIndex++);
cell0.setCellValue(orderPassApplyDo.getUnloadEntName());
Cell cell1 = row.createCell(cellIndex++);
cell1.setCellValue(orderPassApplyDo.getLoadingEntName());
Cell cell2 = row.createCell(cellIndex++);
cell2.setCellValue(orderPassApplyDo.getOrderType() == 0 ? "进园装货" : "进园卸货");
Cell cell3 = row.createCell(cellIndex++);
cell3.setCellValue(sdf.format(orderPassApplyDo.getCreatedTime()));
Cell cell4 = row.createCell(cellIndex++);
cell4.setCellValue(orderPassApplyDo.getTractorPlateNum());
Cell cell5 = row.createCell(cellIndex++);
cell5.setCellValue(orderPassApplyDo.getTrailerPlateNum());
Cell cell6 = row.createCell(cellIndex++);
cell6.setCellValue(orderPassApplyDo.getDriverName());
Cell cell7 = row.createCell(cellIndex++);
cell7.setCellValue(orderPassApplyDo.getDriverIdCard());
Cell cell8 = row.createCell(cellIndex++);
cell8.setCellValue(orderPassApplyDo.getDriverContact());
Cell cell9 = row.createCell(cellIndex++);
cell9.setCellValue(orderPassApplyDo.getLoadingEntPerson());
Cell cell10 = row.createCell(cellIndex++);
cell10.setCellValue(orderPassApplyDo.getLoadingEntContact());
Cell cell11 = row.createCell(cellIndex++);
cell11.setCellValue(orderPassApplyDo.getUnloadEntPerson());
Cell cell12 = row.createCell(cellIndex++);
cell12.setCellValue(orderPassApplyDo.getUnloadEntContact());
Cell cell13 = row.createCell(cellIndex++);
cell13.setCellValue(orderPassApplyDo.getNote());
Cell cell14 = row.createCell(cellIndex++);
cell14.setCellValue(sdf.format(orderPassApplyDo.getPlanStartTime()));
Cell cell15 = row.createCell(cellIndex++);
cell15.setCellValue(sdf.format(orderPassApplyDo.getPlanEndTime()));
Cell cell16 = row.createCell(cellIndex++);
cell16.setCellValue(null != orderPassApplyDo.getActualStartTime() ? sdf.format(orderPassApplyDo.getActualStartTime()) : null);
Cell cell17 = row.createCell(cellIndex++);
cell17.setCellValue(null != orderPassApplyDo.getActualEndTime() ? sdf.format(orderPassApplyDo.getActualEndTime()) : null);
Cell cell18 = row.createCell(cellIndex++);
cell18.setCellValue(null != orderPassApplyDo.getAuditTime() ? sdf.format(orderPassApplyDo.getAuditTime()) : null);
Cell cell19 = row.createCell(cellIndex++);
cell19.setCellValue(orderPassApplyDo.getAuditComment());
Cell cell20 = row.createCell(cellIndex++);
cell20.setCellValue(orderPassApplyDo.getAuditorName());
Cell cell21 = row.createCell(cellIndex);
String status = null;
switch (orderPassApplyDo.getStatus()) {
case AUDIT_PENDING:
status = "审核中";
break;
case AUDIT_SUCCESS:
status = "审核通过";
break;
case AUDIT_FAILED:
status = "审核不通过";
break;
case APPLY_AGAIN_FOR_LATE:
status = "迟到再申请";
break;
case APPLY_INVALIDATION:
status = "失效";
break;
}
cell21.setCellValue(status);
}
扫一扫
3363
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
