java后台监听器导出 后期还需优化仅供自己适用

需求说明：公司要导出大量数据 平均在30w~100w左右，这个时候前台导入会超时在加上sql性能 这个时候可以用到后台导出，流程为 利用java监听器监听前端请求然后后端慢慢导入到exelce表格中，最后将导出成功的表格上传到文件服务器，返回下载链接给前端进行下载

     /** jz
     * 导出行程报表  后台导出然后上传到文件服务 给客户链接下载
     * 公司最近导出百万数据 但是不用第三方插件 只能以这种办法进行导出
     * 突然和另一个开发想到可以凑合用用 监听器后台导出
     */
    public R exportTripReportForms(@RequestBody ExprotDeviceTripDTO exprotDeviceTripDTO) {
        OvmsUser user = SecurityUtils.getUser();
        if (exprotDeviceTripDTO.getEtpId() == null && user != null) {
            if (user.getEtpId() != CommonConstants.ETP_ID_1) {
                exprotDeviceTripDTO.setEtpId(user.getEtpId());
            } else {
                exprotDeviceTripDTO.setEtpId(null);
            }
        }
        if (Objects.equals(exprotDeviceTripDTO.getEtpId(), null)) {
            exprotDeviceTripDTO.setEtpId(user.getEtpId());
        }
        List<EtpInfoSVo> currentAndParents1 = etpInfoFeign.getCurrentAndParents1(exprotDeviceTripDTO.getEtpId());
        currentAndParents1.forEach(etpInfoSVo -> {
            exprotDeviceTripDTO.setEtpIds(etpInfoSVo.getIds());
        });
        //List<DeviceTripVO> reportFormsVOS = deviceTripService.exportTripReportForms(exprotDeviceTripDTO);
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        RequestContextHolder.setRequestAttributes(servletRequestAttributes, true);//设置子线程共享
        //save export record
        ExportRecord exportRecord = new ExportRecord();
        exportRecord.setEtpId(exprotDeviceTripDTO.getEtpId());
        exportRecord.setExportType(ExportRecordConstant.HISTORICAL_ITINERARY);
        exportRecord.setExportStatus(ExportRecordConstant.EXPORTING);
        exportRecord.setUserId(SecurityUtils.getUser().getId());
        DateTimeFormatter dtf2 = DateTimeFormatter.ofPattern("yyyy-MM-dd_HH-mm-ss");
        String fileName = "历史行程_" + SecurityUtils.getUser().getId() + "_" + dtf2.format(LocalDateTime.now());
        exportRecord.setExportName(fileName);
        exportRecordService.save(exportRecord);
        exprotDeviceTripDTO.setExportId(exportRecord.getId());
        exprotDeviceTripDTO.setUserId(SecurityUtils.getUser().getId());
        exprotDeviceTripDTO.setFileName(fileName);
        try {
            publisher.publishEvent(new DeviceTripExcelExportEvent(this, exprotDeviceTripDTO));
        } catch (Exception e) {
            exportRecord.setExportStatus(ExportRecordConstant.EXPORT_FAILED);
            exportRecordService.updateById(exportRecord);
            log.error("发布历史行程导出错误");
        }

        //MsgCode.EXPORTING
        return R.ok(MsgCode.EXPORTING.msg());
    }

public class DeviceTripExcelExportEvent extends ApplicationEvent {

    private static final long serialVersionUID = 1L;

    @Setter
    @Getter
    private ExprotDeviceTripDTO deviceTripDTO;

    public DeviceTripExcelExportEvent(Object source) {
        super(source);
    }

    public DeviceTripExcelExportEvent(Object source, ExprotDeviceTripDTO carViolationsDTO) {
        super(source);
        this.deviceTripDTO = carViolationsDTO;
    }
}

