EasyPoi导出多sheet、多层级复杂表头使用键值对格式

问题描述：

刚派发一个任务下来说要导出export，要求导出是多sheet和复杂表头，easyPoi最常用的就是mvc方式导出，还有一种键值对的方式在官方文档(http://easypoi.mydoc.io/#category_50222)查阅下找到关键线索

---

分析

` 按照之前的写法如下：

	1.先设计表头如下

		//设计表头
		List<ExcelExportEntity> colList = new ArrayList<>();
		ExcelExportEntity colEntity = new ExcelExportEntity("姓名", "studentName");
		colEntity.setNeedMerge(true);
		colEntity.setWidth(20);
		colList.add(colEntity);
		colEntity = new ExcelExportEntity("年级", "graderName");
		colEntity.setNeedMerge(true);
		colEntity.setWidth(15);
		colList.add(colEntity);
		colEntity = new ExcelExportEntity("班级", "className");
		colEntity.setNeedMerge(true);
		colEntity.setWidth(15);
		colList.add(colEntity);
		colEntity = new ExcelExportEntity("课程", "course");
		//多层级表头
		List<ExcelExportEntity> entityList = new ArrayList<>();
		ExcelExportEntity entity = new ExcelExportEntity("语文", "course1");
		entity.setNeedMerge(true);
		entity.setWidth(15);
		entityList.add(entity);
		entity = new ExcelExportEntity("数学", "course2");
		entity.setNeedMerge(true);
		entity.setWidth(15);
		entityList.add(entity);
		entity = new ExcelExportEntity("英语", "course3");
		entity.setNeedMerge(true);
		entity.setWidth(15);
		entityList.add(entity);
		colEntity.setList(entityList);
		colList.add(colEntity);
		colEntity = new ExcelExportEntity("学校", "schoolName");
		colEntity.setNeedMerge(true);
		colEntity.setWidth(15);
		colList.add(colEntity);

	2.将数据封装到map中，要注意的是写入的数据需要多加一层List<Map>包装一下sheet内的主要数据集：title,entityList，date(注：名称是不是固定的，但要与exportExcel()方法中取时候的key值对应上)

		List<Map<String, Object>> sheetsList = new ArrayList<>();
		for(String sheetName : sheetList){
			List<Map<String, Object>> dataList = new ArrayList<>();
			for(StuInfo stu : studentInfoList){
				//行数据
				Map<String, Object> valMap = new HashMap<>(5);
				valMap.put("studentName", stu.getStudentName());
				valMap.put("graderName", stu.getGradeName());
				valMap.put("className", stu.getClassName());
				valMap.put("schoolName", stu.getStudentName());
				//多级表头对应行数据
				List<Map<String, Object>> maps = new ArrayList<>();
				Map<String, Object> map = new HashMap<>();
				map.put("course1",scoreMap.get("score1"));
				map.put("course2",scoreMap.get("score2"));
				map.put("course3",scoreMap.get("score3"));
				maps.add(map);
				valMap.put("course", maps);
				dataList.add(valMap);
			}
			Map<String, Object> sheetExportMap = new HashMap<>();
			//sheet名称、内容、内容标题
			ExportParams sheetExportParams = new ExportParams("学校学生信息标题", sheetName, ExcelType.HSSF);
			sheetExportParams.setTitleHeight((short) 20);
			sheetExportParams.setStyle(ExcelStyleUtil.class);
			//title 设置的是sheet名称和第一行的标题
			sheetExportMap.put("title", sheetExportParams);
			//导出表设计的表头
			sheetExportMap.put("entityList", colList);
			//导出数据list<map>格式
			sheetExportMap.put("data", dataList);
			sheetsList.add(sheetExportMap);
		}

	3.关键重要点导出createSheetWithList方法将表头数据存储进去

		Workbook workbook = MyExcelExportService.exportExcel(sheetsList);

	依据官方文档提示重写一下对exportExcel()的调用和createSheet()

public class MyExcelExportService {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExportBase.class);
   
    public static Workbook exportExcel(List<Map<String, Object>> list) {
        Workbook workbook = new HSSFWorkbook();
        for (Map<String, Object> map : list) {
            MyExcelExportService service = new MyExcelExportService();
            service.createSheetWithList(workbook, (ExportParams) map.get("title"), ExportParams.class, (List<ExcelExportEntity>) map.get("entityList"), (Collection<?>) map.get("data"));
        }
        return workbook;
    }
    
    public void createSheetWithList(Workbook workbook, ExportParams entity, Class<?> pojoClass, List<ExcelExportEntity> entityList, Collection<?> dataSet) {
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Excel export start ,class is {}", pojoClass);
            LOGGER.debug("Excel version is {}",
                    entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
        }
        if (workbook == null || entity == null || pojoClass == null || dataSet == null) {
            throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
        }
        try {
        	//重点在这
        	//源码里面是 List<ExcelExportEntity> excelParams = new ArrayList();
        	//我们要动态的表头和列，所以需要将设计好的表头塞入
            List<ExcelExportEntity> excelParams = entityList;
            Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
            ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
            String targetId = etarget == null ? null : etarget.value();
            //获取所有参数后,后面的逻辑判断就一致了
            ExcelExportServer excelExportServer = new ExcelExportServer();
            excelExportServer.getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass, null);
            excelExportServer.createSheetForMap(workbook, entity, excelParams, dataSet);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
        }
    }
}

结果展示：

	完整代码如下：

public void getTestExport(HttpServletResponse response) {
		//测试sheet
		List<String> sheetList = new ArrayList<>();
		sheetList.add("测试sheet1");
		sheetList.add("测试sheet2");
		sheetList.add("测试sheet3");
		//测试数据内容
		Map<String,String> scoreMap = new HashMap<>();
		scoreMap.put("score1", "100");
		scoreMap.put("score2", "100");
		scoreMap.put("score3", "100");
		List<StuInfo> studentInfoList = new ArrayList<>();
		StuInfo info = new StuInfo();
		info.setStudentName("张三");
		info.setGradeName("一年级");
		info.setClassName("一班");
		info.setSchoolName("xxx学校");
		studentInfoList.add(info);
		info = new StuInfo();
		info.setStudentName("李四");
		info.setGradeName("二年级");
		info.setClassName("一班");
		info.setSchoolName("xxx学校");
		studentInfoList.add(info);
		info = new StuInfo();
		info.setStudentName("王五");
		info.setGradeName("三年级");
		info.setClassName("一班");
		info.setSchoolName("xxx学校");
		studentInfoList.add(info);
		//设计表头
		List<ExcelExportEntity> colList = new ArrayList<>();
		ExcelExportEntity colEntity = new ExcelExportEntity("姓名", "studentName");
		colEntity.setNeedMerge(true);
		colEntity.setWidth(20);
		colList.add(colEntity);
		colEntity = new ExcelExportEntity("年级", "graderName");
		colEntity.setNeedMerge(true);
		colEntity.setWidth(15);
		colList.add(colEntity);
		colEntity = new ExcelExportEntity("班级", "className");
		colEntity.setNeedMerge(true);
		colEntity.setWidth(15);
		colList.add(colEntity);
		colEntity = new ExcelExportEntity("课程", "course");
		//多层级表头
		List<ExcelExportEntity> entityList = new ArrayList<>();
		ExcelExportEntity entity = new ExcelExportEntity("语文", "course1");
		entity.setNeedMerge(true);
		entity.setWidth(15);
		entityList.add(entity);
		entity = new ExcelExportEntity("数学", "course2");
		entity.setNeedMerge(true);
		entity.setWidth(15);
		entityList.add(entity);
		entity = new ExcelExportEntity("英语", "course3");
		entity.setNeedMerge(true);
		entity.setWidth(15);
		entityList.add(entity);
		colEntity.setList(entityList);
		colList.add(colEntity);
		colEntity = new ExcelExportEntity("学校", "schoolName");
		colEntity.setNeedMerge(true);
		colEntity.setWidth(15);
		colList.add(colEntity);
		
		List<Map<String, Object>> sheetsList = new ArrayList<>();
		for(String sheetName : sheetList){
			List<Map<String, Object>> dataList = new ArrayList<>();
			for(StuInfo stu : studentInfoList){
				//行数据
				Map<String, Object> valMap = new HashMap<>(5);
				valMap.put("studentName", stu.getStudentName());
				valMap.put("graderName", stu.getGradeName());
				valMap.put("className", stu.getClassName());
				valMap.put("schoolName", stu.getSchoolName());
				//多级表头对应行数据
				List<Map<String, Object>> maps = new ArrayList<>();
				Map<String, Object> map = new HashMap<>();
				map.put("course1",scoreMap.get("score1"));
				map.put("course2",scoreMap.get("score2"));
				map.put("course3",scoreMap.get("score3"));
				maps.add(map);
				valMap.put("course", maps);
				dataList.add(valMap);
			}
			Map<String, Object> sheetExportMap = new HashMap<>();
			//sheet名称、内容、内容标题
			ExportParams sheetExportParams = new ExportParams("学校学生信息标题", sheetName, ExcelType.HSSF);
			sheetExportParams.setTitleHeight((short) 20);
			sheetExportParams.setStyle(ExcelStyleUtil.class);
			sheetExportMap.put("title", sheetExportParams);
			sheetExportMap.put("entityList", colList);
			sheetExportMap.put("data", dataList);
			sheetsList.add(sheetExportMap);
		}
		String filename = "学生信息" + ".xls";
		//导出表
		Workbook workbook = MyExcelExportService.exportExcel(sheetsList);
		try {
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename="
							+ new String((filename).getBytes("utf-8"), "ISO-8859-1"));
			response.addHeader("Cache-Control", "no-cache");
			OutputStream out = response.getOutputStream();
			try {
				workbook.write(out);// 将数据写出去
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				out.close();
			}
		}catch (Exception e){
			e.printStackTrace();
		}
	}