springMVC下导出Excel功能

1、ExcleUtils.java代码：

public class ExcelUtils {
	//excel默认宽度；
	private static int width = 256*14;
	//默认字体
	private static String excelfont = "微软雅黑";
	
	/**
	 * 
	 * @param excelName  导出的EXCEL名字
	 * @param sheetName  导出的SHEET名字  当前sheet数目只为1
	 * @param headers      导出的表格的表头
	 * @param ds_titles      导出的数据 map.get(key) 对应的 key
	 * @param ds_format    导出数据的样式
	 *                          1:String left; 
	 *							2:String center   
	 *							3:String right
	 *							4 int  right
     *							5:float ###,###.## right 
 	 *							6:number: #.00% 百分比 right
	 * @param widths      表格的列宽度  默认为 256*14
	 * @param data        数据集  List<Map>
	 * @param response
	 * @throws IOException
	 */
	public static void export(String excelName, String sheetName,String[] headers,String[] ds_titles,int[] ds_format,int[] widths, List<Map<String,Object>> data ,HttpServletRequest request, HttpServletResponse response) throws IOException {
		HttpSession session = request.getSession();  
	    session.setAttribute("state", null);  
		if(widths==null){
        	  widths = new int[ds_titles.length];
        	  for(int i=0;i<ds_titles.length;i++){
        		  widths[i]=width;
        	  }
          }
		  if(ds_format==null){
			  ds_format = new int[ds_titles.length];
			  for(int i=0;i<ds_titles.length;i++){
				  ds_format[i]=1;
        	  }
		  }
		   //设置文件名
			String fileName = "";
			if(StringUtils.isNotEmpty(excelName)){
				fileName = excelName;
			}
	        //创建一个工作薄
	        HSSFWorkbook wb = new HSSFWorkbook();
	        //创建一个sheet
	        HSSFSheet  sheet = wb.createSheet(StringUtils.isNotEmpty(sheetName)?sheetName:"excel");
	        //创建表头，如果没有跳过
	        int headerrow = 0;
	        if(headers!=null){
	        	HSSFRow  row = sheet.createRow(headerrow);
	        	//表头样式
	        	HSSFCellStyle style = wb.createCellStyle();  
	        	HSSFFont font = wb.createFont();
	        	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	        	font.setFontName(excelfont);
	        	font.setFontHeightInPoints((short) 11);
	        	style.setFont(font);
		        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
		        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	        	 for (int i = 0; i < headers.length; i++) {  
	        		sheet.setColumnWidth((short)i,(short)widths[i]); 
	 	            HSSFCell cell = row.createCell(i);  
	 	            cell.setCellValue(headers[i]);  
	 	            cell.setCellStyle(style);  
	 	        }  
	        	headerrow++;
	        }
	        //表格主体  解析list
	        if(data != null){
	        	List styleList = new ArrayList();
	        	
	        	for (int i = 0; i <ds_titles.length; i++) {  //列数
	        		HSSFCellStyle style = wb.createCellStyle();  
		        	HSSFFont font = wb.createFont();
		        	font.setFontName(excelfont);
		        	font.setFontHeightInPoints((short) 10);
		        	style.setFont(font);
			        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			    	if(ds_format[i]==1){
				        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);  
			    	}else if(ds_format[i]==2){
				        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
			    	}else if(ds_format[i]==3){
			    		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); 
			    		 //int类型
			    	}else if(ds_format[i]==4){
			    		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); 
			    		 //int类型
						style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));	 
			    	}else if(ds_format[i]==5){
			    		//float类型
			    		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); 
			    		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));	
			    	}else if(ds_format[i]==6){
			    		//百分比类型
			    		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); 
			    		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));	
			    	}
			        styleList.add(style);
				}
	        	for (int i = 0; i < data.size() ; i++) {  //行数
	        		HSSFRow  row = sheet.createRow(headerrow);
	        		Map map = data.get(i);
					for (int j = 0; j <ds_titles.length; j++) {  //列数
						 HSSFCell cell = row.createCell(j);  
						 Object o = map.get(ds_titles[j]);
						 if(o==null||"".equals(o)){
							 cell.setCellValue("");
						 }else if(ds_format[j]==4){
							 //int
							 cell.setCellValue((Long.valueOf((map.get(ds_titles[j]))+"")).longValue()); 
					     }else if(ds_format[j]==5|| ds_format[j]==6){
					    	 //float
					    	 cell.setCellValue((Double.valueOf((map.get(ds_titles[j]))+"")).doubleValue()); 
					     }else {
					    	 cell.setCellValue(map.get(ds_titles[j])+""); 
					     }
			 	         
			 	         cell.setCellStyle((HSSFCellStyle)styleList.get(j));  
					}
					headerrow++;
				}
	        }
	       
			fileName=fileName+".xls";
			String filename = "";
			try{
			   filename =encodeChineseDownloadFileName(request,fileName);
			}catch(Exception e){
				e.printStackTrace();
			}
