package com.anwei.ssm.util;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.hssf.util.CellRangeAddress;
/**
* 合并工号相同的单元格(工号/姓名/考试次数)
* @author wjh
*
*/
public class PoiTest {
public static void ptest(Sheet sheet){
//把工号相同的单元格置空
if(sheet.getLastRowNum()>2){//获取最后一行行标,比行数小 满足合并的条件,2是数据行的开始
Row row_1 = sheet.getRow(2);
Cell cell_0 = row_1.getCell(0);
String staffcode = cell_0.getStringCellValue();
for(int i=3;i<=sheet.getLastRowNum();i++){//除第一个外,循环将内容相同的单元格设为"",这里体现出为什么原数据要有序
Row rows = sheet.getRow(i);
//此处表示对4个单元格进行内容相同合并处理
Cell cells_0 = rows.getCell(0);//工号
// Cell cells_1 = rows.getCell(1);//姓名
// Cell cells_2 = rows.getCell(5);//考试次数
if(staffcode.equals(cells_0.getStringCellValue())){
cells_0.setCellValue("");
// cells_1.setCellValue("");
// cells_2.setCellValue(Cell.CELL_TYPE_BLANK);
}else{
staffcode = cells_0.getStringCellValue();
}
}
}
//将为空的单元格与之前不为空的合并
int sk;
if(sheet.getLastRowNum()>2){
sk=2;
for(int i=3;i<=sheet.getLastRowNum();i++){
Row rows = sheet.getRow(i);
Cell cell_0 = rows.getCell(0);
if(cell_0.getStringCellValue() == ""){
if(i==sheet.getLastRowNum()){
sheet.addMergedRegion(new CellRangeAddress(sk, i, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(sk, i, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(sk, i, 5, 5));
}
}else{
if(sk != i-1){
sheet.addMergedRegion(new CellRangeAddress(sk, i-1, 0, 0));//起始行号,终止行号, 起始列号,终止列号
sheet.addMergedRegion(new CellRangeAddress(sk, i-1, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(sk, i-1, 5, 5));
}
sk =i;
}
}
}
}
}
[图片]
参考:https://blog.csdn.net/dabingsun/article/details/79917056