JAVA Excel逐级合并单元格算法基于Hutool工具

我不是周雷

已于 2023-12-28 16:48:37 修改

阅读量1k

点赞数 11

文章标签： java excel 算法

于 2023-12-28 15:11:00 首次发布

本文链接：https://blog.csdn.net/qq_22213171/article/details/135269843

版权

文章介绍了如何使用Hutool库中的`mergeRowsByMap`方法，根据Map结构动态合并Excel表格的单元格，实现根据不同列值合并内容。

摘要由CSDN通过智能技术生成

pom依赖：

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.24</version>
</dependency>

算法代码如下：

/**
     * 根据map 从最深层开始描述excel数据，map的key理解为当前列 value理解为后一列，最后一列不需要合并，一个map如果value有三个，那么key需要合并当前列三行的数据
     * */
    public static ExcelRowTag mergeRowsByMap(Map<Object, ?> map, ExcelWriter writer, Integer column) {
        boolean isFirst = true;
        ExcelRowTag excelRowTag = new ExcelRowTag();
        for (Map.Entry<Object, ?> mapChild : map.entrySet()) {
            Object contentObject = mapChild.getKey();
            if (!(mapChild.getValue() instanceof Map)) {
                if (isFirst) {
                    excelRowTag.setStart(writer.getRowCount());
                    excelRowTag.setEnd(writer.getRowCount());
                    isFirst = false;

                }
                excelRowTag.setEnd(writer.getRowCount());
                writer.writeCellValue(column, writer.getRowCount(), contentObject);
                continue;
            }

            ExcelRowTag excelColumnChild = mergeRowsByMap((Map<Object, ?>) mapChild.getValue(), writer, column + 1);
            if (isFirst) {
                excelRowTag.setStart(excelColumnChild.getStart());
                isFirst = false;
            }
            excelRowTag.setEnd(excelColumnChild.getEnd());
            if (!excelColumnChild.getStart().equals(excelColumnChild.getEnd())) {
                writer.merge(excelColumnChild.getStart(), excelColumnChild.getEnd(), column, column, contentObject, false);
            } else {
                writer.writeCellValue(column, excelColumnChild.getStart(), contentObject);
            }

        }
        return excelRowTag;
    }

ExcelRowTag类：

@Data
public class ExcelRowTag {
    private Integer start;

    private Integer end;
}

启动类

public static void main(String[] args) {
        try (ExcelWriter writer = ExcelUtil.getWriter(true);
             FileOutputStream fileOutputStream = new FileOutputStream("/Users/a1234/Desktop/images/test.xlsx");
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream();) {
            List<String> title = new ArrayList<>(Arrays.asList("标题1", "标题2", "标题3", "标题4"));
            writer.writeHeadRow(title);
            //组装数据
            List<List<Object>> rows = new ArrayList<>();
            rows.add(Arrays.asList("测试1-1", "测试2-1", "测试3-1", "测试4-1"));
            rows.add(Arrays.asList("测试1-1", "测试2-1", "测试3-1", "测试4-2"));
            rows.add(Arrays.asList("测试1-1", "测试2-1", "测试3-2", "测试4-1"));
            rows.add(Arrays.asList("测试1-1", "测试2-2", "测试3-1", "测试4-1"));
            rows.add(Arrays.asList("测试2-1", "测试2-2", "测试3-1", "测试4-1"));
            //将数据的每一列都作为key 后一列当作其value
            HashMap<Object, ?> collect = rows.stream().collect(Collectors.groupingBy(x -> getNonNullKey(x.get(0)), LinkedHashMap::new,
                    Collectors.groupingBy(x -> getNonNullKey(x.get(1)), LinkedHashMap::new,
                            Collectors.groupingBy(x -> getNonNullKey(x.get(2)), LinkedHashMap::new,
                                    Collectors.groupingBy(x -> getNonNullKey(x.get(3)))))));
            //输出到excel 并合并单元格

            mergeRowsByMap(collect, writer, 0);

            writer.flush(outputStream);
            outputStream.toByteArray();
            fileOutputStream.write(outputStream.toByteArray());

        }catch (Exception e){
        }

    }

输出结果：