EasyPoi多sheet导出功能实现---java

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());
就完成啦

参考救命的文档 = https://blog.csdn.net/baidu_36821021/article/details/85216855
官方文档-easypoi = http://doc.wupaas.com/docs/easypoi/easypoi-1c0u9bn89hr5u