Hutool Java Excel导出单元格合并方案

 1.示例代码

package com.seerbigdata.business.controller.test;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @Version 1.0
 * @Author: HuYongHui
 * @Date: 2024/5/16
 * Content:
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel(description = "学生信息")
public class Student {

    /**
     * id
     */
    @ApiModelProperty(value = "id")
    private Integer id;
    /**
     * 姓名
     */
    @ApiModelProperty(value = "姓名")
    private String name;
    /**
     * 年龄
     */
    @ApiModelProperty(value = "年龄")
    private Integer age;
    /**
     * 城市
     */
    @ApiModelProperty(value = "城市")
    private String city;
    /**
     * 镇街
     */
    @ApiModelProperty(value = "镇街")
    private String town;

    public Student(String name, Integer age, String city, String town){
        this.name = name;
        this.age = age;
        this.city = city;
        this.town = town;
    }
}

/**
     * 按要求合并单元格
     * @param response
     * @throws Exception
     */
    @ApiOperation(value = "导出数据到excel")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "response", value = "响应体对象", dataType = "HttpServletResponse")
    })
    @GetMapping("/export")
    public void export(HttpServletResponse response) throws Exception {
        List<Student> list = new ArrayList<>();
        list.add(new Student(1,"张三", 20, "广州", "白云"));
        list.add(new Student(3,"张三", 20, "东莞", "石排"));
        list.add(new Student(4,"张三", 20, "东莞", "长安"));
        list.add(new Student(5,"张三", 20, "深圳", "龙岗"));
        list.add(new Student(6,"张三", 20, "广州", "天河"));
        list.add(new Student(8,"李四", 23, "广州", "南沙"));
        list.add(new Student(9,"王五", 25, "广州", "白云"));
        list.add(new Student(10,"王五", 25, "广州", "南沙"));
        list.add(new Student(11,"王五", 25, "东莞", "长安"));
        list.add(new Student(12,"王五", 25, "佛山", "禅城"));

        //2.定义基础数据
        List<String> rowHead = CollUtil.newArrayList("id", "名字", "年龄", "城市", "镇街");
        //3.通过ExcelUtil.getBigWriter()创建Writer对象，BigExcelWriter用于大数据量的导出，不会引起溢出；
        ExcelWriter writer = ExcelUtil.getBigWriter();
        //4.写入标题
        writer.writeHeadRow(rowHead);
        ServletOutputStream out = null;

        //5.实现核心逻辑
        try {
            //6.定义容器保存人物数据
            List<List<Object>> rows = new LinkedList<>();
            //7.按照名字进行分组
            LinkedHashMap<String, List<Student>> classList = list.stream().collect(Collectors.groupingBy(item -> item.getName(),
                    LinkedHashMap::new, Collectors.toList()));

            int currentRow = 1; // 从第二行开始写（第一行为标题）

            for (Map.Entry<String, List<Student>> classEntry : classList.entrySet()) {
                List<Student> classStudents = classEntry.getValue();
                int classSize = classStudents.size();

                if (classSize > 1) {
                    //合并行，第一个参数是合并行的开始行号（行号从0开始），第二个参数是合并行的结束行号，第三个参数是合并的列号开始(列号从0开始)，
                    //第四个参数是合并的列号结束，第五个参数是合并后的内容，null不设置，第六个参数指是否支持设置样式，true指的是。
                    writer.merge(currentRow, currentRow + classSize - 1, 1, 1, classEntry.getKey(), true);
                    writer.merge(currentRow, currentRow + classSize - 1, 2, 2, classStudents.get(0).getAge(), true);
                }

                LinkedHashMap<String, List<Student>> cityList = classStudents.stream()
                        .collect(Collectors.groupingBy(Student::getCity, LinkedHashMap::new, Collectors.toList()));

                for (Map.Entry<String, List<Student>> cityEntry : cityList.entrySet()) {
                    List<Student> cityStudents = cityEntry.getValue();
                    int citySize = cityStudents.size();

                    if (citySize > 1) {
                        writer.merge(currentRow, currentRow + citySize - 1, 3, 3, cityEntry.getKey(), true);
                    }

                    for (Student student : cityStudents) {
                        List<Object> row = CollUtil.newArrayList(
                                student.getId(),
                                student.getName(),
                                student.getAge(),
                                student.getCity(),
                                student.getTown()
                        );
                        rows.add(row);
                        currentRow++;
                    }
                }
            }
            //8.导出数据
            //logger.info("导出数据：{}",rows.toString());
            // 一次性写出内容，使用默认样式，强制输出标题
            writer.write(rows, true);
            //response为HttpServletResponse对象
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //获取当前日期作为文件名
            Date currentDate = new Date();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String date = sdf.format(currentDate);
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            //response.setHeader("Content-Disposition", "attachment;filename=file.xlsx");
            response.setHeader("Content-Disposition", "attachment;filename=report_" + date + ".xlsx");
            out = response.getOutputStream();
            writer.flush(out, true);
        } finally {
            //关闭输出Servlet流
            IoUtil.close(out);
            //关闭writer，释放内存
            writer.close();
        }
    }

2.合并后的效果