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;}}