1.上干货
@GetMapping(value = "/exportExcel")
@ApiOperation(value = "查询统计(看板显示),导出")
public RestResult<String> exportExcel(@RequestParam(value = "startTime", required = false) String startTime,
@RequestParam(value = "endTime", required = false) String endTime,
@RequestParam(value = "departmentId", required = false) String departmentId,
HttpServletResponse response
) {
try {
List<DatagoActivity> strList = activityService.findCount(departmentId, startTime, endTime);
HSSFWorkbook wb = new HSSFWorkbook();
//整理分组(例如5个工会又数据)
List<DatagoActivity> strListTotal = new ArrayList<>();
List<String> departmentIdsList = new ArrayList<>();
for (int i = 0; i < strList.size(); i++) {
DatagoActivity datagoActivity = strList.get(i);
DatagoActivity newItem = new DatagoActivity();
if (Utils.isEmpty(departmentIdsList) || !departmentIdsList.contains(datagoActivity.getDepartmentId())) {
List<DatagoActivity> subList = new ArrayList<>();
newItem.setDepartmentName(datagoActivity.getDepartmentName());
newItem.setActivityNum(1);
newItem.setConutNum(datagoActivity.getApplynumber());
newItem.setCurrStartyear(datagoActivity.getCurrStartyear());
newItem.setCurrEndyear(datagoActivity.getCurrEndyear());
subList.add(datagoActivity);
newItem.setActivities(subList);
departmentIdsList.add(datagoActivity.getDepartmentId());
strListTotal.add(newItem);
} else {
int idx = departmentIdsList.indexOf(datagoActivity.getDepartmentId());
DatagoActivity datagoActivitytemp = strListTotal.get(idx);
datagoActivitytemp.setActivityNum(datagoActivitytemp.getActivityNum() + 1);
datagoActivitytemp.setConutNum(datagoActivitytemp.getConutNum() + datagoActivity.getApplynumber());
List<DatagoActivity> subList2 = datagoActivitytemp.getActivities();
subList2.add(datagoActivity);
datagoActivitytemp.setActivities(subList2);
}
}
if (strListTotal.size() > 0) {
HSSFSheet sheetTotal = wb.createSheet("各工会活动开展情况");
CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, 3);
//参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
sheetTotal.addMergedRegion(region1);
HSSFRow row1 = sheetTotal.createRow(0);
row1.createCell(0).setCellValue("统计时间:"+startTime+"-"+endTime);
//标题
HSSFRow rowTotalTitle = sheetTotal.createRow(1);
rowTotalTitle.createCell(0).setCellValue("工会名称");
rowTotalTitle.createCell(1).setCellValue("开展活动次数");
rowTotalTitle.createCell(2).setCellValue("活动参与人数");
rowTotalTitle.createCell(3).setCellValue("当前疗养轮次");
for (int i = 0; i < strListTotal.size(); i++) {
HSSFRow row = sheetTotal.createRow(i + 2);
DatagoActivity datagoActivityTotal = strListTotal.get(i);
row.createCell(0).setCellValue(datagoActivityTotal.getDepartmentName());
row.createCell(1).setCellValue(datagoActivityTotal.getActivityNum());
row.createCell(2).setCellValue(datagoActivityTotal.getConutNum());
row.createCell(3).setCellValue(datagoActivityTotal.getCurrStartyear()
+ "-" + datagoActivityTotal.getCurrEndyear()
);
//导出当前工会的明细
List<DatagoActivity> activitySubList = datagoActivityTotal.getActivities();
if (activitySubList.size() > 0) {
HSSFSheet sheetSub = wb.createSheet(datagoActivityTotal.getDepartmentName());
//标题
CellRangeAddress region2 = new CellRangeAddress(0, 0, 0, 4);
//参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
sheetSub.addMergedRegion(region2);
HSSFRow row2 = sheetSub.createRow(0);
row2.createCell(0).setCellValue("统计时间:"+startTime+"-"+endTime);
HSSFRow rowTitle = sheetSub.createRow(1);
rowTitle.createCell(0).setCellValue("活动名称");
rowTitle.createCell(1).setCellValue("活动类型");
rowTitle.createCell(2).setCellValue("活动时间");
rowTitle.createCell(3).setCellValue("报名人数");
rowTitle.createCell(4).setCellValue("活动所处疗养轮次");
for (int k = 0; k < activitySubList.size(); k++) {
HSSFRow rowSub = sheetSub.createRow(k + 2);
DatagoActivity datagoActivitySub = activitySubList.get(k);
rowSub.createCell(0).setCellValue(datagoActivitySub.getTitle());
rowSub.createCell(1).setCellValue(datagoActivitySub.getType().equals("1") ? "奖励型活动" : "常规性活动");
rowSub.createCell(2).setCellValue(datagoActivitySub.getTime());
rowSub.createCell(3).setCellValue(datagoActivitySub.getApplynumber());
rowSub.createCell(4).setCellValue(datagoActivitySub.getCycle());
}
//列宽自适应
for (int l = 0; l < 3; l++) {
sheetSub.autoSizeColumn(l);
}
}
}
//列宽自适应
for (int l = 0; l < 2; l++) {
sheetTotal.autoSizeColumn(l);
}
}
Date date = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd-hh-mm-ss");
String format = dateFormat.format(new Date());
FileOutputStream outputStream = new FileOutputStream(excelPath + "工会活动报名情况单" + format + ".xlsx");
String httpUrl = httpPath + excelPath.replaceAll(excelPath.split("/")[0], "") + "工会活动报名情况单" + format + ".xlsx";
wb.write(outputStream);
outputStream.flush();
outputStream.close();
wb.close();
return RestResultUtil.genSuccessResult(httpUrl);
} catch (Exception e) {
e.printStackTrace();
}
return RestResultUtil.failed();
}
2.成功实例