且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

POI 导出合并列

更新时间:2022-08-12 10:36:05

/**
 * 合并单元格相同的值
 * @param wb
 * @param startRow 去除表头要合并的第一行index
 * @param columns   要合并的列的index
 */
private static void mergeCell(Workbook wb, int startRow, int... columns) {
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中

    int numOfSheet = wb.getNumberOfSheets();
    logger.info("numOfSheet:" + numOfSheet);
    for (int i = 0; i < numOfSheet; i++) {
        Sheet sheet = wb.getSheetAt(i);

        for (int k = 0; k < columns.length; k++) {
            int cellNum = columns[k];

            int lastRowNum = sheet.getLastRowNum();
            logger.info("lastRowNum:" + lastRowNum);
            int currentRow = startRow;
            for (int j = startRow; j <= lastRowNum; j++) {
                Cell cell = sheet.getRow(j).getCell(cellNum);
                String cellValue = cell.getStringCellValue();

                String nextValue = getNextCellValueString(cellNum, sheet.getRow(j + 1));
                logger.info("当前值:" + cellValue + "     下一个值:" + nextValue);
                // 将当前cellValue 置空直到最后一个
                cell.setCellValue("");
                if (cellValue.equals(nextValue)) {
                    continue;
                } else {
                    // 获取左上角的单元格--合并后只保存左上角单元格的值
                    Cell leftTopCell = sheet.getRow(currentRow).getCell(cellNum);
                    leftTopCell.setCellStyle(cellStyle);
                    leftTopCell.setCellValue(cellValue);
                    // 多个cell相同合并
                    if (currentRow != j) {
                        sheet.addMergedRegion(new CellRangeAddress(currentRow, j, cellNum, cellNum));
                        currentRow = j + 1;
                    } else {
                        currentRow += 1;
                    }
                    logger.info("设置左上角单元格:" + currentRow + "     值:" + cellValue);
                }
            }
        }
    }

}

/**
 * 获取下一个单元格的值
 * @param cellNum
 * @param nextRow
 * @return
 */
private static String getNextCellValueString(int cellNum, Row nextRow) {
    String nextValue;
    if (nextRow != null) {
        Cell nextCell = nextRow.getCell(cellNum);
        if (nextCell != null) {
            nextValue = nextCell.getStringCellValue();
        } else {
            nextValue = "";
        }
    } else {
        nextValue = "";
    }
    return nextValue;
}

使用方法:指定要合并的开始行,指定要合并的数组列

 mergeCell(workbook,2, 2);