JAVA使用POI实现Excel单元格合并

实现效果

解释：只要是遇见与前一行相同的数据就合并

引入jar

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

controller层

    @PostMapping(value = "getExcel")
    public  void getExcel(@RequestBody BrucellosisListDTO brucellosisListDTO, HttpServletRequest request, HttpServletResponse response){
        businessTaskBrucellosisService.getExcel1(brucellosisListDTO,request,response);
    }

Service

 void getExcel1(BrucellosisListDTO brucellosisListDTO, HttpServletRequest request, HttpServletResponse response);

serviceImpl

 @Override
    public void getExcel1(BrucellosisListDTO brucellosisListDTO, HttpServletRequest request, HttpServletResponse response) {
        List<BrucellosisExportExcel> list = queryExcelList(brucellosisListDTO);
        //表头
        String[] titleAttr = {"姓名","养殖户类型","手机号","人口数","所在区域(省)","所在区域(市)","所在区域(区/县)","所在区域(乡镇)","所在区域(乡村)","防疫负责人","养殖总数","布病人数","布病人员","布病人手机号码","布病人身份证号码"};
        //设置单元的宽度
        int[] widthAttr = {30,30,30,30,50,30,30,30,30,30,30,30,30,30,30};
        String titleHead = "布病统计";
        List<Map<String, String>> dataList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(list)){
            LinkedHashMap<String, List<BrucellosisExportExcel>> collect = list.stream().collect(Collectors.groupingBy(BrucellosisExportExcel::getFarmerPhone, LinkedHashMap::new, Collectors.toList()));
            dataFarmer(collect,dataList);
        }
        Map<String,List<Map<String, String>>> map = Maps.newHashMap();
        map.put("布病统计", dataList);
        ExportExcelByPoiUtil.createExcel(response,titleAttr,titleHead,widthAttr,map,new int[]{0,1,2,3,4,5,6,7,8,9,10,11});
    }
    public List<BrucellosisExportExcel> queryExcelList(BrucellosisListDTO brucellosisListDTO) {
        //查询对应的养殖户信息
        List<BrucellosisExportExcel> list = businessTaskBrucellosisMapper.queryExcelList(brucellosisListDTO);
        //养殖户下患布病的人数
        List<BrucellosisFarmerNumVO> brucellosisFarmerNumVOList = businessTaskBrucellosisMapper.queryBruCount();
        if(!brucellosisFarmerNumVOList.isEmpty()){
            list.forEach(res ->{
                String farmerPhone = res.getFarmerPhone();
                List<BrucellosisFarmerNumVO> collect = brucellosisFarmerNumVOList.stream().filter(result -> result.getFarmerPhone().equals(farmerPhone)).collect(Collectors.toList());
                if(ObjectUtils.isNotEmpty(collect)){
                    res.setBruNum(collect.get(0).getBruNum());
                }
            });
        }
        return list;
    }

    /**
     *合并数据
     * @param collect 根据养殖户Id分组
     * @date 2024/3/21 17:18
     * @author fyh
     **/
    public  void dataFarmer(LinkedHashMap<String, List<BrucellosisExportExcel>> collect,List<Map<String, String>> dataList){
        Set<String> longs = collect.keySet();
        List<String> farmerIdList = new ArrayList<>(longs);
        for (int i = 0; i < farmerIdList.size(); i++) {
            List<BrucellosisExportExcel> list1 = collect.get(farmerIdList.get(i));
            dataVoWorkOrder(dataList,list1);
        }
    }

    private void dataVoWorkOrder(List<Map<String, String>> dataList, List<BrucellosisExportExcel> list) {
        if (CollectionUtils.isNotEmpty(list)) {
            for (BrucellosisExportExcel displayBrucellosisVO : list) {
                Map<String, String> temp = new HashMap<>();
                temp.put("姓名", displayBrucellosisVO.getFarmerName());
                temp.put("养殖户类型", displayBrucellosisVO.getFarmerTypeName());
                temp.put("手机号", displayBrucellosisVO.getFarmerPhone());
                temp.put("人口数", displayBrucellosisVO.getPopulation()+"");
                temp.put("所在区域(省)", displayBrucellosisVO.getProvinceName());
                temp.put("所在区域(市)", displayBrucellosisVO.getCityName());
                temp.put("所在区域(区/县)", displayBrucellosisVO.getAreaName());
                temp.put("所在区域(乡镇)", displayBrucellosisVO.getTownshipName());
                temp.put("所在区域(乡村)", displayBrucellosisVO.getStreetName());
                temp.put("防疫负责人", displayBrucellosisVO.getPersonInChargeName());
                temp.put("养殖总数", displayBrucellosisVO.getAnimalNum()+"");
                temp.put("布病人数", displayBrucellosisVO.getBruNum()+"");
                temp.put("布病人员", displayBrucellosisVO.getUserName());
                temp.put("布病人手机号码", displayBrucellosisVO.getUserPhone());
                temp.put("布病人身份证号码", displayBrucellosisVO.getIdCard());
                dataList.add(temp);
            }
        }
    }

