poi导出excel，支持合并的单元格

poi导出excel

支持合并的单元格

/**
 * 
 */
package ctsi.pf.business.exportToExcel;


import com.eos.system.annotation.Bizlet;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.Region;


import com.eos.data.xpath.XPathLocator;
import com.eos.foundation.common.io.FileUtil;
import com.eos.foundation.eoscommon.ConfigurationUtil;
import com.eos.runtime.core.ApplicationContext;
import com.eos.system.annotation.Bizlet;
import com.eos.system.annotation.BizletParam;
import com.eos.system.annotation.ParamType;

import commonj.sdo.DataObject;
/**
 * @author majie
 * @date 2012-11-13 13:44:55
 *
 */
@Bizlet("")
public class OrderExportToExcel {

	/**
	 * @param args
	 * @author majie
	 * @throws IOException 
	 */
	private static HSSFCellStyle cellStyleTXT;
	private static HSSFCellStyle cellStyleData;
	private static DataObject[] exportObjects;
	private static HSSFSheet sheetFirst;
	private static HSSFSheet sheetSecond;
	@Bizlet("测试")
	public static void exportToExcel(DataObject[] exportObjectsIn) throws IOException{
		exportObjects=exportObjectsIn;
		
		//文件名
		String filename="test1";
		if(filename.indexOf(".xls")==-1){
			filename+=".xls";
		}
		
		//输出目录
		String tempDir="c:/"+"temp/";
		File file=new File(tempDir);
		if(!file.exists()){
			//创建临时目录
			FileUtil.mkDir(tempDir);
			//file.createNewFile();
		}
		//生成完整文件名+路径
		
		String outputFile=tempDir+generateOutputExcelFile(filename);
        // 声明一个工作薄  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        // 生成两个sheet 
        HSSFSheet sheet1 = workbook.createSheet("订单基本信息");  
        HSSFSheet sheet2 = workbook.createSheet("订单内容信息"); 
        sheetFirst=sheet1;
        sheetSecond=sheet2;
        sheet1.setColumnWidth((short)0,(short)4000);
        sheet1.setColumnWidth((short)1,(short)8000);
        sheet1.setColumnWidth((short)2,(short)4000);
        sheet1.setColumnWidth((short)3,(short)8000);
        
		//生成sheet1左边文字样式
		HSSFCellStyle StyleTXT=workbook.createCellStyle();
		StyleTXT.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		StyleTXT.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		StyleTXT.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
		StyleTXT.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
		StyleTXT.setBorderRight(HSSFCellStyle.BORDER_THIN);  
		StyleTXT.setBorderTop(HSSFCellStyle.BORDER_THIN);        
		cellStyleTXT=StyleTXT;
		
		//生成sheet1数据样式
		HSSFCellStyle StyleData=workbook.createCellStyle();
		StyleData.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		StyleData.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		StyleData.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
		StyleData.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
		StyleData.setBorderRight(HSSFCellStyle.BORDER_THIN);  
		StyleData.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyleData=StyleData;
//		System.out.println("233");
        int startRow=0;
        
        
        //设置行数列数
        int column=4,row=13;

        String[][] datasImportFormat={
        		{"定单流水号:","#_formCode","单据类型:","#_orderProcessType"},
        		{"合同编号:","#_contractCode","单据类型:","#_acceptType"},
        		{"受理运营商:","#_acceptCompany","受理部门:","#_businessCreateAreaName"},
        		{"受理类型:","#_busiAcceptType","申请时间:","#_applyDate2"},
        		{"申请人:","#_applyPerson2","发起地客户经理:","#_initiateCustomerManager"},
        		{"发起地业务经理:","#_initiateBusinessManager","集团国际业务支撑经理:","#_groupInterBusinessManager"},
        		{"海外公司业务经理:","#_abroadBusinessManager","集团业务经理:","#_groupBusinessManager"},
        		{"付费方式:","#_orderFeetype","付费客户:","#_payCustomerNo"},
        		{"付费客户名称:","#_payCustomerName","客户名称:","#_consumerName"},
        		{"客户编号:","#_consumerId","客户联系人:","#_consumerPerson"},
        		{"联系电话:","#_consumerTel","客户地址:","#_consumerAddr"},
        		{"Email:","#_consumerEmail","账户名称:","#_null"},
        		{"备注","$_MR3$#_orderNote"}
        	};
        //格式说明：  $_MR3$ 合并3个单元格（合并多少个就写多少个数字）
        //			#_加实体的字段名
        //			如果为字串则直接写入
        //计算总元素数
        int count=0;
        for(int i=0;i<row;i++)
        	count+=datasImportFormat[i].length;
        
//        System.out.println(datasImportFormat.length+"");
        for(int i=0;i<row;i++){
            HSSFRow rowData=sheet1.createRow(startRow++);
            rowData.setHeight((short) 800);            
        	for(int j=0;j<column;j++){
        		//写入单元格
        		if((count--)>0)
        			creatCell(rowData,j,datasImportFormat[i][j],sheet1);
        	}
        	
        }
               
        //生成excel文件
        FileOutputStream fileOut = new FileOutputStream(outputFile);
        workbook.write(fileOut);
        fileOut.close();
        
	}
	
	
	/**
	 * 生成单元格，套用格式
	 * @param HSSFRow row,int cellIndex,String strIn,HSSFSheet sheet
	 * @return
	 */
	private static void creatCell(HSSFRow row,int cellIndex,String strIn,HSSFSheet sheet){
		HSSFCell cell=row.createCell((short)cellIndex); 
		Object str;
		int MergedRegionNum=0;//合并单元格的数量
		
		//处理合并的单元格
		if(strIn.startsWith("$_MR")){
			int nextStart=strIn.indexOf("$", 3);	
			MergedRegionNum=Integer.parseInt(strIn.substring(4,nextStart));
			strIn=strIn.substring(nextStart+1);
			sheet.addMergedRegion(new Region(row.getRowNum(),(short)cellIndex,row.getRowNum(),(short)(cellIndex+MergedRegionNum-1)));
		}
		
		//处理格式
		if(strIn.startsWith("#_")){
			str=XPathLocator.newInstance().getValue(exportObjects[0],strIn.substring(2));
			cell.setCellStyle(cellStyleData);
			if(MergedRegionNum>1){
				for(int i=1;i<MergedRegionNum;i++){
					row.createCell((short)(i+cellIndex));
					row.getCell((short)(i+cellIndex)).setCellStyle(cellStyleData);
				}
			}				
		}else{
			str=strIn;
			cell.setCellStyle(cellStyleTXT);

			if(MergedRegionNum>1){
				for(int i=1;i<MergedRegionNum;i++){
					row.createCell((short)(i+cellIndex));
					row.getCell((short)(i+cellIndex)).setCellStyle(cellStyleTXT);
				}
			}
		}
		
		//判断数据类型写入单元格类型
		if(str!=null){
			if(str instanceof Double|| str instanceof BigDecimal){
				cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(Double.parseDouble(str.toString()));
			}else{
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(str.toString()));
			}
		}else{
			cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
		}
	}
	
	/**
	 * 生成EXCEL输出文件，默认带时间戳
	 * @param templateFilename 文件名
	 * @return
	 */
	private static String generateOutputExcelFile(String templateFilename){
		String filename=templateFilename;
		if(templateFilename.endsWith(".xls")){
			filename=templateFilename.substring(0,templateFilename.length()-4);
		}

		SimpleDateFormat format=new SimpleDateFormat("yyyyMMddHHmmss");
		String datetimeString=format.format(new Date());

		filename=filename+"_"+datetimeString+".xls";
		return filename;
	}
	
}