apache POI导出excel文件 及单元格合并 、样式的设置

客户需要从完单物料信息中到处excel
大概思路：
单击某一按钮，触发请求至后台，创建输出流，导出excel ^_^ 
 

前台代码：
（此段代码 注释部分存在一个问题，注释部分的请求无效，后台无法响应前台请求，
参数传过去了，后台也接受了，但输出流没有输出，木鸡why）解决方法，用一个重定向代替这个框架sendRequest发送请求。

isc.IButton.create({
	ID:"excelBtn",
	width:"80",
	height:"25",
	icon:"demoApp/page_excel.png",
	title:"導出excel",
	click:function(){
		var length = DataList.getSelectedRecords().getLength();
    	if(length>0){
			var projEndId = DataList.getSelectedRecord().projEndId;
			window.location='com/el/ProjectEnd/createPeSystemReport.action?projEndId=' + projEndId;
//		    RPCManager.sendRequest({
//		        actionURL:"com/el/ProjectEnd/createPeSystemReport.action",
//		        params:{projEndId:projEndId},
//		        callback:function() {isc.say("報表導入成功");}
//		    });
    	}else{
    		isc.say("請選擇要導出的項目");
    		
    	}
	}
});


后台代码
在web层（controller）写一个方法，在此方法内获取信息数据源，创建输出流，然后调用生成excel的函数，关闭输出流。
//----------導出excel報----------------------------------
	
	@RequestMapping(value = "com/el/ProjectEnd/createPeSystemReport.action")
	public void exportExecl(HttpServletRequest request, HttpServletResponse response) {
		
		int projEndId = Integer.parseInt(request.getParameter("projEndId"));
		
		System.out.println("projEndId--:"+projEndId);
		List<ProjItemSumLine> list = projItemSumLineService.findProjLineByProjEndId(projEndId);
		ProjItemSumHead projItemSumHead = this.projItemSumHeadService.findProjItemSumHeadByProjEndId(projEndId);
		
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment; filename=PEMaterialSummary.xls; target=_blank");
		String ppc_ = "PPC";
		String pmc_ = "PMC";
		String purchaser_ = "Purchaser";//採購
		String engineer_ = "Engineer";
		
		String ppc = this.projItemSumLiabUserService.findProjItemSumLiabUserName(projEndId, ppc_);
		String pmc = this.projItemSumLiabUserService.findProjItemSumLiabUserName(projEndId, pmc_);
		String purchaser = this.projItemSumLiabUserService.findProjItemSumLiabUserName(projEndId, purchaser_);
		String enginer = this.projItemSumLiabUserService.findProjItemSumLiabUserName(projEndId, engineer_);
		
		ExportToExecl ete = new ExportToExecl();
		OutputStream os = ete.exportMaterialSummary(list,projItemSumHead,ppc,pmc,purchaser,enginer, response);
		try {
			if(os != null){
				os.flush();
				os.close();
			}
		} catch (IOException e) {
				e.printStackTrace();
		}
		
	}
}


生成excel的函数：
返回OutputStream类型

package com.el.pe.util.export;

import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.IndexedColors;

import com.el.pe.model.ProjItemSumHead;
import com.el.pe.model.ProjItemSumLine;

public class ExportToExecl {

	public OutputStream exportMaterialSummary(List<ProjItemSumLine> list, ProjItemSumHead projItemSumHead,String ppc,String pmc,String purchaser,String enginer,HttpServletResponse response) {
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet itemSheet = workbook.createSheet("PEMaterialSummary");
		int rownum = 2;// 第二行開始
		String cellTitle[] = {"列名"};
		Map<String, HSSFCellStyle> styles = ExportToExecl.createStyles(workbook);
		
		HSSFRow row_first_header = itemSheet.createRow(rownum++);
		HSSFCell cell_title = null;
		//創建列名
		for (int i = 0; i < cellTitle.length; i++) {
			itemSheet.setColumnWidth(i, 18 * 256);
			row_first_header.setHeight((short) (2 * 356));
			cell_title = row_first_header.createCell(i);
			cell_title.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell_title.setCellStyle(styles.get("cell_header_title"));
			cell_title.setCellValue(cellTitle[i]);
		}
		//合併單元格 四个参数分别为：开始行开始列，结束行结束列
//		itemSheet.addMergedRegion(new CellRangeAddress(0,(short)0,1,(short)0)); 
//		itemSheet.addMergedRegion(new CellRangeAddress((short)1, (short)1, (short)2, (short)2));

		
		//写入数据
		HSSFRow row_data = null;
		HSSFCell cell_data = null;
		for(int j =0;j<list.size();j++){
		 
			for(int i = 0 ;i<cellTitle.length;i++){
		 //此处省略N个字
		}
		try {
			OutputStream os = response.getOutputStream();
			workbook.write(os);
			return os;
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/*----------創建excel styles----------------------------------*/
	private static Map<String, HSSFCellStyle> createStyles(HSSFWorkbook wb) {
		Map<String, HSSFCellStyle> styles = new HashMap<String, HSSFCellStyle>();

		//----------------------標題樣式---------------------------
		HSSFCellStyle cell_header_title = wb.createCellStyle();
		HSSFFont font_header_title = wb.createFont();
		font_header_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗體
		font_header_title.setFontHeight((short) (9 * 20));
		font_header_title.setFontName("Times New Roman");// 字體樣式
		cell_header_title.setFont(font_header_title);
		cell_header_title.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
		cell_header_title.setWrapText(true);
		styles.put("cell_header_title", cell_header_title);
		
		//-----------------------設置字符樣式---------------------------
		
		HSSFCellStyle cell_data_default = wb.createCellStyle();
		HSSFFont font_data_default = wb.createFont();
		font_data_default.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		font_data_default.setFontHeight((short) (8 * 20));
		font_data_default.setFontName("Arial Narrow");// 字體樣式
		cell_data_default.setFont(font_data_default);
		cell_data_default.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 居中
		cell_data_default.setWrapText(true);// 自動換行
		styles.put("cell_data_default", cell_data_default);
		
		
		return styles;
	}
}