POI 自动合并相同值单元格工具类

POI 自动合并相同值单元格工具类

public class ExcelUtil {

    /**
     * @param startRowNum 开始行
     * @param endColNum 结束列，默认从0列开始
     * @param sheet
     */
    public static void mergeSameValue(int startRowNum, int endColNum, Sheet sheet){
        int sheetLastRowNum = sheet.getLastRowNum();
        if (sheetLastRowNum - startRowNum < 2) {
            // sheet 少于 2 行不需要合并
            return;
        }

        for (int i = 0; i <= endColNum; i++) {
            int nextRowNum = startRowNum;
            do {
                nextRowNum = mergeSameValueCell(nextRowNum, i, sheet);
            } while (nextRowNum < sheetLastRowNum);
        }
    }

    private static int mergeSameValueCell(int rowNum, int colNum, Sheet sheet){
        int sheetLastRowNum = sheet.getLastRowNum();

        int firstRowNum = rowNum;
        int lastRowNum = rowNum;

        Row row = sheet.getRow(rowNum);
        Cell cell = row.getCell(colNum);
        String cellValue = String.valueOf(CellUtil.getCellValue(cell));
        for (int i = (rowNum+1); i <= sheetLastRowNum; i++) {
            Row nextRow = sheet.getRow(i);
            Cell nextCell = nextRow.getCell(colNum);
            String nextValue = String.valueOf(CellUtil.getCellValue(nextCell));
            if (!StringUtils.equals(cellValue, nextValue)) {
                break;
            }
            lastRowNum = i;
        }


        if (firstRowNum == lastRowNum) {
            // 不需要合并
            return lastRowNum+1;
        }

        // 合并单元格
        CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRowNum, lastRowNum, colNum, colNum);
        sheet.addMergedRegion(cellRangeAddress);

        return lastRowNum+1;
    }
}

示例 test 方法中使用了 hutool-poi 工具类

public class ExcelUtilTest {

    @Test
    public void mergeTest(){

        URL resource = getClass().getClassLoader().getResource("");
        String path = resource.getPath() + "merge.xlsx";
        System.out.println(path);
        ExcelWriter writer = ExcelUtil.getWriter(path);

        //自定义标题别名
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("address", "地址");
        writer.write(getData());

        // 合并单元格
        com.zx.economy.utils.ExcelUtil.mergeSameValue(0, 1, writer.getSheet());

        writer.close();

    }

    @Data
    class ModelDemo{
        private String name;
        private String address;
    }

    private List<ModelDemo> getData(){
        List<ModelDemo> list = new ArrayList<>();
        ModelDemo modelDemo = new ModelDemo();
        modelDemo.setName("小红");
        modelDemo.setAddress("北京");
        list.add(modelDemo);
        modelDemo = new ModelDemo();
        modelDemo.setName("小红");
        modelDemo.setAddress("天津");
        list.add(modelDemo);
        modelDemo = new ModelDemo();
        modelDemo.setName("小南");
        modelDemo.setAddress("湖南");
        list.add(modelDemo);
        modelDemo = new ModelDemo();
        modelDemo.setName("bob");
        modelDemo.setAddress("湖南");
        list.add(modelDemo);
        return list;
    }
}

合并之后的效果如下

只能自动合并简单的表格，而且只能合并字符串类型的单元格，因为比较的之后是字符串比较，可以加一个比较方法，针对不同数据类型进行比较。