POI 合并单元格 及 注意事项

POI进行跨行需要用到对象HSSFSheet对象，现在就当我们程序已经定义了一个HSSFSheet对象sheet。

跨第1行第1个到第2个单元格的操作为
sheet.addMergedRegion(new Region(0,(short)0,0,(short)1));

跨第1行第1个到第2行第1个单元格的操作为
sheet.addMergedRegion(new Region(0,(short)0,1,(short)0));


重点注意事项：
1.单元格CELL和ROW对象下标都是从0开始的。
2.单元格合并时Region(1,2,3,4)第1个值的行号必须要比3位置的行号小，如果大于3就不能正常合并单元格
3.合并单元格的时候要合并的单单元格必须先创建，这样方便后面再次获取这个单元格来填充数据，主要就是因为合并时不能由后向前进行合并引起的。



完整的例子程序包含了输出

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<jsp:directive.page import="com.yuantiao.ipub.ytcms.service.ArticleService"/>
<jsp:directive.page import="com.yuantiao.ipub.ytcms.stat.bean.ArticleInfo"/>
<jsp:directive.page import="org.apache.poi.hssf.usermodel.*"/>
<jsp:directive.page import="org.apache.poi.hssf.util.HSSFColor"/>
<jsp:directive.page import="org.apache.poi.hssf.util.Region"/>
<jsp:directive.page import="java.text.SimpleDateFormat"/>
<%!//自定义的方法，主要用于插入中文的字符格式
  private void setGB2312String(HSSFCell Cell, String Value) throws
      Exception {
    Cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    Cell.setCellValue(Value);
  } 
%>


<%
ArticleService articleService = new ArticleService();
ArticleInfo articleInfo;

String beginTime = request.getParameter("beginTime");
String endTime = request.getParameter("endTime");
String stateType = request.getParameter("stateType");

if(beginTime==null)
{
	beginTime = "";
}
if(endTime == null)
{
	endTime = "";
}
int statetype = stateType==null||stateType.equals("")?1:Integer.parseInt(stateType);
//System.out.println(beginTime+","+endTime+","+stateType);
ArrayList list = articleService.getArticleInfoList(statetype,beginTime,endTime);

if(list.size() == 0 ){
%>
	<script>
		alert("无满足要求的信息.");
		window.close();
	</script>
<%
	return;
}
java.sql.Date bb=new java.sql.Date(System.currentTimeMillis());  
SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//定义日期用格式化工具

int index = 0;//行标记,记录创建到第几行了

HSSFWorkbook wb = new HSSFWorkbook(); 
HSSFSheet s = wb.createSheet();
wb.setSheetName(0, "MySheet");

HSSFHeader header = s.getHeader();

HSSFRow trow=null;
HSSFCell tcell=null;
HSSFCell cell = null;
HSSFFont font = wb.createFont();
HSSFFont font2 = wb.createFont();
HSSFCellStyle cellStyle = wb.createCellStyle();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗                                
font.setFontHeight((short) 380);        // 设置字体大小
font.setFontName("宋体");      // 设置单元格字体   

HSSFCellStyle cellStyle2 = wb.createCellStyle();
font2.setColor(HSSFFont.COLOR_RED); // 设置单元格字体的颜色.


HSSFCellStyle cellStyle1_2 = wb.createCellStyle();

cellStyle1_2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
cellStyle1_2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle1_2.setBorderBottom((short)1);
cellStyle1_2.setBorderLeft((short)1);
cellStyle1_2.setBorderRight((short)1);
cellStyle1_2.setBorderTop((short)1);
cellStyle1_2.setBottomBorderColor(HSSFColor.BLACK.index);

HSSFCellStyle cellStyle1_3 = wb.createCellStyle();

cellStyle1_3.setBorderBottom((short)1);
cellStyle1_3.setBorderLeft((short)1);
cellStyle1_3.setBorderRight((short)1);
cellStyle1_3.setBorderTop((short)1);
cellStyle1_3.setBottomBorderColor(HSSFColor.BLACK.index);

HSSFCellStyle cellStyle1_4 = wb.createCellStyle();
cellStyle1_4.setBorderBottom((short)1);
cellStyle1_4.setBorderLeft((short)1);
cellStyle1_4.setBorderRight((short)1);
cellStyle1_4.setBorderTop((short)1);
cellStyle1_4.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
cellStyle1_4.setBottomBorderColor(HSSFColor.BLACK.index);
 
//创建第一行和第二行的文字信息
/**********************标题第一行***************************/
//创建第一行的标题
int cellIndex = 0;//CELL的位置标记

cellIndex = 0;//CELL的位置标记
trow=s.createRow(index++);//创建行
trow.setHeight((short)400);
tcell=trow.createCell((short)cellIndex++);
tcell.setCellStyle(cellStyle1_2);
setGB2312String(tcell,"序号");

tcell=trow.createCell((short)cellIndex++);
tcell.setCellStyle(cellStyle1_2);
setGB2312String(tcell,Integer.parseInt(stateType)==1?"作者":"出处");

tcell=trow.createCell((short)cellIndex++);
tcell.setCellStyle(cellStyle1_2);
setGB2312String(tcell,"标题");

