springboot-实现excle文件导出的单元格相同内容合并

导出excle文件中的单元格有些需要合并如何操作

例如：左边的表格想合并单元格成右边的表格更加便于观看

         

 一、依赖文件

 <!-- excle操作-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.8</version>
        </dependency>

        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.10</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.1</version>
        </dependency>

 二、代码

@RestController
@RequestMapping("/merge")
public class MergeController {

    @RequestMapping(value = "/excleExport" )
    @ResponseBody
    public void csvExport(HttpServletResponse response){
        //表头
        List<String> headerList = Arrays.asList("编号", "姓名");
        //通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        //写入表头
        writer.writeHeadRow(headerList);
        //组织数据
        // 组织数据
        List<List<Object>> data = new ArrayList<>();

        List list1 = Arrays.asList("001","张三");
        List list2 = Arrays.asList("001","张三");
        List list3 = Arrays.asList("001","张三");
        List list4 = Arrays.asList("002","李四");
        List list5 = Arrays.asList("002","李四");
        List list6 = Arrays.asList("003","王五");
        List list7 = Arrays.asList("003","王五");
        data.add(list1);
        data.add(list2);
        data.add(list3);
        data.add(list4);
        data.add(list5);
        data.add(list6);
        data.add(list7);

        //一次性写出内容，使用默认样式，强制输出标题
        writer.write(data,true);
        //设置表格宽度自动
        writer.autoSizeColumnAll();

        //获取底层的Workbook和Sheet对象
        Workbook workbook = writer.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);

        //合并相同编号的单元格
        int startRow = 1; // 数据开始行（跳过表头）
        for (int i = 1; i <= data.size(); i++) {
            if(i <data.size()) {
                if (!data.get(i).get(0).equals(data.get(i - 1).get(0))) {
                    System.out.println(data.get(i).get(0));
                    System.out.println(data.get(i - 1).get(0));
                    if (startRow < i) {
                        // 合并编号列  前两个参数起始行和结束行  后两个参数起始列和结束列
                        sheet.addMergedRegion(new CellRangeAddress(startRow, i, 0, 0));
                        // 合并姓名列
                        sheet.addMergedRegion(new CellRangeAddress(startRow, i, 1, 1));
                    }
                    startRow = i + 1;
                }
            }
            //处理最后一行
            if(i==data.size()){
                if(startRow != i) {
                    // 合并编号列  前两个参数起始行和结束行  后两个参数起始列和结束列
                    sheet.addMergedRegion(new CellRangeAddress(startRow, i, 0, 0));
                    // 合并姓名列
                    sheet.addMergedRegion(new CellRangeAddress(startRow, i, 1, 1));
                }
            }
        }



        //response为HttpServeltReponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");

        response.setHeader("Content-Disposition","attachment;filename=1.xls");
        ServletOutputStream out= null;
        try{
            out = response.getOutputStream();
            writer.flush(out,true);

        }catch (IOException e){
            e.printStackTrace();
        }finally {
            //关闭writer,释放内存
            writer.close();
        }
        IoUtil.close(out);
    }

}

 

三、postman调用

下载后就是开头合并单元格后的效果

 源码获取方式（免费）：
（1）登录-注册：http://resources.kittytiger.cn/
（2）搜索：springboot导出excle+导出的excle单元格合并