请求
<#-- 明细表导出-->
$("#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.