多级Excel动态列导出实现

上山打个龙

已于 2024-08-12 13:49:22 修改

阅读量473

点赞数 4

文章标签： java excel

于 2024-08-12 13:38:23 首次发布

本文链接：https://blog.csdn.net/m0_65385393/article/details/141128623

版权

1.背景

在后端开发中，有时会涉及到excel文件导出，这个一方面最趁手的工具莫过于easyExcel。同样，easyExcel提供的动态列导出功能，官网例子相对简单，在这里我就easyExcel的动态列导出做进一步的丰富和实现，使其可以做到多级表头的动态导出。

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的导出，需要更多些的考虑。首先，确定确定要处理数据的格式：

[ {

    "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单元格合并的机制了，单元格（以列为角度）对应的field相同的自动合并在一起，当然，两个相邻的单元格对应的filed相同的，也会合并在一起。

我们在处理多级动态列导出时，无法知道当前表头是多少级的，在知道多少级之后，还需要填充我们得到的配置列数据，以便让表头深度对齐。具体代码实现如下所示：

  /**
     * 处理表头
     *
     * @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处理列表头与数据的对应关系

剩下的部分就比较好做了。

    /**
     * 数据处理：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集合就可以。像下面这样来对查表后得到的数据进行加工至我们预期的那种格式。

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;
    }
}