EasyExcel动态单元格合并(跨行或跨列)

EasyExcel动态单元格合并(跨行或跨列)

• 简单的合并单元格可以参照官网提供的@OnceAbsoluteMerge()和@ContentLoopMerge()两个注解进行

@OnceAbsoluteMerge()注解只会合并一次就不再执行了

动态相同值合并单元格代码示例(可以直接使用):

先看结果:

1. 开启合并列行合并单元格,指定1,3,5,6列进行跨行合并结果图:

   

2. 开启行合并结果图(指定1,3,5,6列进行跨行合并)

   

3. 开启列合并结果图

   

具体代码显示:

1. 测试使用(先创建两个类麻烦,测试类中将注释放开可以使用内部类进行测试)

    
   import com.alibaba.excel.EasyExcel;
   import com.alibaba.excel.metadata.Head;
   import com.alibaba.excel.write.merge.AbstractMergeStrategy;
   import org.apache.poi.ss.usermodel.Cell;
   import org.apache.poi.ss.usermodel.Sheet;
   import org.apache.poi.ss.util.CellRangeAddress;
   import org.junit.jupiter.api.Assertions;
   import org.junit.jupiter.api.BeforeEach;
   import org.junit.jupiter.api.Test;
    
   import java.io.File;
   import java.util.*;
    
   /**
    * 功能描述：easyExcel单元格导出合并测试类,
    *
    * @author SXT
    * @version 1.0
    * @date 2024/3/8 20:56
    */
   public class EasyExcelTest {
    
       /** excel表头 */
       private List<List<String>> head;
    
       /** excel内容 */
       private List<List<Object>> data;
    
       @Test
       public void testMergeCell(){
           File file = new File("C:\\Users\\sxtvv\\Desktop\\testExcel合并.xlsx");
           EasyExcel.write(file)
                   .head(head)
                   .sheet()
                   // 开启相等值 列合并,行合并,从行索引1(excel中第二行)开始合并, 指定跨行合并的列索引
                   //.registerWriteHandler(new MergeCellStrategyHandler(true, true, 1, Set.of(0, 2, 4, 5)))
    
                   // 开启相等值 列合并,从行索引1(excel中第二行)开始合并
                   //.registerWriteHandler(new MergeCellStrategyHandler(true, 1))
    
                   // 开启相等值 行合并,从行索引1(excel中第二行)开始合并, 指定跨行合并的列索引
                   .registerWriteHandler(new MergeCellStrategyHandler(true, 1, Set.of(0, 2, 4, 5)))
                   .doWrite(data);
           System.out.println("导出成功");
    
       }
    
       @BeforeEach
       public void buildHead(){
           List<List<String>> head = new ArrayList<>();
           head.add(List.of("字段1"));
           head.add(List.of("字段2"));
           head.add(List.of("字段3"));
           head.add(List.of("字段4"));
           head.add(List.of("字段5"));
           head.add(List.of("字段6"));
           this.head = head;
       }
    
       @BeforeEach
       public void buildData(){
           List<List<Object>> data = new ArrayList<>();
           Set<Integer> column = Set.of(2, 4, 5);
           Set<Integer> row = Set.of(2,3, 6,7,8);
           // 重复
           int repeat = 2;
           for (int i = 0; i < 10; i++) {
               List<Object> el = new ArrayList<>(6);
               if (row.contains(i)) {
                   el.add("相同");
                   el.add("测试-1");
                   el.add("相同");
                   el.add("测试-" + i);
                   el.add("测试-" + i);
                   el.add("测试-" + i);
               }else {
                   for (int j = 0; j <6; j++) {
                       if (column.contains(j)) {
                           el.add("相同");
                       }else {
                           el.add("测试" + (i + 1) + "-" + (j + 1));
                       }
                   }
               }
               data.add(el);
           }
           this.data = data;
       }
    
       /**
        * 规则: 优先合并列,再合并行
        */
       //static class MergeCellStrategyHandler extends AbstractMergeStrategy {
       //    /**
       //     * 相同列合并
       //     */
       //    private boolean alikeColumn;
       //
       //    /**
       //     * 相同行合并
       //     */
       //    private boolean alikeRow;
       //
       //    /** 开始进行合并的行index */
       //    private int rowIndex;
       //
       //    /** 跨行合并的列index */
       //    private Set<Integer> columns;
       //
       //    private int currentRowIndex = 0;
       //
       //    public MergeCellStrategyHandler(boolean alikeColumn, boolean alikeRow, int rowIndex, Set<Integer> columns){
       //        Assertions.assertNotNull(columns, "指定列跨行合并不能为空");
       //        this.alikeColumn = alikeColumn;
       //        this.alikeRow = alikeRow;
       //        this.rowIndex = rowIndex;
       //        this.columns = columns;
       //    }
       //
       //    /**
       //     * 指定是否进行跨列合并单元格
       //     * @param alikeColumn
       //     * @param rowIndex
       //     */
       //    public MergeCellStrategyHandler(boolean alikeColumn, int rowIndex){
       //        this(alikeColumn, false, rowIndex, new HashSet<>());
       //    }
       //
       //    /**
       //     * 指定是否进行跨行合并单元格
       //     * @param alikeRow
       //     * @param rowIndex
       //     * @param columns
       //     */
       //    public MergeCellStrategyHandler(boolean alikeRow, int rowIndex, Set<Integer> columns){
       //        this(false, alikeRow, rowIndex, columns);
       //    }
       //
       //
       //    @Override
       //    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
       //        int rowId = cell.getRowIndex();
       //        currentRowIndex = rowId == currentRowIndex ? currentRowIndex : rowId;
       //        if (rowIndex > rowId) {
       //            return;
       //        }
       //        int columnId = cell.getColumnIndex();
       //        // 列合并
       //        if (alikeColumn && columnId > 0) {
       //            String currentCellVal = this.getCellVal(cell);
       //            Cell preCell = cell.getRow().getCell(columnId - 1);
       //            String preCellVal = this.getCellVal(preCell);
       //            if (null != currentCellVal && null != preCellVal && !preCellVal.isBlank() && !currentCellVal.isBlank()) {
       //                // 当前单元格内容与上一个单元格内容相等,进行合并处理
       //                if (preCellVal.equals(currentCellVal)) {
       //                    CellRangeAddress rangeAddress = new CellRangeAddress(currentRowIndex, currentRowIndex, columnId - 1, columnId);
       //                    rangeAddress = this.findExistAddress(sheet, rangeAddress, currentCellVal);
       //                    if (null != rangeAddress) {
       //                        sheet.addMergedRegion(rangeAddress);
       //                    }
       //                }
       //            }
       //        }
       //        // 行合并
       //        if (alikeRow && rowIndex < rowId && columns.contains(columnId)) {
       //            String currentCellVal = this.getCellVal(cell);
       //            Cell preCell = sheet.getRow(rowId - 1).getCell(columnId);
       //            String preCellVal = this.getCellVal(preCell);
       //            if (null != currentCellVal && null != preCellVal && !preCellVal.isBlank() && !currentCellVal.isBlank()) {
       //                // 当前单元格内容与上一行单元格内容相等,进行合并处理
       //                if (preCellVal.equals(currentCellVal)) {
       //                    //sheet.validateMergedRegions();
       //                    CellRangeAddress rangeAddress = new CellRangeAddress(currentRowIndex - 1, currentRowIndex, columnId, columnId);
       //                    rangeAddress = this.findExistAddress(sheet, rangeAddress, currentCellVal);
       //                    if (null != rangeAddress) {
       //                        sheet.addMergedRegion(rangeAddress);
       //                    }
       //                }
       //            }
       //        }
       //
       //    }
       //
       //    /**
       //     * 合并单元格地址范围,发现存在相同的地址则进行扩容合并
       //     *
       //     * @param isColumn       true:跨列合并 false:跨行合并
       //     * @param sheet
       //     * @param rangeAddress   :
       //     * @param currentVal 当前单元格中的值
       //     * @return
       //     */
       //    private CellRangeAddress findExistAddress(Sheet sheet, CellRangeAddress rangeAddress, String currentVal) {
       //        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
       //        int existIndex = 0;
       //        Map<Integer, CellRangeAddress> existIdexMap = new LinkedHashMap<>();
       //        if (null != mergedRegions && !mergedRegions.isEmpty()) {
       //            //验证当前合并的单元格是否存在重复(fixme 重复问题待处理!!!)
       //            for (CellRangeAddress mergedRegion : mergedRegions) {
       //                if (mergedRegion.intersects(rangeAddress)) {
       //                    existIdexMap.put(existIndex, mergedRegion);
       //                }
       //                existIndex++;
       //            }
       //        }
       //        if (existIdexMap.isEmpty()) {
       //            return rangeAddress;
       //        }
       //        List<Integer> existIndexList = new ArrayList<>(existIdexMap.size());
       //        for (Map.Entry<Integer, CellRangeAddress> addressEntry : existIdexMap.entrySet()) {
       //            CellRangeAddress exist = addressEntry.getValue();
       //            // 自动进行单元格合并处理
       //            int firstRow = rangeAddress.getFirstRow();
       //            int lastRow = rangeAddress.getLastRow();
       //            int firstColumn = rangeAddress.getFirstColumn();
       //            int lastColumn = rangeAddress.getLastColumn();
       //
       //            int firstRow1 = exist.getFirstRow();
       //            int lastRow1 = exist.getLastRow();
       //            int firstColumn1 = exist.getFirstColumn();
       //            int lastColumn1 = exist.getLastColumn();
       //            // 跨行合并 最后一列相等, 行不相等
       //            if (lastRow > lastRow1 && lastColumn == lastColumn1) {
       //                // 检查进行跨行合并的单元格是否已经存在跨列合并
       //                if (lastColumn > 0 && firstColumn1 != lastColumn1) {
       //                    // 获取当前单元格的前一列单元格
       //                    String cellVal = this.getCellVal(sheet.getRow(lastRow).getCell(lastColumn - 1));
       //                    if (null != cellVal && cellVal.equals(currentVal)) {
       //                        exist.setLastRow(lastRow);
       //                    }
       //                } else {
       //                    exist.setLastRow(lastRow);
       //                }
       //                rangeAddress = exist;
       //                existIndexList.add(addressEntry.getKey());
       //            }
       //
       //            // 跨列合并 行相等,列不相等
       //            if (lastColumn > lastColumn1 && firstRow == firstRow1 ) {
       //                exist.setLastColumn(lastColumn);
       //                rangeAddress = exist;
       //                existIndexList.add(addressEntry.getKey());
       //            }
       //        }
       //        // 移除已经存在且冲突的合并数据
       //        if (existIndexList.isEmpty()) {
       //            rangeAddress = null;
       //        }else {
       //            sheet.removeMergedRegions(existIdexMap.keySet());
       //        }
       //        return rangeAddress;
       //    }
       //
       //    private String getCellVal(Cell cell) {
       //        String val = null;
       //        try {
       //            val = cell.getStringCellValue();
       //        }catch (Exception e){
       //            System.out.printf("读取单元格内容失败:行%d 列%d %n", (cell.getRowIndex() + 1), (cell.getColumnIndex() + 1));
       //        }
       //        return val;
       //    }
       //}
    
   }
    

