首先将内容相同的单元格设置为空:
if(hssfSheet.getLastRowNum()>3){//获取最后一行行标,比行数小 满足合并的条件,3是数据行的开始,0 1 2行为表头
HSSFRow row_1 = hssfSheet.getRow(3);
HSSFCell cell_0 = row_1.getCell(0);
HSSFCell cell_1 = row_1.getCell(1);
HSSFCell cell_2 = row_1.getCell(2);
HSSFCell cell_3 = row_1.getCell(3);
String leader_name = cell_0.getStringCellValue();
String vice_leader_name = cell_1.getStringCellValue();
String group1_name = cell_2.getStringCellValue();
String group2_name = cell_3.getStringCellValue();
for(int i=4;i<=hssfSheet.getLastRowNum();i++){//除第一个外,循环将内容相同的单元格设为"",这里体现出为什么原数据要有序
HSSFRow rows = hssfSheet.getRow(i);
//此处表示对4个单元格进行内容相同合并处理
HSSFCell cells_0 = rows.getCell(0);
HSSFCell cells_1 = rows.getCell(1);
HSSFCell cells_2 = rows.getCell(2);
HSSFCell cells_3 = rows.getCell(3);
if(leader_name.equals(cells_0.getStringCellValue())){
cells_0.setCellValue("");
}else{
leader_name = cells_0.getStringCellValue();
}
if(vice_leader_name.equals(cells_1.getStringCellValue())){
cells_1.setCellValue("");
}else{
vice_leader_name = cells_1.getStringCellValue();
}
if(group1_name.equals(cells_2.getStringCellValue())){
cells_2.setCellValue("");
}else{
group1_name = cells_2.getStringCellValue();
}
if(group2_name.equals(cells_3.getStringCellValue())){
cells_3.setCellValue("");
}else{
group2_name = cells_3.getStringCellValue();
}
}
}
下面将为空的单元格合并:
int sk;
if(hssfSheet.getLastRowNum()>3){//将为空的单元格与之前不为空的合并
sk=3;
for(int i=4;i<=hssfSheet.getLastRowNum();i++){
HSSFRow rows = hssfSheet.getRow(i);
HSSFCell cell_0 = rows.getCell(0);
if(cell_0.getStringCellValue() == ""){
if(i==hssfSheet.getLastRowNum()){
hssfSheet.addMergedRegion(new CellRangeAddress(sk, i, 0, 0));
}
}else{
if(sk != i-1){
hssfSheet.addMergedRegion(new CellRangeAddress(sk, i-1, 0, 0));//起始行号,终止行号, 起始列号,终止列号
}
sk =i;
}
}
sk=3;
for(int i=4;i<=hssfSheet.getLastRowNum();i++){
HSSFRow rows = hssfSheet.getRow(i);
HSSFCell cell_1 = rows.getCell(1);
if(cell_1.getStringCellValue() == ""){
if(i==hssfSheet.getLastRowNum()){
hssfSheet.addMergedRegion(new CellRangeAddress(sk, i, 1, 1));
}
}else{
if(sk != i-1){
hssfSheet.addMergedRegion(new CellRangeAddress(sk, i-1, 1, 1));//起始行号,终止行号, 起始列号,终止列号
}
sk =i;
}
}
sk=3;
for(int i=4;i<=hssfSheet.getLastRowNum();i++){
HSSFRow rows = hssfSheet.getRow(i);
HSSFCell cell_2 = rows.getCell(2);
if(cell_2.getStringCellValue() == ""){
if(i==hssfSheet.getLastRowNum()){
hssfSheet.addMergedRegion(new CellRangeAddress(sk, i, 2, 2));
}
}else{
if(sk != i-1){
hssfSheet.addMergedRegion(new CellRangeAddress(sk, i-1, 2, 2));//起始行号,终止行号, 起始列号,终止列号
}
sk =i;
}
}
sk=3;
for(int i=4;i<=hssfSheet.getLastRowNum();i++){
HSSFRow rows = hssfSheet.getRow(i);
HSSFCell cell_3 = rows.getCell(3);
if(cell_3.getStringCellValue() == ""){
if(i==hssfSheet.getLastRowNum()){
hssfSheet.addMergedRegion(new CellRangeAddress(sk, i, 3, 3));
}
}else{
if(sk != i-1){
hssfSheet.addMergedRegion(new CellRangeAddress(sk, i-1, 3, 3));//起始行号,终止行号, 起始列号,终止列号
}
sk =i;
}
}
}
效果如下: