easyexcel poi 一个模板导出excel包含多个sheet

1.简述

通过调用ExcelExportUtil.exportExcelClone(map, params)实现

2.导出代码实例

@ResponseExcel
@ApiOperation(value = "导出excel表格", notes = "导出车辆信息统计excel表格")
@GetMapping("/export")
public void exportInfo(HttpServletResponse response) {
	try {
		// 获取模板文件
		String path = "excelFile/excel模板.xlsx";
		TemplateExportParams params = new TemplateExportParams(path);

		Map<Integer, List<Map<String, Object>>> map=new HashMap<>();

		//第一个sheet
		List<Map<String, Object>> mList=new ArrayList<>();
		Map<String,Object> m1=new HashMap<>();
		m1.put("title","好好学习，天天向上");
		m1.put("sheetName","sheet名称1");
		m1.put("list", VehicleVo.testData1());
		mList.add(m1);

		//第二个sheet
		List<Map<String, Object>> mList1=new ArrayList<>();
		Map<String,Object> m2=new HashMap<>();
		m2.put("title","量变达到质变");
		m2.put("sheetName","sheet名称2");
		m2.put("list", VehicleVo.testData2());
		mList1.add(m2);

		map.put(0,mList);
		map.put(1,mList1);

		Workbook workbook = ExcelExportUtil.exportExcelClone(map, params);

		// 设置文件名
		response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("年度车辆信息.xlsx", "UTF-8"));
		// 写出文件
		OutputStream os = new BufferedOutputStream(response.getOutputStream());
		// 输出
		workbook.write(os);
		os.flush();
		os.close();
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class VehicleVo {

	//序号
	private Integer index;
	//驾驶员
	private String driver;
	//车牌号
	private String number;
	//车辆型号
	private String models;

	public static List<VehicleVo> testData1(){
		List<VehicleVo> list=new ArrayList<>();
		list.add(new VehicleVo(1,"张三","车牌号1","车辆型号1"));
		list.add(new VehicleVo(2,"李四","车牌号2","车辆型号2"));
		return list;
	}

	public static List<VehicleVo> testData2(){
		List<VehicleVo> list=new ArrayList<>();
		list.add(new VehicleVo(1,"张飞","车牌号33","车辆型号33"));
		list.add(new VehicleVo(2,"关羽","车牌号22","车辆型号22"));
		return list;
	}
}

3.导出模板

4.导出效果

5.项目实战案例

@ResponseExcel
@ApiOperation(value = "导出excel表格", notes = "导出明细excel表格")
@GetMapping("/export/{checkDeptIds}")
public void exportInfo(@PathVariable("checkDeptIds") String checkDeptIds, HttpServletResponse response) {
	Map<Integer, List<Map<String, Object>>> maps=new HashMap<>();
	String[] ids = checkDeptIds.split(",");
	for (int i = 0; i < ids.length; i++) {
		long checkDeptId = Long.parseLong(ids[i]);
		SecrecyCheckDepartment dept = secrecyCheckDepartmentService.getById(checkDeptId);
		List<Map<String, Object>> mapList=new ArrayList<>();
		Map<String, Object> map = new HashMap<>();

		List<SecrecyCheckContent> list = secrecyCheckApproveService.getInfoById(checkDeptId);
		list.add(secrecyCheckContentService.selectHj(checkDeptId));

		map.put("year",dept.getCheckYear());
		map.put("sheetName",dept.getDeptName());
		map.put("list", list);
		mapList.add(map);
		maps.put(i,mapList);
	}

	try {
		// 获取模板文件
		String path = "excelFile/明细.xlsx";
		TemplateExportParams params = new TemplateExportParams(path);
		Workbook workbook = ExcelExportUtil.exportExcelClone(maps,params);//<!-- POI的版本要对应，不然依赖冲突，找不到方法什么的都是版本问题，版本一致的，可以忽略-->

		for (int i = 0; i < ids.length; i++) {
			Sheet sheet = workbook.getSheetAt(i);
			List<SecrecyCheckContent> list = (List<SecrecyCheckContent>) maps.get(i).get(0).get("list");
			/**合并行*/
			distributeUserService.rowspan(list, sheet,2,0,"targetName");
		}

		// 设置文件名
		response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("明细.xlsx", "UTF-8"));
		// 写出文件
		OutputStream os = new BufferedOutputStream(response.getOutputStream());
		// 输出
		workbook.write(os);
		os.flush();
		os.close();
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}