1111
导出五张表的数据写了四天,真难啊
需求 : 实现 Excel 的多 sheet 导出功能, 并且对数据样式进行相应的排版
直属领导让做成这样我做成这样
拼尽全力啊!真难搞
1. 首先获取到数据
@RequestMapping(path = "/{id}/export", method = RequestMethod.GET)
@ApiOperation(value = "任务导出", notes = "")
public void exportReport(@PathVariable Long projectId, @PathVariable Long id, HttpServletResponse response) throws IOException, ParseException {
DeptProject deptProject = projectService.getById(projectId);
StringBuilder fileName = new StringBuilder();
if (deptProject != null ){
fileName.append(deptProject.getName() + ".xls");
}
List<DeptProjectReportExport> exportList = new ArrayList<>();
List<ReportMoneyExport> reportMoneyExportList = new ArrayList<>();
for (DeptProjectReportMoneySchedule moneySchedule : reportService.getById(id).getMoneySchedules()) {
ReportMoneyExport moneyExport = new ReportMoneyExport();
DeptProjectMoney deptProjectMoney = projectService.getMoney(moneySchedule.getMoneyId());
moneyExport.setSeq(deptProjectMoney.getSeq());
moneyExport.setName(deptProjectMoney.getName());
moneyExport.setMoney(deptProjectMoney.getMoney());
moneyExport.setSurplusMoney(deptProjectMoney.getMoney()*(1-moneySchedule.getSchedule()*0.01));
moneyExport.setSchedule(moneySchedule.getSchedule());
reportMoneyExportList.add(moneyExport);
}
if (null != deptProject ) {
for (ReportProjectWork reportProjectWork : deptProject.getReportProjectWorks()) {
DeptProjectReportExport reportExport = new DeptProjectReportExport();
List<DeptProjectWorkExport> works = new ArrayList<>();
reportExport.setTaskName(reportProjectWork.getChain());
for (DeptProjectWork deptProjectWork : reportProjectWork.getDeptProjectWorks()) {
List<DeptProjectReportWorkSchedule> workSchedules = deptProjectReportService.findByDeptProjectWorkIdAndReportId(deptProjectWork.getId(),id);
DeptProjectWorkExport deptProjectWorkExport = new DeptProjectWorkExport();
deptProjectWorkExport.setName(deptProjectWork.getName());
deptProjectWorkExport.setProgress(deptProjectWork.getProgress());
deptProjectWorkExport.setResponsibleName(deptProjectWork.getResponsibleName());
deptProjectWorkExport.setAssistName(deptProjectWork.getAssistName());
for (DeptProjectReportWorkSchedule schedule : workSchedules){
switch (schedule.getMonth()){
case 1: deptProjectWorkExport.setJanuary(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
case 2: deptProjectWorkExport.setFebruary(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
case 3: deptProjectWorkExport.setMarch(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
case 4: deptProjectWorkExport.setApril(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
case 5: deptProjectWorkExport.setMay(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
case 6: deptProjectWorkExport.setJune(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
case 7: deptProjectWorkExport.setJuly(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
case 8: deptProjectWorkExport.setAugust(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
case 9: deptProjectWorkExport.setSeptember(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
case 10: deptProjectWorkExport.setOctober(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
case 11: deptProjectWorkExport.setNovember(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
case 12: deptProjectWorkExport.setDecember(this.addShow(schedule.getStatus(),schedule.getInstructions())); break;
}
}
works.add(deptProjectWorkExport);
}
reportExport.setWorks(works);
exportList.add(reportExport);
}
}对于数据的获取,因为涉及到了多张表就在这里进行数据的拼接了,
先拿到对应表的数据,封装到新建的导出实体中.
// 第一个导出实体 DeptProjectReportExport
@Excel(name="所属任务", needMerge = true )
private String taskName;
@ExcelCollection(name = "工作完成情况")
private List<DeptProjectWorkExport> works;// 包含的works 对应的实体 DeptProjectWorkExport
@Excel(name = "工作名称",height = 50)
private String name;
@Excel(name = "一月")
private String january;
@Excel(name = "二月")
private String february;
@Excel(name = "三月")
private String march;
@Excel(name = "四月")
private String april;
@Excel(name = "五月")
private String may;
@Excel(name = "六月")
private String june;
@Excel(name = "七月")
private String july;
@Excel(name = "八月")
private String august;
@Excel(name = "九月")
private String september;
@Excel(name = "十月")
private String october;
@Excel(name = "十一月")
private String november;
@Excel(name = "十二月")
private String december ;
@Excel(name = "进度", suffix = "%")
private Integer progress;
@Excel(name = "负责人")
private String responsibleName;
@Excel(name = "协助人")
private String assistName;将获取到的数据封装进对应的实体中就行了,
需要注意的点就在,使用集合存储的时候,就会出现添加上一级的大的单元格合并的情况, 此时就要考虑怎么出了 , 不是单个的数据都需要使用集合来存储,而不是你每次重新插入,那样会导致只保留最后一个数据,之前插入的数据会被覆盖掉.
@ExcelCollection(name = “工作完成情况”) 这个为标注集合的注解
第二个sheet内容
// ReportMoneyExport 第二个sheet的实体
@Excel(name = "序号")
private Integer seq;
private Long projectId;
@Excel(name = "预算名称",height = 30, width = 20)
private String name;
@Excel(name = "金额", width = 20)
private Double money;
@Excel(name = "剩余金额", width = 20)
private Double surplusMoney;
@TableField(exist = false)
@Excel(name = "使用进度", suffix = "%")
private Integer schedule;上面也进行了对第二个sheet的数据的封装
接下来就是重点了 ,导出多个sheet
/**
* 第一个sheet的内容拼接
* */
ExportParams reportWorkExportParams = new ExportParams();
reportWorkExportParams.setSheetName("工作完成情况");
// 创建sheet1使用得map
Map<String, Object> reportWorkExportMap = new HashMap<>();
// title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
reportWorkExportMap.put("title", reportWorkExportParams);
// 模版导出对应得实体类型
reportWorkExportMap.put("entity", DeptProjectReportExport.class);
// sheet中要填充得数据
reportWorkExportMap.put("data", exportList);
/**
* 第二个sheet的内容拼接
* */
ExportParams reportMoneyExportParams = new ExportParams();
reportMoneyExportParams.setSheetName("经费使用情况");
// 创建sheet1使用得map
Map<String, Object> reportMoneyExportMap = new HashMap<>();
// title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
reportMoneyExportMap.put("title", reportMoneyExportParams);
// 模版导出对应得实体类型
reportMoneyExportMap.put("entity", ReportMoneyExport.class);
// sheet中要填充得数据
reportMoneyExportMap.put("data", reportMoneyExportList);
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(reportWorkExportMap);
sheetsList.add(reportMoneyExportMap);
// 执行方法
Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
UploadUtil.setFileDownloadHeader(response,fileName.toString());
workbook.write(response.getOutputStream());查看代码上的注解可知, 需要创建你需要的数量的导出实体,和sheet的名称和对应的数据封装好,
最后创建 List<Map<String, Object>> sheetsList = new ArrayList<>();
来对于创建好的数据进行导出
sheetsList.add(reportWorkExportMap);
sheetsList.add(reportMoneyExportMap);
进入流的方式为:
Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
UploadUtil.setFileDownloadHeader(response,fileName.toString());
workbook.write(response.getOutputStream());
就完成啦
官方文档-easypoi = http://doc.wupaas.com/docs/easypoi/easypoi-1c0u9bn89hr5u
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