Component
@Slf4j
public class DeviceTripExeclExportlListener implements ApplicationListener<DeviceTripExcelExportEvent> {
    @Autowired
    private DeviceTripService deviceTripService;
    @Autowired
    private ExprotUploadUtil exprotUploadUtil;
    @Autowired
    private ExportRecordService exportRecordService;
    @Value("${excel.queryLimit}")
    private int queryLimit;
    /**
     * 后台导出历史行程监听器
     * @param deviceTripExcelExportEvent
     */
    @Override
    @Async
    public void onApplicationEvent(DeviceTripExcelExportEvent deviceTripExcelExportEvent) {

        ExprotDeviceTripDTO deviceTripDTO = deviceTripExcelExportEvent.getDeviceTripDTO();
        ExportRecord exportRecord = new ExportRecord();
        exportRecord.setId(deviceTripDTO.getExportId());//更新导出表的实体
        List<ExprotDeviceTripVO> reportFormsVOS = deviceTripService.exportTripReportForms(deviceTripDTO);
        int numSheet = 1;
        int sheetLimit = 500;
        if (reportFormsVOS.size() > sheetLimit) {
            numSheet = reportFormsVOS.size() / queryLimit + 1;
        }
        int alarmCountPage = 1; //分页查询：每次5000
        if (reportFormsVOS.size() > queryLimit) {
            alarmCountPage = (reportFormsVOS.size() / queryLimit) + 1;
        }

        int count = 0;
        int num = 0;
        ExcelHandler handler = null;
        ExcelWriter excelWriter = null;
        try {
            //创建handler对象--参数:文件夹名
            handler = new ExcelHandler("DeviceTripExcel/" + deviceTripDTO.getUserId());
            excelWriter = handler.create(deviceTripDTO.getFileName(), ExprotDeviceTripVO.class, numSheet);

            List<ExprotDeviceTripVO> list = new ArrayList<>(1024);
            for (int t = 1; t <= alarmCountPage; t++) {//模拟分页页数 每页50W数据
                deviceTripDTO.setCurrent(t);
                deviceTripDTO.setSize((queryLimit));
                list = reportFormsVOS;
                //count 将控制插入哪一个sheet
                count += list.size();
                handler.write(excelWriter, list, count);
                list.clear();//必须clear,否则数据会重复
            }
            //上传文件

            //更新状态
            exportRecord.setExportStatus(ExportRecordConstant.EXPORT_SUCCEEDED);
            exportRecordService.updateById(exportRecord);
        } catch (Exception ex) {
            exportRecord.setExportStatus(ExportRecordConstant.EXPORT_FAILED);
            exportRecordService.updateById(exportRecord);
            ex.printStackTrace();
        } finally {
            if (null != excelWriter) {
                handler.finish(excelWriter);
            }
            String fileNames="DeviceTripExcel/" + deviceTripDTO.getUserId()+"/"+deviceTripDTO.getFileName();
            exprotUploadUtil.uploadExcel(deviceTripDTO.getFileName(),fileNames,"excel",exportRecord);
        }
    }

@Slf4j
@Service
//文件上传导出工具类
public class ExprotUploadUtil {
    @Autowired
    private ExportRecordService exportRecordService;
    @Autowired
    private MinioTemplate minioTemplate;

    public R uploadExcel(String path, String fileNames, String bucketName, ExportRecord exportRecord) {
        minioTemplate.createBucket(bucketName);
        MinioClient minioClient = minioTemplate.getMinioClient();
        FileInputStream fileInputStream = null;
        String filePath = null;
        try {
            File file = new File("");
            filePath = file.getCanonicalPath();
            fileInputStream = new FileInputStream(new File(filePath + "/" + fileNames + ".xlsx"));

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {

        }


//        String fileName = IdUtil.simpleUUID() + StrUtil.DOT + FileUtil.extName(file.getOriginalFilename());
        String fileName = path + ".xlsx";
        Map<String, String> resultMap = new HashMap<>(4);
//		resultMap.put("bucketName", CommonConstants.BUCKET_NAME);
        resultMap.put("bucketName", bucketName);
        resultMap.put("fileName", fileName);
        resultMap.put("url", String.format("/admin/sys-file/%s/%s", "excel", fileName));

        try {
            minioTemplate.putObject(bucketName, fileName, fileInputStream);
            /*关闭文件，进行删除本地文件*/
            fileInputStream.close();
            //文件管理数据记录,收集管理追踪文件
//            fileLog(file, fileName);
        } catch (Exception e) {
            log.error("上传失败", e);
            return R.failed(e.getLocalizedMessage());
        }
        //更新状态
        exportRecord.setExportUrl(resultMap.get("url"));
        exportRecord.setExportStatus(2);
        exportRecordService.updateById(exportRecord);

        File file = new File(filePath + "/" + fileNames + ".xlsx");
        if (file.isFile() && file.exists()) {
            /*删除本地文件*/
            file.delete();
        }
        return R.ok(resultMap);
    }

}