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

2023-07-02 03:26| 来源: 网络整理| 查看: 265

问题描述：

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

分析

` 按照之前的写法如下：

1.先设计表头如下 //设计表头 List 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 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包装一下sheet内的主要数据集：title,entityList，date(注：名称是不是固定的，但要与exportExcel()方法中取时候的key值对应上) List sheetsList = new ArrayList(); for(String sheetName : sheetList){ List dataList = new ArrayList(); for(StuInfo stu : studentInfoList){ //行数据 Map 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 maps = new ArrayList(); Map 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 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格式 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 list) { Workbook workbook = new HSSFWorkbook(); for (Map map : list) { MyExcelExportService service = new MyExcelExportService(); service.createSheetWithList(workbook, (ExportParams) map.get("title"), ExportParams.class, (List) map.get("entityList"), (Collection) map.get("data")); } return workbook; } public void createSheetWithList(Workbook workbook, ExportParams entity, Class pojoClass, List 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 excelParams = new ArrayList(); //我们要动态的表头和列，所以需要将设计好的表头塞入 List 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 sheetList = new ArrayList(); sheetList.add("测试sheet1"); sheetList.add("测试sheet2"); sheetList.add("测试sheet3"); //测试数据内容 Map scoreMap = new HashMap(); scoreMap.put("score1", "100"); scoreMap.put("score2", "100"); scoreMap.put("score3", "100"); List 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 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 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 sheetsList = new ArrayList(); for(String sheetName : sheetList){ List dataList = new ArrayList(); for(StuInfo stu : studentInfoList){ //行数据 Map 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 maps = new ArrayList(); Map 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 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(); } }

【本文地址】

公司简介

联系我们