2. 使用easyexce导出excel并进合并单元格可以直接实现CellWriteHandler接口进行单元格合并,也可以继承AbstractMergeStrategy类实现抽象方法merge()进行,下列代码使用继承的方式显示单元格合并(核心类)

    
   import com.alibaba.excel.metadata.Head;
   import com.alibaba.excel.write.merge.AbstractMergeStrategy;
   import org.apache.poi.ss.usermodel.Cell;
   import org.apache.poi.ss.usermodel.Sheet;
   import org.apache.poi.ss.util.CellRangeAddress;
    
   import java.util.*;
    
   /**
    * 功能描述：规则: 优先合并列,再合并行
    *
    * @author SXT
    * @version 1.0
    * @date 2024/3/9 15:12
    */
   public class MergeCellStrategyHandler extends AbstractMergeStrategy {
       /**
        * 相同列合并
        */
       private boolean alikeColumn;
    
       /**
        * 相同行合并
        */
       private boolean alikeRow;
    
       /** 开始进行合并的行index */
       private int rowIndex;
    
       /** 跨行合并的列index */
       private Set<Integer> columns;
    
       private int currentRowIndex = 0;
    
       /**
        * 构造方法,指定合并方式
        * @param alikeColumn
        * @param alikeRow
        * @param rowIndex
        * @param columns
        */
       public MergeCellStrategyHandler(boolean alikeColumn, boolean alikeRow, int rowIndex, Set<Integer> columns){
           this.alikeColumn = alikeColumn;
           this.alikeRow = alikeRow;
           this.rowIndex = rowIndex;
           this.columns = columns;
       }
    
       /**
        * 指定是否进行跨列合并单元格
        * @param alikeColumn
        * @param rowIndex
        */
       public MergeCellStrategyHandler(boolean alikeColumn, int rowIndex){
           this(alikeColumn, false, rowIndex, new HashSet<>());
       }
    
       /**
        * 指定是否进行跨行合并单元格
        * @param alikeRow
        * @param rowIndex
        * @param columns
        */
       public MergeCellStrategyHandler(boolean alikeRow, int rowIndex, Set<Integer> columns){
           this(false, alikeRow, rowIndex, columns);
       }
    
    
       @Override
       protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
           int rowId = cell.getRowIndex();
           currentRowIndex = rowId == currentRowIndex ? currentRowIndex : rowId;
           if (rowIndex > rowId) {
               return;
           }
           int columnId = cell.getColumnIndex();
           // 列合并
           if (alikeColumn && columnId > 0) {
               String currentCellVal = this.getCellVal(cell);
               Cell preCell = cell.getRow().getCell(columnId - 1);
               String preCellVal = this.getCellVal(preCell);
               if (null != currentCellVal && null != preCellVal && !preCellVal.isBlank() && !currentCellVal.isBlank()) {
                   // 当前单元格内容与上一个单元格内容相等,进行合并处理
                   if (preCellVal.equals(currentCellVal)) {
                       CellRangeAddress rangeAddress = new CellRangeAddress(currentRowIndex, currentRowIndex, columnId - 1, columnId);
                       rangeAddress = this.findExistAddress(sheet, rangeAddress, currentCellVal);
                       if (null != rangeAddress) {
                           sheet.addMergedRegion(rangeAddress);
                       }
                   }
               }
           }
           // 行合并
           if (alikeRow && rowIndex < rowId && columns.contains(columnId)) {
               String currentCellVal = this.getCellVal(cell);
               Cell preCell = sheet.getRow(rowId - 1).getCell(columnId);
               String preCellVal = this.getCellVal(preCell);
               if (null != currentCellVal && null != preCellVal && !preCellVal.isBlank() && !currentCellVal.isBlank()) {
                   // 当前单元格内容与上一行单元格内容相等,进行合并处理
                   if (preCellVal.equals(currentCellVal)) {
                       //sheet.validateMergedRegions();
                       CellRangeAddress rangeAddress = new CellRangeAddress(currentRowIndex - 1, currentRowIndex, columnId, columnId);
                       rangeAddress = this.findExistAddress(sheet, rangeAddress, currentCellVal);
                       if (null != rangeAddress) {
                           sheet.addMergedRegion(rangeAddress);
                       }
                   }
               }
           }
    
       }
    
       /**
        * 合并单元格地址范围,发现存在相同的地址则进行扩容合并
        *
        * @param sheet
        * @param rangeAddress  单元格合并地址
        * @param currentVal 当前单元格中的值
        * @return
        */
       private CellRangeAddress findExistAddress(Sheet sheet, CellRangeAddress rangeAddress, String currentVal) {
           List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
           int existIndex = 0;
           Map<Integer, CellRangeAddress> existIdexMap = new LinkedHashMap<>();
           if (null != mergedRegions && !mergedRegions.isEmpty()) {
               //验证当前合并的单元格是否存在重复
               for (CellRangeAddress mergedRegion : mergedRegions) {
                   if (mergedRegion.intersects(rangeAddress)) {
                       existIdexMap.put(existIndex, mergedRegion);
                   }
                   existIndex++;
               }
           }
           if (existIdexMap.isEmpty()) {
               return rangeAddress;
           }
           List<Integer> existIndexList = new ArrayList<>(existIdexMap.size());
           for (Map.Entry<Integer, CellRangeAddress> addressEntry : existIdexMap.entrySet()) {
               CellRangeAddress exist = addressEntry.getValue();
               // 自动进行单元格合并处理
               int firstRow = rangeAddress.getFirstRow();
               int lastRow = rangeAddress.getLastRow();
               int firstColumn = rangeAddress.getFirstColumn();
               int lastColumn = rangeAddress.getLastColumn();
    
               int firstRow1 = exist.getFirstRow();
               int lastRow1 = exist.getLastRow();
               int firstColumn1 = exist.getFirstColumn();
               int lastColumn1 = exist.getLastColumn();
               // 跨行合并 最后一列相等, 行不相等
               if (lastRow > lastRow1 && lastColumn == lastColumn1) {
                   // 检查进行跨行合并的单元格是否已经存在跨列合并
                   if (lastColumn > 0 && firstColumn1 != lastColumn1) {
                       // 获取当前单元格的前一列单元格
                       String cellVal = this.getCellVal(sheet.getRow(lastRow).getCell(lastColumn - 1));
                       if (null != cellVal && cellVal.equals(currentVal)) {
                           exist.setLastRow(lastRow);
                       }
                   } else {
                       exist.setLastRow(lastRow);
                   }
                   rangeAddress = exist;
                   existIndexList.add(addressEntry.getKey());
               }
    
               // 跨列合并 行相等,列不相等
               if (lastColumn > lastColumn1 && firstRow == firstRow1 ) {
                   exist.setLastColumn(lastColumn);
                   rangeAddress = exist;
                   existIndexList.add(addressEntry.getKey());
               }
           }
           // 移除已经存在且冲突的合并数据
           if (existIndexList.isEmpty()) {
               rangeAddress = null;
           }else {
               sheet.removeMergedRegions(existIdexMap.keySet());
           }
           return rangeAddress;
       }
    
       /**
        * 获取单元格中的内容
        * @param cell
        * @return
        */
       private String getCellVal(Cell cell) {
           String val = null;
           try {
               val = cell.getStringCellValue();
           }catch (Exception e){
               System.out.printf("读取单元格内容失败:行%d 列%d %n", (cell.getRowIndex() + 1), (cell.getColumnIndex() + 1));
           }
           return val;
       }
   }