POI 合并单元格

一、合并效果

二、POI 合并单元格样例代码

package org.linxiupan.export.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PoiMergeTest {
	public static void main(String[] args) throws IOException{
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sht = wb.createSheet("sheet1");
		int columnSize = 6;
		for(int i=0;i<4;i++){
			XSSFRow row = sht.createRow(i);
			for(int j=0;j<columnSize;j++){
				XSSFCell cell = row.createCell(j);
				cell.setCellValue(i+":"+j);
			}
		}
		int rowPointer = 4;
		for(int i=4;i<7;i++){
			for(int j=0;j<3;j++){
				for(int k=0;k<2;k++){
					for(int l=0;l<3;l++){
						XSSFRow row = sht.createRow(rowPointer++);
						XSSFCell cell = row.createCell(0);
						cell.setCellValue(i);
						
						cell = row.createCell(1);
						cell.setCellValue(j);
						
						cell = row.createCell(2);
						cell.setCellValue(k);
						
						cell = row.createCell(3);
						cell.setCellValue(l);
						
						cell = row.createCell(4);
						cell.setCellValue((rowPointer-1)+":"+4);
						
						cell = row.createCell(5);
						cell.setCellValue((rowPointer-1)+":"+5);
					}
				}
			}
		}
		List<Integer> l1 = new ArrayList<Integer>();
		List<Integer> l2 = new ArrayList<Integer>();
		List<Integer> l3 = new ArrayList<Integer>();
		String s1 = "",s2="",s3="";
		Iterator<Row> itr = sht.iterator();
		while(itr.hasNext()){
			Row row = itr.next();
			if(row.getRowNum()<4)continue;
			String n1 = getCellValue(row.getCell(0));
			if(!n1.equals(s1)){
				l1.add(row.getRowNum());
				s1=n1;
			}
			
			String n2 = getCellValue(row.getCell(1));
			if(!n2.equals(s2)){
				l2.add(row.getRowNum());
				s2=n2;
			}
			
			String n3 = getCellValue(row.getCell(2));
			if(!n3.equals(s3)){
				l3.add(row.getRowNum());
				s3=n3;
			}
			
		}
		l1.add(sht.getLastRowNum()+1);
		l2.add(sht.getLastRowNum()+1);
		l3.add(sht.getLastRowNum()+1);
		
		Integer prevI = 4;
		for(Integer i:l1){
			if(prevI!=i){
				CellRangeAddress cra = new CellRangeAddress(prevI,i-1,0,0);
				sht.addMergedRegion(cra);
				prevI=i;
			}
		}
		
		prevI = 4;
		for(Integer i:l2){
			if(prevI!=i){
				CellRangeAddress cra = new CellRangeAddress(prevI,i-1,1,1);
				sht.addMergedRegion(cra);
				prevI=i;
			}
		}
		
		prevI = 4;
		for(Integer i:l3){
			if(prevI!=i){
				CellRangeAddress cra = new CellRangeAddress(prevI,i-1,2,2);
				sht.addMergedRegion(cra);
				prevI=i;
			}
		}
		
		//get imported values;
		List<String> measures = new ArrayList<String>();
		measures.add("2.0");
		Iterator<Row> importItr = sht.iterator();
		while(importItr.hasNext()){
			Row row = importItr.next();
			if(row.getRowNum()<4)continue;
			String measure = getCellValue(row.getCell(3));
			//System.out.println(measure);
			if(measures.contains(measure)){
				System.out.print("level1="+getCellValue(row.getCell(0)));
				System.out.print("\tlevel2="+getCellValue(row.getCell(1)));
				System.out.println("\tlevel3="+getCellValue(row.getCell(2)));
			}
		}
		
		FileOutputStream fos = new FileOutputStream(new File("/Users/lxp/Desktop/20180618.xlsx"));
		wb.write(fos);
		wb.close();
		fos.close();
		System.out.println("finished.");
	}
	
	static String getCellValue(Cell cell){
		CellType type = cell.getCellTypeEnum();
		if(type==CellType.NUMERIC){
			return String.valueOf(cell.getNumericCellValue());
		}else if(type==CellType.STRING){
			return cell.getStringCellValue();
		}
		
		
		return "";
	}
}