ExportExcelByPoiUtil 合并的公共类

public class ExportExcelByPoiUtil {

    private static Logger logger = LoggerFactory.getLogger(ExportExcelByPoiUtil.class);

    /**
     * @param @param request
     * @param @param response
     * @param @param title 标题数组
     * @param @param titleHead  Excel标题
     * @param @param widthAttr  单元格宽度
     * @param @param maps  数据
     * @param @param mergeIndex  要合并的列   数组
     * @param @return    设定文件
     * @return String    返回类型
     * @throws
     */
    @SuppressWarnings("rawtypes")
    public static void createExcel(HttpServletResponse response,String[] title,String titleHead ,int[] widthAttr,Map<String/*sheet名*/, List<Map<String/*对应title的值*/, String>>> maps, int[] mergeIndex){
        if (title.length==0){
            return;
        }
        /*初始化excel模板*/
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = null;
        int n = 0;
        /*循环sheet页*/
        for(Map.Entry<String, List<Map<String/*对应title的值*/, String>>> entry : maps.entrySet()){
            /*实例化sheet对象并且设置sheet名称，book对象*/
            try {
                sheet = workbook.createSheet();
                workbook.setSheetName(n, entry.getKey());
                workbook.setSelectedTab(0);
            }catch (Exception e){
                e.printStackTrace();
            }
            // 设置样式 头 cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            // 水平方向的对齐方式
            CellStyle cellStyle_head = style(0, workbook);
            // 导出时间
            CellStyle cellStyle_export = style(3, workbook);
            // 标题
            CellStyle cellStyle_title = style(1, workbook);
            // 正文
            CellStyle cellStyle = style(2, workbook);
            // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            CellRangeAddress c1 = new CellRangeAddress(0, 0, 0, title.length-1);
            sheet.addMergedRegion(c1);
            CellRangeAddress c2 = new CellRangeAddress(1, 1, 0, title.length-1);
            sheet.addMergedRegion(c2);
            // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            Row row0 = sheet.createRow(0);
            // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
            Cell cell1 = row0.createCell(0);
            // 设置单元格内容 标题  可以自定义拼接
            //列 cell1.setCellValue("" + titleHead + "");
            cell1.setCellValue(titleHead);
            cell1.setCellStyle(cellStyle_head);
            // 设置合并单元格边框
            setRegionStyle(sheet, c1, cellStyle_head);
            setRegionStyle(sheet, c2, cellStyle_export);
            // 设置列宽
            for (int i = 0; i < widthAttr.length; i++) {
                sheet.setColumnWidth((short) i, (short) widthAttr[i] * 200);
            }
            // 在sheet里创建第二行
            Row row1 = sheet.createRow(1);
            // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
            Cell cell2 = row1.createCell(0);
            // 设置单元格内容 标题
            //cell2.setCellValue("导出时间：" + DateUtil.getDateString(DateUtil.DATE_TIME_PATTERN) );
            cell2.setCellValue("导出时间："+ DateUtil.now());
            cell2.setCellStyle(cellStyle_export);
            /*初始化标题，填值标题行（第一行）*/
            Row row2 = sheet.createRow(2);
            for(int i = 0; i<title.length; i++){
                /*创建单元格，指定类型*/
                //Cell cell_1 = row2.createCell(i, Cell.class.CELL_TYPE_STRING);
                Cell cell_1 = row2.createCell(i,CellType.STRING);
                //设置标题的值
                cell_1.setCellValue(title[i]);
                //设置标题样式
                cell_1.setCellStyle(cellStyle_title);
            }
            /*得到当前sheet下的数据集合*/
            List<Map<String/*对应title的值*/, String>> list = entry.getValue();
            /*遍历该数据集合*/
            List<PoiModel> poiModels = Lists.newArrayList();
            if(null!=workbook){
                Iterator iterator = list.iterator();
//                int index = 1;/*这里1是从excel的第二行开始，第一行已经塞入标题了*/
                int index = 3;/*这里3是从excel的第四行开始，前面几行已经塞入标题了*/
                while (iterator.hasNext()){
                    Row row = sheet.createRow(index);
                    /*取得当前这行的map，该map中以key，value的形式存着这一行值*/
                    @SuppressWarnings("unchecked")
                    Map<String, String> map = (Map<String, String>)iterator.next();
                    /*循环列数，给当前行塞值*/
                    for(int i = 0; i<title.length; i++){
                        String old = "";
                        /*old存的是上一行统一位置的单元的值，第一行是最上一行了，所以从第二行开始记*/
                        if(index > 3){
                            old = poiModels.get(i)==null ? "":poiModels.get(i).getContent();
                        }
                        /*循环需要合并的列*/
                        for(int j = 0; j < mergeIndex.length; j++){
                            /* 因为标题行前还有2行  所以index从3开始    也就是第四行*/
                            if(index == 3){
                                /*记录第一行的开始行和开始列*/
                                PoiModel poiModel = new PoiModel();
                                poiModel.setOldContent(map.get(title[i]));
                                poiModel.setContent(map.get(title[i]));
                                poiModel.setRowIndex(3);
                                poiModel.setCellIndex(i);
                                poiModels.add(poiModel);
                                break;
                            }else if(i > 0 && mergeIndex[j] == i){
                                /*这边i>0也是因为第一列已经是最前一列了，只能从第二列开始*/
                                /*当前同一列的内容与上一行同一列不同时，把那以上的合并, 或者在当前元素一样的情况下，前一列的元素并不一样，这种情况也合并*/
                                /*如果不需要考虑当前行与上一行内容相同，但是它们的前一列内容不一样则不合并的情况，把下面条件中||poiModels.get(i).getContent().equals(map.get(title[i])) && !poiModels.get(i - 1).getOldContent().equals(map.get(title[i-1]))去掉就行*/
                                //|| poiModels.get(i).getContent().equals(map.get(title[i])) && !poiModels.get(i - 1).getOldContent().equals(map.get(title[i-1]))
                                if(!poiModels.get(i).getContent().equals(map.get(title[i]))){
                                    if(index - 1 ==poiModels.get(i).getRowIndex() && poiModels.get(i).getCellIndex() == poiModels.get(i).getCellIndex()){
                                        continue;
                                    }
                                    /*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/
                                    CellRangeAddress cra=new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index - 1/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
                                    //在sheet里增加合并单元格
                                    sheet.addMergedRegion(cra);
                                    /*重新记录该列的内容为当前内容，行标记改为当前行标记，列标记则为当前列*/
                                    poiModels.get(i).setContent(map.get(title[i]));
                                    poiModels.get(i).setRowIndex(index);
                                    poiModels.get(i).setCellIndex(i);
                                }
                            }
                            /*处理第一列的情况*/
                            if(mergeIndex[j] == i && i == 0 && !poiModels.get(i).getContent().equals(map.get(title[i]))){
                                if(index - 1 ==poiModels.get(i).getRowIndex() && poiModels.get(i).getCellIndex() == poiModels.get(i).getCellIndex()){
                                    continue;
                                }
                                /*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/
                                CellRangeAddress cra=new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index - 1/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
                                //在sheet里增加合并单元格
                                sheet.addMergedRegion(cra);
                                /*重新记录该列的内容为当前内容，行标记改为当前行标记*/
                                poiModels.get(i).setContent(map.get(title[i]));
                                poiModels.get(i).setRowIndex(index);
                                poiModels.get(i).setCellIndex(i);
                            }

                            /*最后一行没有后续的行与之比较，所有当到最后一行时则直接合并对应列的相同内容  加2是因为标题行前面还有2行*/
                            if(mergeIndex[j] == i && index == list.size()+2){
                                if(index == poiModels.get(i).getRowIndex() && poiModels.get(i).getCellIndex() == poiModels.get(i).getCellIndex()){
                                    continue;
                                }
                                CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);
                                //在sheet里增加合并单元格
                                sheet.addMergedRegion(cra);
                            }
                        }
                        Cell cell = row.createCell(i,CellType.STRING);
                        cell.setCellValue(map.get(title[i]));
                        cell.setCellStyle(cellStyle);
                        /*在每一个单元格处理完成后，把这个单元格内容设置为old内容*/
                        poiModels.get(i).setOldContent(old);
                    }
                    index++;
                }
            }
            n++;
        }
        OutputStream out = null;
        try {
            Calendar calendar1 = Calendar.getInstance();
            String cal = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(calendar1.getTime());
            out = response.getOutputStream();
            response.reset();//清空输出流
            response.setHeader("Content-disposition", "attachment;filename=" + new String(titleHead.getBytes(StandardCharsets.UTF_8), "iso8859-1") + cal + ".xlsx");// 设定输出文件头
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");// 定义输出类型
            workbook.write(out);
        }catch (IOException e){
            logger.error("导出异常",e);
        }finally {
            try {
                out.flush();
                out.close();
            }catch (IOException e){
                logger.error("流的关闭异常",e);
            }
        }
    }

    /**
     * @param @return    设定文件  index 0:头 1：标题 2：正文
     * @return HSSFCellStyle    返回类型
     * @throws
     */
    public static CellStyle style(int index, Workbook workbook) {
        CellStyle cellStyle = null;
        if (index == 0) {
            // 设置头部样式
            cellStyle = workbook.createCellStyle();
            // 设置字体大小 位置
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 生成一个字体
            Font font = workbook.createFont();
            //设置字体
            font.setFontName("微软雅黑");
            //字体颜色
            font.setColor(IndexedColors.BLACK.getIndex());// HSSFColor.VIOLET.index
            font.setFontHeightInPoints((short) 12);
            // 上下居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //背景白色
            cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            //这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法展示背景色，头默认了 FillPatternType所以可以不指定
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //设置边框线
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            //设置对其
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setFont(font);
        }
        //标题
        if (index == 1) {
            cellStyle = workbook.createCellStyle();
            // 设置字体大小 位置
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 生成一个字体
            Font font_title = workbook.createFont();
            //设置字体
            font_title.setFontName("微软雅黑");
            font_title.setColor(IndexedColors.BLACK.getIndex());// HSSFColor.VIOLET.index
            //字体颜色
            font_title.setFontHeightInPoints((short) 10);
            cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
            //这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法展示背景色，头默认了 FillPatternType所以可以不指定
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //设置边框样式
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            //设置对其
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 上下居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setFont(font_title);
        }
        //正文
        if (index == 2) {
            // 设置样式
            cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 生成一个字体
            Font font_title = workbook.createFont();
            //设置字体
            font_title.setFontName("微软雅黑");
            cellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
            cellStyle.setWrapText(true); // 自动换行
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //背景白色
            cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            //设置边框样式
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        }
        //时间
        if (index == 3) {
            // 设置样式
            cellStyle = workbook.createCellStyle();
            // 居中
            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
            // 生成一个字体
            Font font_title = workbook.createFont();
            //设置字体
            font_title.setFontName("微软雅黑");
            font_title.setColor(IndexedColors.BLACK.getIndex());// HSSFColor.VIOLET.index
            // //字体颜色
            font_title.setFontHeightInPoints((short) 10);
            //font_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 上下居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //设置单元格格式
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setFont(font_title);

        }
        if (index == 4) {
            // 设置样式
            cellStyle = workbook.createCellStyle();
            //设置背景色
            cellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //设置边框样式
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        }
        return cellStyle;
    }
    /**
     * @param @param sheet
     * @param @param region
     * @param @param cs    设定文件
     * @return void    返回类型
     * @throws
     */
    public static void setRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            Row row = CellUtil.getRow(i, sheet);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                Cell cell = CellUtil.getCell(row, (short) j);
                cell.setCellStyle(cs);
            }
        }
    }
}

PoiModel 实体类

/**
 * @package: com.ruoyi.easyExcelHeadStyle
 * @program: prevention
 * @author: fyh
 * @date: 2024/3/21
 * @description: 用来记录上一行数据
 **/
@Data
public class PoiModel {
    //内容
    private String content;
    //上一行同一位置内容
    private String oldContent;
    //行标
    private int rowIndex;
    //列标
    private int cellIndex;
}