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();
    }
%>

new Region 这个已经过时了，现在是用addMergedRegion(new CellRangeAddress(row,row,col,col);