tcell=trow.createCell((short)cellIndex++);
tcell.setCellStyle(cellStyle1_2);
setGB2312String(tcell,"主栏目");

tcell=trow.createCell((short)cellIndex++);
tcell.setCellStyle(cellStyle1_2);
setGB2312String(tcell,"录入时间");

tcell=trow.createCell((short)cellIndex++);
tcell.setCellStyle(cellStyle1_2);
setGB2312String(tcell,"文章分值");

tcell=trow.createCell((short)cellIndex++);
tcell.setCellStyle(cellStyle1_2);
setGB2312String(tcell,"分值合计1");

 
			
String pName = "";
String equalStr = "";//主判断
int totalScore = 0,loopQuantity = 0;
boolean flag = false;//判断标记
ArticleInfo tempArticleInfo = null;

for(int i=0;i<list.size();i++)
{
	articleInfo = (ArticleInfo)list.get(i);	
	
	
	//如果对比变量为空时进行初始化
	if("".equals(equalStr)){
	//如果类型为1时按作者进行统计和判断
		if(statetype == 1){
				equalStr = articleInfo.getAuthoer();
		}else{
	 		equalStr = articleInfo.getQuote();
		}
	}
	
	//开始循环生成EXCEL行数据
	cellIndex = 0;//初始化CELL下标
	trow=s.createRow(index++);//创建行
	trow.setHeight((short)400);
	tcell=trow.createCell((short)cellIndex++);
	tcell.setCellStyle(cellStyle1_3);
	setGB2312String(tcell,""+(i+1));
	
	tcell=trow.createCell((short)cellIndex++);
	tcell.setCellStyle(cellStyle1_3);
	setGB2312String(tcell,statetype == 1? articleInfo.getAuthoer() : articleInfo.getQuote());//判断是显示作者信息还是出处信息
	
	tcell=trow.createCell((short)cellIndex++);
	tcell.setCellStyle(cellStyle1_3);
	setGB2312String(tcell,articleInfo.getTitle());
	
	tcell=trow.createCell((short)cellIndex++);
	tcell.setCellStyle(cellStyle1_3);
	setGB2312String(tcell,articleInfo.getSiteName());
	
	tcell=trow.createCell((short)cellIndex++);
	tcell.setCellStyle(cellStyle1_3);
	setGB2312String(tcell,dateFormat.format(articleInfo.getRecordDate()));
	
	tcell=trow.createCell((short)cellIndex++);
	tcell.setCellStyle(cellStyle1_3);
	setGB2312String(tcell,""+articleInfo.getScore());
	
	tcell=trow.createCell((short)cellIndex++);
	tcell.setCellStyle(cellStyle1_3);
	setGB2312String(tcell,"");
	
		
		
		tempArticleInfo = null; //每次循环进行初始化，以便知道什么时候到达最后条记录
		flag = false;
		if(i+1 != list.size()){
				tempArticleInfo = (ArticleInfo)list.get(i+1);	
		}
		
		
		//进行统一判断，方便后面使用，简化代码
		if(tempArticleInfo != null){
			if(statetype == 1){
					flag = equalStr.equals(tempArticleInfo.getAuthoer());
			}else{
					flag = equalStr.equals(tempArticleInfo.getQuote());
			}
		}

		//下一条记录不等于当前的记录对比值时进行相应的处理
		if(!flag || tempArticleInfo == null){
				totalScore += articleInfo.getScore();
	 		loopQuantity ++;
				
				//进行跨列处理
				//#########注意：跨行操作时Region(1,2,3,4)第1个值的行号必须要比3位置的行号小，如果大于3就不能正常合并单元格
				 s.addMergedRegion(new Region(index-loopQuantity,(short)(cellIndex-1),index-1,(short)(cellIndex-1)));
					
					//获取最先需要跨的CELL，然后把值加入
				 tcell = s.getRow(index-loopQuantity).getCell((short)(cellIndex-1));
					setGB2312String(tcell,""+totalScore);
					
				
				//清空统计数据
				totalScore = 0;
				loopQuantity = 0;
		}else{
	 		totalScore += articleInfo.getScore();
	 		loopQuantity ++;
		}
	

	
	
}

	//s.addMergedRegion(new Region(1,(short)(0),0,(short)(0)));

	pName="栏目统计表";
	response.reset();
	response.setContentType("application/x-msdownload");
	response.setHeader("Content-Disposition","attachment; filename="+new String(pName.getBytes("gb2312"),"ISO-8859-1")+".xls");
	ServletOutputStream outStream=null;

	try{
		outStream = response.getOutputStream();
		wb.write(outStream);
	}catch(Exception e)
	{
	 e.printStackTrace();
	}finally{
		outStream.close();
	}
%>

评论

2 楼 jateide 2013-10-23  

smile_wangchun 写道

poi3.7合并单元格addMergedRegion过时了，你没发现？   

过时了？那现在用哪个接口？API上没说addMergedRegion被取代了啊

1 楼 smile_wangchun 2012-07-18  

poi3.7合并单元格addMergedRegion过时了，你没发现？