poi 导出，将数据库中的数据读取到excel 表格中

项目用的框架springMVC

poi导出 前端代码如下：

<button class=\"btn btn-primary\"   onclick=\"ToExcel(this)\">导出</button>

function ToExcel(t){
	window.location.href="${ctx}/main/getExcel;
};

Controller层代码

@ResponseBody
	@RequestMapping("/getExcel")
	public void getExcel( HttpServletRequest request,HttpServletResponse response){
		try {
			ToExcel.getExcel();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

在业务层首先创建两个集合以方便存取数据，在方法中闲设置表格样式，再设置自己需要的表格宽度，我这里用的是四个宽度的表格，再将查询的数据插入到表格中就可以直接导出自己需要的表格数据

业务层代码：

	private static Set<MixDef> NSmix = new LinkedHashSet<MixDef>();
	private static List<MixDef> mixs = new ArrayList<MixDef>();
	public static void getExcel(String org_id,String org_name,String dim1,HttpServletRequest request,HttpServletResponse response) throws Exception {
		NSmix.clear();
		mixs.clear();
		//创建excel文档
		HSSFWorkbook wb = new HSSFWorkbook();
		//创建一个excel标签页
		HSSFSheet sheet = wb.createSheet();
		//设置样式
		HSSFCellStyle style = wb.createCellStyle();  
		style.setLocked(false);//可编辑
		
	    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
	    style.setWrapText(true);//自动换行
	    style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
	    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
	    style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
	    style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
	    
	    HSSFCellStyle style2 = wb.createCellStyle();  
	    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
	    style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
	    style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
	    style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
	    style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
	    style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
	    
	    HSSFCellStyle style3 = wb.createCellStyle();
	    style3.setWrapText(true);//自动换行
	    style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
	    style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
	    style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
	    style3.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
	    style3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
	    
	    HSSFCellStyle style4 = wb.createCellStyle();  
	    style4.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
	    style4.setWrapText(true);//自动换行
	    style4.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
	    style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
	    style4.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
	    style4.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
	    
	    HSSFFont font = wb.createFont();
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
	    font.setFontHeightInPoints((short) 12);
	    style.setFont(font);
	    
	    HSSFFont font0 = wb.createFont();
	    font0.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
	    font0.setFontHeightInPoints((short) 16);
	    style4.setFont(font0);
	    
	    
	    //列宽
	    sheet.setColumnWidth(0, 3000);
	    sheet.setColumnWidth(1, 9000);
	    sheet.setColumnWidth(2, 9000);
	    sheet.setColumnWidth(3, 8000);
  
	    CellRangeAddress region = new CellRangeAddress(0,0,0,3);
	    sheet.addMergedRegion(region);
	    
	    HSSFRow row0 = sheet.createRow(0);
	    row0.setHeightInPoints(30);
	    //创建列
	    HSSFCell cell0 = row0.createCell(0);
	    //写入内容
	    String times=new SimpleDateFormat("yyyy").format(new Date());
	    
	    cell0.setCellValue("南山医院"+times+"年度综合目标管理责任书－"+org_name+"");
	    //设置样式
	    cell0.setCellStyle(style4);
	    
	    //创建第二行 放表头
	    HSSFRow row = sheet.createRow(1);
	    row.setHeightInPoints(20);
	    //创建列
	    HSSFCell cell = row.createCell(1);
	    
	    cell = row.createCell(0);
	    cell.setCellValue("序号");
	    //设置样式
	    cell.setCellStyle(style);
	    
	    cell = row.createCell(1);
	    cell.setCellValue("指标名称");  
	    cell.setCellStyle(style);
	    
	    cell = row.createCell(2);
	    cell.setCellValue("考核办法");  
	    cell.setCellStyle(style);
	    
	    cell = row.createCell(3);
	    cell.setCellValue("指标说明");  
	    cell.setCellStyle(style);

	    //查数据
	    String sql="select D.kpi_id,D.kpi_name,D.method,D.score,D.des,L.kpi_dim_value_name,D.parameter,V.score,D.visible,D.depth from "+TableName.kpi_def+" D left join "+TableName.org_assess_rel+" R on D.kpi_id=R.kpi_id left join "+TableName.kpi_dim_value+" L on R.assess_id=L.kpi_dim_value left join "+TableName.average_value+" V on R.kpi_id=V.kpi_id and V.dim1='"+dim1+"' where R.org_id="+org_id+" and R.status=1 order by D.par_kpi_id";
	    List<Object[]> list = DataUtil.query(GwnBaseDefaultData.sqlName, sql);
            读取list集合内容，通过for循环取出数据
             for(int j=0;j<list.size();j++){
			MixDef mix = new MixDef();
				if(list.get(j)[0]!=null){
					mix.setId(list.get(j)[0].toString());		//kpi_id
				}else{
					mix.setId("");
				}
				if(list.get(j)[1]!=null){
					mix.setName(list.get(j)[1].toString());		//kpi_name
				}else{
					mix.setName("");
				}
				if(list.get(j)[2]!=null){
					mix.setBenefit(list.get(j)[2].toString());	//par_kpi_name(用实体类MixDef中的Benefit存储par_kpi_id_name)
				}else{
					mix.setBenefit("");
				}
				if(list.get(j)[3]!=null){
					mix.setDetail(list.get(j)[3].toString());	//detail
				}else{
					mix.setDetail("");
				}
			 NSmix.add(mix);
		}
	    Iterator<MixDef> it = NSmix.iterator();
		while (it.hasNext()) {
			mixs.add(it.next());
		}

	    //将数据写入
		for (int i = 0; i < mixs.size(); i++) {
			 row = sheet.createRow(i + 2);  
			 //序号
			 cell=row.createCell(0);
			 cell.setCellValue(i+1);
			 cell.setCellStyle(style2);

			 cell= row.createCell(1);
			 cell.setCellValue(mixs.get(i).getName());
			 cell.setCellStyle(style2);
			 cell.setCellStyle(style3);
		 
			 cell=row.createCell(2);
			 cell.setCellValue(mixs.get(i).getBenefit());
			 cell.setCellStyle(style3);
			 
			 cell=row.createCell(3);
			 cell.setCellValue(mixs.get(i).getDepth());
			 cell.setCellStyle(style3);
			 
		}
		
		String time= new SimpleDateFormat("yyyy-MM-dd_HH：mm").format(new Date());  
		String filename=""+org_name+"-绩效考核_"+time+".xls";
		OutputStream output = response.getOutputStream();
		response.reset();
		response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("gb2312"), "ISO8859-1"));
		response.setContentType("application/msexcel");
		wb.write(output);
		output.close();
	}