1瘪松、說明

實際業(yè)務中咸作，系統(tǒng)會涉及Excel報表下載共功能，當業(yè)務數(shù)據(jù)體量過大宵睦，為避免用戶點擊下載后長時間等待记罚，可設計成異步Excel(生成Excel和下載Excel兩步)下載，用戶點擊下載任務觸達后壳嚎，可進行其他業(yè)務操作桐智，稍后在業(yè)務報表模塊中查看生成的Excel信息，在點擊已生成完成的的Excel文件鏈接下載即可诬辈。

2、 設計方案

2.1 業(yè)務交互流程

（1）用戶點擊業(yè)務下載按鈕

image.png

image.png

image.png

2.2 設計示意圖

image.png

3、實現(xiàn)

3.1 數(shù)據(jù)庫表設計

• 導出文件表point_export_file

CREATE TABLE `point_export_file` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `zone_id` varchar(32) NOT NULL DEFAULT '0' COMMENT '導出文件所屬區(qū)部',
  `type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '文件類型',
  `file_name` varchar(256) DEFAULT '' COMMENT '文件名稱',
  `file_url` varchar(256) DEFAULT '' COMMENT '文件url',
  `operator_id` bigint(20) DEFAULT NULL COMMENT '操作人id',
  `operator_name` varchar(256) NOT NULL DEFAULT 'admin' COMMENT '用戶名稱',
  `down_count` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '下載次數(shù)',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最近更新時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='導出文件列表';

3.2 程序代碼實現(xiàn)

（1）controller類悦穿，接收業(yè)務導出請求攻礼，觸發(fā)異步導出任務。

@Qualifier("excelThreadPool")
@Autowired
private ExecutorService executorService;

@ApiOperation("導出積分明細")
@GetMapping(value = "/api/point/export")
public Response<Boolean> exportConsumeRecord(PointExportCriteria criteria) {
     // 觸發(fā)異步事件
     Future<?> future = executorService.submit( () -> {
          final String fileUrl = pointService.getPointRecordFileUrl(criteria);} );
     return Response.ok(true);
}

（2）配置類config栗柒，配置異步線程池礁扮。

@Bean("excelThreadPool")
   public ExecutorService buildExcelThreadPool() {
       int cpuNum = Runtime.getRuntime().availableProcessors();
       BlockingQueue<Runnable> workQueue = new LinkedBlockingQueue<>(1000);
       ThreadFactory threadFactory = new ThreadFactoryBuilder().setNameFormat("excel-pool-%d").build();
       return new ThreadPoolExecutor( cpuNum * 2 + 1, 5 * cpuNum,
               2, TimeUnit.MINUTES, workQueue, threadFactory);
   }

（3）核心異步業(yè)務實現(xiàn)類，讀取業(yè)務數(shù)據(jù)生成Excel文件瞬沦，并觸發(fā)上傳文件太伊。

public String getPointRecordFileUrl(PointExportCriteria criteria){
      // todo 1查詢數(shù)據(jù)
      List<Point>  pointList  = new ArrayList();
       int pageNo = 1;
       criteria.setPageNo(pageNo);
       criteria.setPageSize(PAGE_SIZE);
       String fileName = "自定義文件名頭-" + DateTime.now().toString("yyyyMMddHHmmss") + ".xlsx";
       String filePath = FILE_PATH +fileName;
       int total = PAGE_SIZE;
        // todo 2.使用alibaba Excel去寫文檔
       ExcelWriter excelWriter = null;
       try {
            // 這里 需要指定寫用哪個class去寫
            excelWriter = EasyExcel.write(filePath, Point.class).build();
            // 這里注意 如果同一個sheet只要創(chuàng)建一次
            WriteSheet writeSheet = EasyExcel.writerSheet("電子券使用記錄").build();
            while (true) {
                criteria.setPageNo(pageNo);
                Paging<Point> paging = point.pagingMapper(criteria);

                if (paging.getData().isEmpty()) {
                    log.warn("this paging now do not has coupon consume record,criteria {},pageNo {}", criteria, pageNo);
                    break;
                }
                List<Point> pointList = paging.getData();
                total= pointList.size();
                exportScopeDtoList.addAll(pointList);
                excelWriter.write(exportScopeDtoList, writeSheet);
                exportScopeDtoList.clear();
                pageNo++;
                if (total < PAGE_SIZE) {
                    break;
                }
            }

        } catch (Exception e) {
            log.error("export failed,cause:{} ", Throwables.getStackTraceAsString(e));
            throw new JsonResponseException(500,"point.export.fail");
        } finally {
            // 千萬別忘記finish 會幫忙關(guān)閉流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        // 獲取下載鏈接
        String fileUrl = getFileUrl(filePath, fileName, currentUser,zoneIds);
        log.info("導出積分 done criteria: {},cost: {} ms",criteria,stopwatch.elapsed(TimeUnit.MILLISECONDS));
        return fileUrl;
}

（4）文件上傳，OSS文件存儲逛钻，返回文件地址僚焦，并保存到導出文件列庫表。

    public String getFileUrl(String filePath, String fileName, BaseUser currentUser, List<String> zoneIds) {
        File file = new File(filePath);
        // 上傳云服務器
        String fileUrl = ossBlobClient.doUpload(file);
        PointExportFile exportFile = new PointExportFile();
        exportFile.setFileUrl(fileUrl);
        exportFile.setType(0);
        exportFile.setFileName(fileName);
        exportFile.setOperatorId(currentUser.getId());
        exportFile.setOperatorName(currentUser.getName());
        if (!CollectionUtils.isEmpty(zoneIds)) {
            exportFile.setZoneId(zoneIds.get(0));
        }
        exportFile.setDownCount(0);
        Response<Long> longResponse = pointExportFileMapper.create(exportFile);
        if (!longResponse.isSuccess()) {
            log.error("create export file record fail,cause {}",longResponse.getError());
            throw new JsonResponseException(longResponse.getError());
        }
        return fileUrl;
    }

（5）文件列表查詢曙痘，返回文件鏈接地址芳悲，名稱信息。前端通過Excel鏈接去完成下載操作边坤。

    @ApiOperation("文件導出分頁查詢")
    @RequestMapping(value = "/export/paging", method = RequestMethod.GET)
      public Response<Paging<PointExportFile>>  paging(
            @RequestParam(required = true,defaultValue = "1")Integer pageNo,
            @RequestParam(required = true,defaultValue = "10")Integer pageSize){
        if (log.isDebugEnabled()) {
            log.debug("start paging export file ,pageNo: {},pageSize: {}", pageNo,pageSize);
        }
        ExportFileCriteria exportFileCriteria = new ExportFileCriteria();
        exportFileCriteria.setPageNo(pageNo);
        exportFileCriteria.setPageSize(pageSize);
        exportFileCriteria.setUserZoneIds(ManageZoneUtil.queryUserManageZone());
        Response<Paging<ExportFile>> resp = exportFileReadService.paging(exportFileCriteria);
        if(!resp.isSuccess()){
            log.error("failed to paging export file cause: {}",
                    resp.getError());
            throw new JsonResponseException(resp.getError());
        }
        return resp;
    }