Java跨月数据按月分Excel打包成压缩包导出—开发记录

请求

<#-- 明细表导出-->
        $("#export").click(function () {
   
            let beginDate = $("#beginDate").val();
            let endDate = $("#endDate").val();
            let comName = $('#comName').combotree('getValues');
            let busiType = $('#busiType').combotree('getValues');
            let reportType = $("#reportType").val();
            if(!beginDate || !endDate){
   
                layer.alert("开始时间和结束时间都不能为空！");
                return ;
            }
            if (beginDate.substring(0,4) != endDate.substring(0,4)) {
   
                layer.alert("开始时间和结束时间必须为同一年！");
                return ;
            }
            layer.alert("导出执行中...");
            $.ajax({
   
                url:'detailExport',
                type:'POST',
                contentType: 'application/json;charset=utf-8',
                data: JSON.stringify({
   
                    'beginDate': beginDate,
                    'endDate': endDate,
                    'comName': comName,
                    'reportType': reportType,
                    'busiType': busiType,
                    'isAssessment': $('#isAssessment').val(),
                    'isUpStandard': $('#isUpStandard').val()
                }),
                success: function(data){
   

                }
            });
        });

入口

AnnualQualityAssesReportForm为接收参数实体

@RequestMapping("/detailExport")
    public void detailExport(@RequestBody AnnualQualityAssesReportForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
   
        if(form == null){
   
            return ;
        }
        form.setLimit(null);
        annualQualityAssesReportService.detailExport(request, response, form);
    }

明细导出有6种报表类型，这里以 日均发车间隔完成率明细导出 为例子

	/**
     * 质量考核明细表导出
     * @param request
     * @param response
     * @param form
     */
    public void detailExport(HttpServletRequest request, HttpServletResponse response, AnnualQualityAssesReportForm form) throws Exception {
   
        Object result = null;
        switch (form.getReportType()){
   
            case 1:    //高峰时段出车率
                detailPeakHourRateExport(request, response, form);
                break;
            case 2:    //高峰时段发车间隔完成率
                detailCompletionRateOfDepartIntervalPeakHoursExport(request, response, form);
                break;
            case 3:    //计划载客里程完成率
                detailCompletionRateOfPlanedPassengerMileageExport(request, response, form);
                break;
            case 4:    //首末班时间达标率
                detailStandardRateLineFirstAnfLastExport(request, response, form);
                break;
            case 5:    //运营趟次完成率
                detailCompletionRateOfassessmentAtripExport(request, response, form);
                break;
            case 6:  // 日均发车间隔完成率
                detailDayAvgStaratIntervalCompltRateExport(request, response, form);
                break;
        }
    }

先设计一个导出记录表

CREATE TABLE `t_export_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `export_name` varchar(100) DEFAULT NULL COMMENT '导入zip名',
  `function_flag` varchar(100) DEFAULT NULL COMMENT '功能标识',
  `export_start` datetime DEFAULT NULL COMMENT '导出开始时间',
  `export_end` datetime DEFAULT NULL COMMENT '导出结束时间',
  `export_user` varchar(40) DEFAULT NULL COMMENT '导出人',
  `export_status` int(11) DEFAULT NULL COMMENT '导出状态 0-导出中... 1-导出完毕',
  `export_path` varchar(100) DEFAULT NULL COMMENT '导出zip地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COMMENT='导出记录表';

	/**
     * 日均发车间隔完成率明细导出
     *
     * @param request
     * @param response
     * @param form
     * @return void
     */
    private void detailDayAvgStaratIntervalCompltRateExport(HttpServletRequest request, HttpServletResponse response, AnnualQualityAssesReportForm form) throws Exception {
   
        String beginDate = form.getBeginDate();
        String endDate = form.getEndDate();
        Date date = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        // 当前时间串yyyyMMddHHmmss
        String dateStr = format.format(date);
        // Excel标题及内容对应实体字段名
        String[] titles = {
   "业务版块", "分公司", "日期类型", "日期", "线路号", "日均发车间隔分钟数", "计划日趟次数", "实际日趟次数", "计划日均发车间隔", "实际日均发车间隔", "完成率", "标准", "是否考核", "是否达标"};
        String[] keys = {
   "busiType", "company", "weekDay", "signDate", "lineNo", "dayAvgStartIntervalMin", "dayPlanTimes", "dayRealTimes", "dayPlanAvgStartInterval", "dayRealAvgStartInterval", "completaRate", "standard", "isAssessment", "isUpStandard"};
        // 获取springboot下resources下statics路径
        String staticPath = ResourceUtils.getURL("classpath:").getPath() + "statics";
        // excel文件夹所在路径（功能+报表类型/当前时间串）
        String excelPath = staticPath + "/exportExcel/assessmentAnnualDetails" + form.getReportType() + "/" + dateStr;
        // 压缩包名
        String zipName = beginDate + "-" + endDate + "年度考核日均发车间隔完成率明细";
        // 压缩包文件真实路径
        String zipPath = staticPath + "/exportExcel/assessmentAnnualDetails" + form.getReportType() + "/" + zipName + dateStr + ".zip";
        // 压缩包文件相对项目的相对路径
        String zipRelPath = "/exportExcel/assessmentAnnualDetails" + form.getReportType() + "/" + zipName + dateStr + ".zip";
        // 插入导出记录表
        ExportRecord exportRecord = new ExportRecord();
        exportRecord.setFunctionFlag("assessmentAnnualDetails");
        exportRecord.setExportUser(Users.getCurrentUser().getLoginName());
        exportRecord.setExportStart(date);
        exportRecord.