发布时间：2025-06-24 17:09:31 作者：北方职教升学中心 阅读量：800
/** * 数据处理：tableData与tableHeader对应关系处理 * * @param tableHeader 表格表头 [{"name":"指标项1","field":"index1code","children":[{"name":"指标项1-1","field":"index1code1"},{"name":"指标项1-2","field":"index1code2"]},]... * @param tableData 表格数据 * @return 处理后的数据 [["value11","value12"],["value21","value22"]] */ private static List<List<String>> data(List<Map<String, Object>> tableHeader, List<Map<String, Object>> tableData) { List<List<String>> dataList = new ArrayList<>(); List<String> fieldList = titleFieldList(tableHeader); for (Map<String, Object> row : tableData) { List<String> list = new ArrayList<>(); for (String field : fieldList) { list.add(Optional.ofNullable(row.get(field)).map(Object::toString).orElse("")); } dataList.add(list); } return dataList; } /** * 获取表头field列表 * * @param tableHeader * @return */ private static List<String> titleFieldList(List<Map<String, Object>> tableHeader) { ArrayList<String> list = new ArrayList<>(); for (Map<String, Object> map : tableHeader) { if (map.get("children") instanceof List) { List<Map<String, Object>> children = (List<Map<String, Object>>) map.get("children"); List<String> child = titleFieldList(children); list.addAll(child); } else { list.add(Optional.ofNullable(map.get("field")).map(Object::toString).orElse("")); } } return list; }3.实战测试
4.使用方法
1.controller层两行代码进行调用即可，动态列导出，不需要再定义相关的VO实体类
2.一般由serviceImpl来做对数据的处理，这里我们用一个map集合就可以。
1.背景
在后端开发中，有时会涉及到excel文件导出，这个一方面最趁手的工具莫过于easyExcel。
public CustomResultBO searchXxxList(xxxQueryBO queryBO){ //表头数据 List<ALlDistOrgEvaConfigPO> headList = RemoteSqlUtil.queryForList(DB_INSTANCE, sqlId + "getMgtIndexOfOrgEvaConfig", queryPO, ALlDistOrgEvaConfigPO.class);//table-data数据List<MgtIndexEvaluationResultPO> dataList = RemoteSqlUtil.queryForList(DB_INSTANCE, sqlId + "getMgtIndicEvaList", queryPO, MgtIndexEvaluationResultPO.class);// 数据处理：1.拼装表头 2.table数据封装List<Map<String, Object>> tableHeader = new ArrayList<>();List<Map<String, Object>> tableData = new ArrayList<>();// 1.拼装表头if (CollectionUtil.isNotEmpty(configList)) { // 管理单位字段 Map<String, Object> mgtOrgHeader = new HashMap<>(); mgtOrgHeader.put("field", "mgtOrgName"); mgtOrgHeader.put("name", "管理单位"); tableHeader.add(mgtOrgHeader); // 总体评分字段 Map<String, Object> totalScore = new HashMap<>(); totalScore.put("field", "totalScore"); totalScore.put("name", "总体评分"); tableHeader.add(totalScore); ...// 根据请求得来的headList数据，对表头进行处理（父子关系等）}// 2.table数据封装if (CollectionUtil.isNotEmpty(dataList)) { // 对请求得到的dataList数据集根据业务需要转为List<Map<String, Object>>的形式 // 每一个list对应页面上的一行记录，map中的key为与headList中的field字段对 //应,value为具体的值}CustomResultBO resultBO = new CustomResultBO();resultBO.setTableHeader(tableHeader);resultBO.setTableData(tableData);return resultBO;}5.附：ExcelUtils代码
/** * @author : tianlong * @ClassName : ExcelUtils * @description: * @datetime : 2024/ 06/ 10 * @version: : 1.0 */public class ExcelUtils { private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class); /** * 多级excel动态导出 * * @author : tianlong * @param response * @param fileName 文件名 * @param tableHeader 表头 [{"name":"指标项1","field":"index1code","children":[{"name":"指标项1-1","field":"index1code1"},{"name":"指标项1-2","field":"index1code2"]},] * @param tableData 数据集合 [{"index1code":"value1","index2code":"value2"},...] key与tableHeader的field对应, value为单元格数据 * @param mark 水印 */ public static void exportDynamic(HttpServletResponse response, String fileName, List<Map<String, Object>> tableHeader, List<Map<String, Object>> tableData, String mark) { try { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); FileUtils.checkFileName(fileName, "xlsx"); ServletOutputStream out = response.getOutputStream(); if (StringUtil.isBlank(mark)) { mark = "xx1.0"; } String sheetName = FileUtils.getFilePrefix(fileName);; EasyExcel.write(out) .head(head(tableHeader)) .sheet(sheetName) .registerWriteHandler(new WaterMarkStrategy(new WaterMark(mark))) .registerWriteHandler(new CustemHandler()) .doWrite(data(tableHeader, tableData)); } catch (IOException e) { throw new RuntimeException(e); } } /** * 处理表头 * * @param tableHeader 表头 [{"name":"指标项1","field":"index1code","children":[{"name":"指标项1-1","field":"index1code1"},{"name":"指标项1-2","field":"index1code2"]},] * @return 表头名列表 [[指标项1, 指标项1-1],[指标项1, 指标项1-1]] */ public static List<List<String>> head(List<Map<String, Object>> tableHeader) { List<Object> columnsList = new ArrayList<>(); int maxDeep = getDeep(tableHeader); for (Map<String, Object> map : tableHeader) { List<Object> columnItem = getChildren(map, null, maxDeep); columnsList.add(columnItem); } return flatList(columnsList); } /** * 数据处理：tableData与tableHeader对应关系处理 * * @param tableHeader 表格表头 [{"name":"指标项1","field":"index1code","children":[{"name":"指标项1-1","field":"index1code1"},{"name":"指标项1-2","field":"index1code2"]},]... * @param tableData 表格数据 * @return 处理后的数据 [["value11","value12"],["value21","value22"]] */ private static List<List<String>> data(List<Map<String, Object>> tableHeader, List<Map<String, Object>> tableData) { List<List<String>> dataList = new ArrayList<>(); List<String> fieldList = titleFieldList(tableHeader); for (Map<String, Object> row : tableData) { List<String> list = new ArrayList<>(); for (String field : fieldList) { list.add(Optional.ofNullable(row.get(field)).map(Object::toString).orElse("")); } dataList.add(list); } return dataList; } /** * 获取表头field列表 * * @param tableHeader * @return */ private static List<String> titleFieldList(List<Map<String, Object>> tableHeader) { ArrayList<String> list = new ArrayList<>(); for (Map<String, Object> map : tableHeader) { if (map.get("children") instanceof List) { List<Map<String, Object>> children = (List<Map<String, Object>>) map.get("children"); List<String> child = titleFieldList(children); list.addAll(child); } else { list.add(Optional.ofNullable(map.get("field")).map(Object::toString).orElse("")); } } return list; } /** * 递归表头map获取所有列 * * @param columnList [{"name":"指标项1","field":"index1code","children":[{"name":"指标项1-1","field":"index1code1"},{"name":"指标项1-2","field":"index1code2"]}] * @param top 路径列名列表 * @param deep 表头深度最大深度 * @return [[指标项1, 指标项1-1],[指标项1, 指标项1-2]] */ private static List<Object> getChildren(Map<String, Object> columnList, List<String> top, int deep) { if (columnList == null) return null; if (top == null) top = new ArrayList<>(); List<Object> current = new ArrayList<>(); String curNodeName =Optional.ofNullable(columnList.get("name").toString()).map(Object::toString).orElse(""); // 如果有子节点，则递归调用 if (columnList.get("children") instanceof List) { top.add(curNodeName); List<Map<String, Object>> children = (List<Map<String, Object>>) columnList.get("children"); for (Map<String, Object> child : children) { current.add(getChildren(child, new ArrayList<>(top), deep)); } } else { // 叶子节点时填充列 int size = top.size(); String copyName = (size > 0) ? top.get(size - 1) : curNodeName; List<String> tempList = new ArrayList<>(top); while (tempList.size() + 1 < deep) { tempList.add(copyName); } tempList.add(curNodeName); current = new ArrayList<>(tempList); } return current; } /** * 打平嵌套list * * @param nestedList [[指标项1, 指标项1, 指标项1], [[指标项2, 指标项2, 指标项1], [指标项2, 指标项2, 指标项3]]] * @return [[指标项1, 指标项1, 指标项1], [指标项2, 指标项2, 指标项1], [指标项2, 指标项2, 指标项3]] */ private static List<List<String>> flatList(List<?> nestedList) { List<List<String>> rs = new ArrayList<>(); for (Object element : nestedList) { if (element instanceof List) { rs.addAll(flatList((List<?>) element)); } else { // 不是list数据类型，就不需要遍历 rs.add(new ArrayList<>((List<String>) nestedList)); return rs; } } return rs; } /** * 获取表头深度方法 * * @param tableHeader [{"name":"指标项1","field":"index1code"},{"name":"指标项2","field":"index2code"},...] * @return deep深度 */ private static int getDeep(List<Map<String, Object>> tableHeader) { int deep = 0; for (Map<String, Object> map : tableHeader) { if (map.get("children") instanceof List) { List<Map<String, Object>> children = (List<Map<String, Object>>) map.get("children"); if (!children.isEmpty()) { deep = Math.max(deep, getDeep(children)); } } } return deep + 1; }}2.实现步骤
2.1定义一个工具类
public class ExcelUtils { /** * 多级excel动态导出 * * @author : tianlong * @param response * @param fileName 文件名 * @param tableHeader 表头 [{"name":"指标项1","field":"index1code","children":[{"name":"指标项1-1","field":"index1code1"},{"name":"指标项1-2","field":"index1code2"]},] * @param tableData 数据集合 [{"index1code":"value1","index2code":"value2"},...] key与tableHeader的field对应, value为单元格数据 * @param mark 水印 */ public static void exportDynamic(HttpServletResponse response, String fileName, List<Map<String, Object>> tableHeader, List<Map<String, Object>> tableData, String mark) { try { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); FileUtils.checkFileName(fileName, "xlsx"); ServletOutputStream out = response.getOutputStream(); if (StringUtil.isBlank(mark)) { mark = "xx1.0"; } String sheetName = FileUtils.getFilePrefix(fileName);; EasyExcel.write(out) .head(head(tableHeader)) .sheet(sheetName) .registerWriteHandler(new WaterMarkStrategy(new WaterMark(mark))) //.registerWriteHandler(new CustemHandler()) .doWrite(data(tableHeader, tableData)); } catch (IOException e) { throw new RuntimeException(e); } }}2.2处理列表头
head()对表头列的处理是这里面比较难办的地方。不管是多少级表头的excel的导出，需要更多些的考虑。
我们在处理多级动态列导出时，无法知道当前表头是多少级的，在知道多少级之后，还需要填充我们得到的配置列数据，以便让表头深度对齐。像下面这样来对查表后得到的数据进行加工至我们预期的那种格式。具体代码实现如下所示：
/** * 处理表头 * * @param tableHeader 表头 [{"name":"指标项1","field":"index1code","children":[{"name":"指标项1-1","field":"index1code1"},{"name":"指标项1-2","field":"index1code2"]},] * @return 表头名列表 [[指标项1, 指标项1-1],[指标项1, 指标项1-1]] */ public static List<List<String>> head(List<Map<String, Object>> tableHeader) { List<Object> columnsList = new ArrayList<>(); int maxDeep = getDeep(tableHeader); for (Map<String, Object> map : tableHeader) { List<Object> columnItem = getChildren(map, null, maxDeep); columnsList.add(columnItem); } return flatList(columnsList); }/** * 递归表头map获取所有列 * * @param columnList [{"name":"指标项1","field":"index1code","children":[{"name":"指标项1-1","field":"index1code1"},{"name":"指标项1-2","field":"index1code2"]}] * @param top 路径列名列表 * @param deep 表头深度最大深度 * @return [[指标项1, 指标项1-1],[指标项1, 指标项1-2]] */ private static List<Object> getChildren(Map<String, Object> columnList, List<String> top, int deep) { if (columnList == null) return null; if (top == null) top = new ArrayList<>(); List<Object> current = new ArrayList<>(); String curNodeName =Optional.ofNullable(columnList.get("name").toString()).map(Object::toString).orElse(""); // 如果有子节点，则递归调用 if (columnList.get("children") instanceof List) { top.add(curNodeName); List<Map<String, Object>> children = (List<Map<String, Object>>) columnList.get("children"); for (Map<String, Object> child : children) { current.add(getChildren(child, new ArrayList<>(top), deep)); } } else { // 叶子节点时填充列 int size = top.size(); String copyName = (size > 0) ? top.get(size - 1) : curNodeName; List<String> tempList = new ArrayList<>(top); while (tempList.size() + 1 < deep) { tempList.add(copyName); } tempList.add(curNodeName); current = new ArrayList<>(tempList); } return current; } /** * 打平嵌套list * * @param nestedList [[指标项1, 指标项1, 指标项1], [[指标项2, 指标项2, 指标项1], [指标项2, 指标项2, 指标项3]]] * @return [[指标项1, 指标项1, 指标项1], [指标项2, 指标项2, 指标项1], [指标项2, 指标项2, 指标项3]] */ private static List<List<String>> flatList(List<?> nestedList) { List<List<String>> rs = new ArrayList<>(); for (Object element : nestedList) { if (element instanceof List) { rs.addAll(flatList((List<?>) element)); } else { // 不是list数据类型，就不需要遍历 rs.add(new ArrayList<>((List<String>) nestedList)); return rs; } } return rs; }当然，获取表头深度，我们采用递归的做法：
/** * 获取表头深度方法 * * @param tableHeader [{"name":"指标项1","field":"index1code"},{"name":"指标项2","field":"index2code"},...] * @return deep深度 */ private static int getDeep(List<Map<String, Object>> tableHeader) { int deep = 0; for (Map<String, Object> map : tableHeader) { if (map.get("children") instanceof List) { List<Map<String, Object>> children = (List<Map<String, Object>>) map.get("children"); if (!children.isEmpty()) { deep = Math.max(deep, getDeep(children)); } } } return deep + 1; }2.3处理列表头与数据的对应关系
剩下的部分就比较好做了。因为要想做出一个通用的、
这里需要先提一下easyExcel单元格合并的机制了，单元格（以列为角度）对应的field相同的自动合并在一起，当然，两个相邻的单元格对应的filed相同的，也会合并在一起。首先，确定确定要处理数据的格式：
[ { "name":"指标项1", "field":"index1code", "children":[ { "name": "指标项1-1", "field":"index1code11" }, { "name": "指标项1-2", "field":"index1code12", "children":[ { "name": "指标项1-2-1", "field":"index1code121" }, ] }, { "name": "指标项2", "field":"index1code2" },]可以看出它是一个多级的样子。同样，easyExcel提供的动态列导出功能，官网例子相对简单，在这里我就easyExcel的动态列导出做进一步的丰富和实现，使其可以做到多级表头的动态导出。
