微服务超大Excel文件导出方案优化

1、在导出Excel时经常会碰到文件过大，导出特别慢

2、微服务限制了请求超时时间，文件过大情况必然超时

优化思路：

1、文件过大时通过文件拆分、打包压缩zip，然后上传到oss,并设置有效期（30天过期）

2、把同步下载改成异步

3、文件生成完成，更新任务状态

4、通过oss的下载链接下载文件

5、多模块复用，只需要实现各自查询Excel业务数据的接口即可

以下是部分参考代码：

public class TxnETExecutor extends AbstractExportTaskExecutor {private final MposScene mposScene;private final OssService ossService;@Overridepublic boolean support(int taskType) {return ExportTaskType.MPOS_TXN_BILL_EXPORT.ordinal() == taskType;}@Overridepublic int getFileSize(String reqBody, String audClientId, Long groupId) {TxnMposBillItemFindReq req = JSON.parseObject(reqBody, TxnMposBillItemFindReq.class);req.setAudClientId(audClientId);req.setLoginGroupId(groupId);req.setPage(1);req.setPageSize(1);req.setDiffStatus(true);Pageable pageable = mposScene.findByChannelBillItem(req);return pageable.getTotalPages().intValue();}public void execute(Long taskId, int fileSize, String reqBody, String audClientId, Long groupId) {TxnMposBillItemFindReq req = JSON.parseObject(reqBody, TxnMposBillItemFindReq.class);req.setAudClientId(audClientId);req.setLoginGroupId(groupId);req.setPage(1);req.setDiffStatus(true);int pageSize = Math.min(fileSize, DEFAULT_PAGE_SIZE);req.setPageSize(pageSize);Pageable pageable = mposScene.findByChannelBillItem(req);Long totalPages = pageable.getTotalPages();List<Map<String, Object>> first = parse(pageable.getItems());ByteArrayOutputStream outputStream = WebFluxUtils.buildExcelOutputStream(first, null);if (totalPages <= 1) {OssPutObjectResp objectResp = upload(audClientId, "xlsx", new ByteArrayInputStream(outputStream.toByteArray()));String fileUrl = objectResp.getFileUrl();update(CashierExportTaskUpdateReq.newBuilder().setId(taskId).setFileStatus(ExportTaskFileStatus.SUCCESS.ordinal()).setFileFormat("xlsx").setFileProgressRate("100%").setFileUrl(fileUrl).build());return;}Map<String, ByteArrayOutputStream> files = new HashMap<>();files.put(String.format("渠道对账单[%s-%s].xlsx", 1, totalPages), outputStream);for (int i = 2; i <= totalPages; i++) {req.setPage(i);Pageable pageableExportMore = mposScene.findByChannelBillItem(req);ByteArrayOutputStream j = WebFluxUtils.buildExcelOutputStream(parse(pageableExportMore.getItems()), null);files.put(String.format("渠道对账单[%s-%s].xlsx", i, totalPages), j);}Path tmp = zip(files);if (tmp == null) {update(CashierExportTaskUpdateReq.newBuilder().setId(taskId).setFileStatus(ExportTaskFileStatus.FAIL.ordinal()).setFileFormat("zip").setFileProgressRate("100%").build());return;}InputStream inputStream;try {inputStream = Files.newInputStream(tmp);} catch (IOException e) {e.printStackTrace();update(CashierExportTaskUpdateReq.newBuilder().setId(taskId).setFileStatus(ExportTaskFileStatus.FAIL.ordinal()).setFileFormat("zip").setFileProgressRate("100%").build());return;}try {OssPutObjectResp objectResp = upload(audClientId, "zip", inputStream);update(CashierExportTaskUpdateReq.newBuilder().setId(taskId).setFileStatus(ExportTaskFileStatus.SUCCESS.ordinal()).setFileFormat("zip").setFileProgressRate("100%").setFileUrl(objectResp.getFileUrl()).build());} catch (Exception e) {e.printStackTrace();update(CashierExportTaskUpdateReq.newBuilder().setId(taskId).setFileStatus(ExportTaskFileStatus.FAIL.ordinal()).setFileFormat("zip").setFileProgressRate("100%").build());} finally {try {Files.delete(tmp);} catch (IOException ignore) {}}}private List<Map<String, Object>> parse(Collection<TxnMposBillItemFindResp> items) {List<Map<String, Object>> result = new ArrayList<>();if (items == null || items.isEmpty()) {throw FibException.ofNotFound("暂无数据");}items.forEach(item -> {Map<String, Object> map = new LinkedHashMap<>();map.put("创建时间", item.getCreateTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));map.put("批次号", item.getBatchNo());map.put("交易时间", item.getTransTime());map.put("交易月份", item.getTransMonth());map.put("平台订单号", item.getServerOrderId());......result.add(map);});return result;}private OssPutObjectResp upload(String audClientId, String suffix, InputStream inputStream) {return ossService.putObject(OssPutObjectReq.newBuilder().setBucketName(DEFAULT_BUCKET).setAudClientId(audClientId).setDirectoryPath("***").setSuffix(suffix).build(), inputStream);}
}

效果图：