EasyExcel（根据条件动态合并单元格的重复数据）

1. 导入jar包

		<!--版本可以根据项目定-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

1. 添加一个工具类

		public class ExcelFillCellMergeStrategy implements CellWriteHandler { 

    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelFillCellMergeStrategy() { 
    }

    public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) { 
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { 

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { 

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { 

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) { 
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex > mergeRowIndex) { 
            for (int i = 0; i < mergeColumnIndex.length; i++) { 
                if (curColIndex == mergeColumnIndex[i]) { 
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }



    /** * 当前单元格向上合并 * * @param writeSheetHolder * @param cell 当前单元格 * @param curRowIndex 当前行 * @param curColIndex 当前列 */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) { 
        //获取当前行的第一列的数据和上一行的第一列数据，通过第一行数据是否相同进行合并
// Cell preCell_now = cell.getSheet().getRow(curRowIndex ).getCell(curColIndex);
// Object curData = preCell_now.getCellTypeEnum() == CellType.STRING ? preCell_now.getStringCellValue() : preCell_now.getNumericCellValue();
// Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex - 1);
        // Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();

        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        //
        if (curData.equals(preData)) { 
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) { 
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) { 
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) { 
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

3.直接上controller代码

 @GetMapping("/testExport")
    public void testExport1(HttpServletResponse response) { 
        try { 
// 设置第几列合并
            int[] mergeColumnIndex = { 0,1,2,3};
// 需要从第几行开始合并
            int mergeRowIndex = 1;
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            List<UserPo>  resultList= exportService.selectUserInfo();
            // 使用java8新特性的stream流去处理数据，把空的数据过滤掉
            List<UserBo> resultBo = resultList.stream().filter(Objects::nonNull)
                    .map(t -> { 
                        return UserBo.builder()
                                .name(t.getName())
                                .sex(t.getSex())
                                .age(t.getAge())
                                .build();
                    }).collect(Collectors.toList());
            // 设置文件名称
            String fileName = URLEncoder.encode("测试导出", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            // sheet名称
            EasyExcel.write(response.getOutputStream(), UserBo.class)
// excel版本
                    .excelType(ExcelTypeEnum.XLSX)
// 是否自动关流
                    .autoCloseStream(Boolean.TRUE)
                    .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex,mergeColumnIndex)).
                    sheet("测试导出").doWrite(resultBo);
        } catch (Exception e) { 
            e.printStackTrace();
        }
    }

4.因为我是测试的数据，在service创建了集合，在集合里面加入了对象

  @Override
    public List<UserPo> selectUserInfo() { 

        UserPo userPo1 = new UserPo("11","老大","男","11");
        UserPo userPo2 = new UserPo("22","老大","男","12");
        UserPo userPo3 = new UserPo("33","老大","男","13");
        UserPo userPo4 = new UserPo("44","李四","女","13");
        UserPo userPo5 = new UserPo("55","王五","男","13");
        UserPo userPo6 = new UserPo("66","刘六","男","11");
        UserPo userPo7 = new UserPo("11","刘六","男","11");
        UserPo userPo8 = new UserPo("66","刘六","男1","16");
        UserPo userPo9 = new UserPo("66","刘六","男1","16");
        UserPo userPo10 = new UserPo("66","11","男","14");
        UserPo userPo11 = new UserPo("66","11","男2","16");
        UserPo userPo12 = new UserPo("66","11","男2","16");
        UserPo userPo13 = new UserPo("66","刘六","男","16");
        UserPo userPo14 = new UserPo("66","刘六","男","6");
        UserPo userPo15 = new UserPo("66","刘六","男","6");
        UserPo userPo16 = new UserPo("66","刘六","男","16");
        UserPo userPo17 = new UserPo("66","33","男","16");
        UserPo userPo18 = new UserPo("66","刘六2","男","16");
        UserPo userPo19 = new UserPo("66","刘六2","男","16");
        UserPo userPo20 = new UserPo("66","刘六","男","16");

        List<UserPo> list = new ArrayList<>();
        list.add(userPo1);
        list.add(userPo2);
        list.add(userPo3);
        list.add(userPo4);
        list.add(userPo5);
        list.add(userPo6);
        list.add(userPo7 );
        list.add(userPo8 );
        list.add(userPo9 );
        list.add(userPo10);
        list.add(userPo11);
        list.add(userPo12);
        list.add(userPo13);
        list.add(userPo14);
        list.add(userPo15);
        list.add(userPo16);
        list.add(userPo17);
        list.add(userPo18);
        list.add(userPo19);
        list.add(userPo20);

        return list;
    }

4.涉及到的实体类UserPo

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserPo { 

    private String Id;

    private String name;

    private String sex;

    private String age;
}

6.涉及到的实体类 UserBo

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ContentRowHeight(25)
@HeadRowHeight(25)
@ColumnWidth(25)
public class UserBo { 

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("性别")
    private String sex;

    @ExcelProperty("年龄")
    private String age;
}

7.访问路径 显示结果