/**
* 合并单元格相同的值
* @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);