//			final String userAgent = request.getHeader("USER-AGENT");
//            if(userAgent.indexOf( "MSIE")!=-1){//IE浏览器
//            	filename = URLEncoder.encode(fileName,"UTF8");
//            }else if(userAgent.indexOf( "Mozilla")!=-1){//google,火狐浏览器
//            	filename = new String(fileName.getBytes(), "ISO8859-1");
//            }else{
//            	filename = URLEncoder.encode(fileName,"UTF8");//其他浏览器
//            }
			
			response.setHeader("Content-disposition", filename);
		    response.setContentType("application/vnd.ms-excel");  
	        response.setHeader("Content-disposition", "attachment;filename="+filename);  
	    	response.setHeader("Pragma", "No-cache");
	        OutputStream ouputStream = response.getOutputStream();  
	        wb.write(ouputStream);  
	        ouputStream.flush();  
	        ouputStream.close();
	        session.setAttribute("state", "open");
	        
	}
	
    /** 
     * 对文件流输出下载的中文文件名进行编码 屏蔽各种浏览器版本的差异性 
     * @throws UnsupportedEncodingException  
     */  
    public static String encodeChineseDownloadFileName(  
            HttpServletRequest request, String pFileName) throws Exception {  
          
         String filename = null;    
            String agent = request.getHeader("USER-AGENT");    
            if (null != agent){    
                if (-1 != agent.indexOf("Firefox")) {//Firefox    
                    filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8"))))+ "?=";    
                }else if (-1 != agent.indexOf("Chrome")) {//Chrome    
                    filename = new String(pFileName.getBytes(), "ISO8859-1");    
                } else {//IE7+    
                    filename = java.net.URLEncoder.encode(pFileName, "UTF-8");    
                    filename = filename.replace("+", "%20");
                }    
            } else {    
                filename = pFileName;    
            }    
            return filename;   
    }  
}

2、前端调用代码：

$("#exportExcel").click(function(){
		var url = "${ctx}/tradeFlow/exprotTradeFlowList";
	     $('#searchform').attr('action', url);
	     $('#searchform').submit();
	});

3、action中代码（springMVC）：

@RequestMapping("/exprotAccountList")
	public void exportExcel(HttpSession session,HttpServletRequest request,HttpServletResponse response){
		String[] excelHeader = { "账户号", "账户类型", "账户名称"};
		String[] ds_titles = { "accountId", "accountType", "accountNm"};
		int[] ds_format = { 1, 1, 1};
		try{
			flowList = respDto.getAccountFlowList();
			List<Map<String,Object>> data = new ArrayList<Map<String,Object>>();
			if(CollectionUtils.isNotEmpty(flowList)){
				for(MsAccountFlowDTO order : flowList){
					Map<String,Object> map = new HashMap<String,Object>();
					map.put("accountId", order.getAccountId());
					map.put("accountType", accountTypeStr);
					map.put("accountNm", order.getAccountNm());
					data.add(map);
				}
			}
			ExcelUtils.export("账户表", "账户", excelHeader, ds_titles, ds_format, null, data, request, response);
		}catch(Exception e){
			logger.error("导出文件异常", e);
		}
		
	}