EasyExcel复杂处理(合并行和列)

日常开发时客户提出了下面样式的需求，于是记录一下。

导入EasyExcel依赖

<!-- 引入easyexcel导出依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

工具类

实现EasyExcel行和列合并都需要实现CellWriteHandler 接口

行合并

public class ExcelFillCellMergePrevCol implements CellWriteHandler {
    private static final String KEY ="%s-%s";
    //所有的合并信息都存在了这个map里面
    Map<String, Integer> mergeInfo = new HashMap<>();

    public ExcelFillCellMergePrevCol() {
    }
    public ExcelFillCellMergePrevCol(Map<String, Integer> mergeInfo) {
        this.mergeInfo = mergeInfo;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

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

    }

    @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();

        Integer num = mergeInfo.get(String.format(KEY, curRowIndex, curColIndex));
        if(null != num){
            // 合并最后一行 ,列
            mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex,num);
        }
    }

    public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {
        Sheet sheet = writeSheetHolder.getSheet();
        CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);
        sheet.addMergedRegion(cellRangeAddress);
    }

    //num从第几列开始增加多少列
    // curRowIndex 在第几行进行行合并
    // curColIndex 在第几列进行合并
    // num 合并多少格
    // 比如我上图中中心需要在第三行 从0列开始合并三列 所以我可以传入 (3,0,2) 
    public void add (int curRowIndex,  int curColIndex , int num){
        mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num);
    }

}

列合并

public class ExcelFillCellMergeStrategy implements CellWriteHandler {

    private int[] mergeColumnIndex; //数组存放这一行需要合并那几列  [0,1,2] 在这mergeRowIndex行中合并 0 、1、2列
    private int mergeRowIndex;  // 存放需要向上合并的列
    private Integer noMergeRowIndex;// 不要合并的列

    public ExcelFillCellMergeStrategy() {
    }

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

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

    @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 writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Row  preRow = cell.getSheet().getRow(curRowIndex - 1);
        if (preRow == null) {
            // 当获取不到上一行数据时，使用缓存sheet中数据
            preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
        }
        Cell preCell = preRow.getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        //不需要合并的列直接跳出
        if ( (noMergeRowIndex != null) &&   noMergeRowIndex == (curRowIndex - 1)  ){
            return;
        }
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);

        //此处需要注意：所以获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并
        boolean equals = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
        if (dataBool && equals) {
            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);
            }
        }
    }

}

样式工具类

public class CellStyleStrategy extends AbstractCellStyleStrategy {
 
    private WriteCellStyle headWriteCellStyle;
    private List<WriteCellStyle> contentWriteCellStyleList;
 
    private CellStyle headCellStyle;
    private List<CellStyle> contentCellStyleList;
 
    public CellStyleStrategy(WriteCellStyle headWriteCellStyle,
                                       List<WriteCellStyle> contentWriteCellStyleList) {
        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyleList = contentWriteCellStyleList;
    }
 
    public CellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
        this.headWriteCellStyle = headWriteCellStyle;
        contentWriteCellStyleList = new ArrayList<WriteCellStyle>();
        contentWriteCellStyleList.add(contentWriteCellStyle);
    }
 
    @Override
    protected void initCellStyle(Workbook workbook) {
        if (headWriteCellStyle != null) {
            headCellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
        }
        if (contentWriteCellStyleList != null && !contentWriteCellStyleList.isEmpty()) {
            contentCellStyleList = new ArrayList<CellStyle>();
            for (WriteCellStyle writeCellStyle : contentWriteCellStyleList) {
                contentCellStyleList.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));
            }
        }
    }



    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (headCellStyle == null) {
            return;
        }
        cell.setCellStyle(headCellStyle);
    }
 
    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (contentCellStyleList == null || contentCellStyleList.isEmpty()) {
            return;
        }
        cell.setCellStyle(contentCellStyleList.get(0));
    }


}

填充数据导出

实体类

由于特殊原因只展示部分，其他属性类推即可

@Data
@NoArgsConstructor
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(20)
public class TelephoneInfo  {
  
      @ExcelProperty("部门")
    private String departmentName;
    @ExcelProperty("现场")
    @ColumnWidth(40)
    private String sceneName;
    @ExcelProperty("团队")
    @ColumnWidth(40)
    private String teamName;
    @ExcelProperty({"团队人力","团队人力"})
    private Integer teamManpower;
    ExcelProperty({"团队人力","上线人力"})
    private Integer onlineManpower;
  
}

正文

   void   test(){
            // 查询出所有的记录 
            List<AgentTelephoneInfo> list =  statisticsService.queryStatistics(agentTelephoneInfo);
            // 创建列合并工具类对象
            ExcelFillCellMergePrevCol mergePrevCol = new ExcelFillCellMergePrevCol();
            int size = list.stream().filter(item -> StringUtils.isNotEmpty(item.getFlag())&&(Integer.valueOf(item.getFlag()) < 2) ).collect(Collectors.toList()).size();
            // 因为上图上方需要合并的行数不确定所以动态合并
            for (int i = 0 ; i < size ; i++ ){
                mergePrevCol.add( i+2,0,2); // i + 2 ?  因为表头占了两行所以要加2  不然报出不能合并的错误 A1:A2已经合并
                // (i + 2 , 0 , 2)  从i+2 行操作 在第0列合并2列   
            }
            EasyExcel.write(EasyExcelUtil.getOutputStream("外呼汇总报表", response), AgentTelephoneInfo.class)
                .sheet("外呼汇总报表")
                .registerWriteHandler(horizontalCellStyleStrategyBuilder()) // 字体表格样式工具类，下方展示
                .registerWriteHandler(new ExcelFillCellMergeStrategy(size,new int[]{0,1,2},size + 1)) // 行合并
                // size 从这一行开始下边的向上合并  合并 0、1、2 三列  size+1 不要向上合并
                // 好像直接把第一个参数 变为size + 1 貌似就可以，懒得改了，能用就行吧 》=《
                .registerWriteHandler(mergePrevCol)
                .doWrite(list);
                
                
            }
            
            
    public CellStyleStrategy horizontalCellStyleStrategyBuilder() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 13);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        return new CellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

最终效果：

 

参考：EasyExcel模板导出(行和列自动合并)_Lzfnemo2009的博客-CSDN博客_easyexcel模板导出