SSM的EXCEL导出

1.前台请求

//导出EXCEL
	function getOut(){
		parent.sessionLive(); 
		var kpyf = $("#month").val();
		if(kpyf==''){
			layui.use('layer', function(){
			    var layer = layui.layer;
			    layer.msg('请选择月份', {
			       time: 1000, //1s后自动关闭
			    });
			}); 
			return false;
		}
		layui.use('layer', function(){
		    var layer = layui.layer;
		    layer.open({
		    	title: '按月份导出EXCEL'
		    	,content: '确定导出EXCEL？'
		    	,area: ['400px', '200px']
				,btn:['确定','取消']
		    	,yes: function(){
		    		layui.use('layer', function(){
					    var layer = layui.layer;
					    layer.msg('加载中', {
					       time: 1000, //1s后自动关闭
					    });
					}); 
		    		var NSRMC = $("#gsNameShow").html();
		    		window.location.href=
		  			'<%=basePath%>pc/excel.do?'+Date.parse(new Date())+
		  			'&kpyf='+kpyf+'&NSRMC='+encodeURI(encodeURI(NSRMC));
		      	}
		    	,btn2: function(){
		        	//return false;
		      	}
		    });   
		}); 
	}

 

2.后台接收

/**
	 * 导出台账数据到excel
	 * @return
	 * @throws Exception
	 */
	@RequestMapping("/pc/excel")
	@ResponseBody
	public ModelAndView excel(HttpServletRequest request) throws Exception{
		 ModelAndView mv = this.getModelAndView();  
	     SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM");
	     PageData pd = this.getPageData();
	     HashMap data = new HashMap();
	     String KPRQ = pd.getString("kpyf");
	     if(null != KPRQ && !"".equals(KPRQ)){
	    	KPRQ = KPRQ.trim();
			//Date kpyf=sdf.parse(KPRQ);
			data.put("kpyf", KPRQ);
	     }
	     String nsr = request.getSession().getAttribute("NSRSession").toString();
	     String[] nsrStrArr = nsr.split(",");
	     data.put("SHXYDM", nsrStrArr[0]);
	     data.put("NSRSBH", nsrStrArr[1]);
	     Map<String,Object> dataMap = new HashMap<String,Object>(); 
	     String NSRMC = new String(pd.getString("NSRMC").getBytes("ISO8859-1"), "UTF-8");
	     NSRMC = java.net.URLDecoder.decode(NSRMC,"UTF-8");
	     dataMap.put("NSRMC", NSRMC);
	     List<String> titles = new ArrayList<String>();  
	     titles.add("序号");
	     titles.add("发票代码");
	     titles.add("发票号码");
	     titles.add("开票日期");
	     titles.add("确定勾选日期");
	     titles.add("发票金额");
	     titles.add("发票税额");
	     titles.add("销方名称");
	     dataMap.put("titles", titles);  
	     //根据条件数据得到数据
	     List<HashMap> createCountList = createCountService.searchByList(data); 
	     List<HashMap> varList = new ArrayList<HashMap>();  
	     
	     if(createCountList!=null&&createCountList.size()>0){
	    	 Integer n=1;
	    	 for (HashMap p:createCountList){
	    		 HashMap sheetData = new HashMap();
	    		 sheetData.put("var1", n.toString()); n++;
	         	 sheetData.put("var2", p.get("FPDM")==null?"":p.get("FPDM").toString());
	         	 sheetData.put("var3", p.get("FPHM")==null?"":p.get("FPHM").toString());
	         	 sheetData.put("var4", p.get("RQ")==null?"":p.get("RQ").toString());
	         	 sheetData.put("var5", p.get("RZSJ")==null?"":p.get("RZSJ").toString());
	         	 sheetData.put("var6", p.get("JE")==null?"":p.get("JE").toString());
	         	 sheetData.put("var7", p.get("SE")==null?"":p.get("SE").toString());
	         	 sheetData.put("var8", p.get("XFMC")==null?"":p.get("XFMC").toString());
	    		 varList.add(sheetData);
	     }
	}
	     dataMap.put("varList", varList);  
	     ObjectExcelView erv = new ObjectExcelView();  
	     mv = new ModelAndView(erv,dataMap);  
	     return mv;   
	}

 

3.工具类

package com.taxsearch.entity;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
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.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

/**
* 导入到EXCEL
* 类名称：ObjectExcelView.java
* 类描述： 
* @author FH
* 作者单位： 
* 联系方式：
* @version 1.0
 */
public class ObjectExcelView extends AbstractExcelView{

	@Override
	protected void buildExcelDocument(Map<String, Object> model,
			HSSFWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		// TODO Auto-generated method stub
		String file = (String) model.get("NSRMC");
		SimpleDateFormat df = new SimpleDateFormat("yyyyMMddhh24mmss");// 设置日期格式
		String date = df.format(new Date());// new Date()为获取当前系统时间
		String filename = file+date;
		HSSFSheet sheet;
		HSSFCell cell;
		response.setContentType("application/octet-stream");
		response.setHeader("Content-Disposition", "attachment;filename="+java.net.URLEncoder.encode(filename, "UTF-8")+".xls");
		sheet = workbook.createSheet("sheet1");
		
		List<String> titles = (List<String>) model.get("titles");
		int len = titles.size();
		HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
		headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		HSSFFont headerFont = workbook.createFont();	//标题字体
		headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		headerFont.setFontHeightInPoints((short)11);
		headerStyle.setFont(headerFont);
		short width = 20,height=25*20;
		sheet.setDefaultColumnWidth(width);
		for(int i=0; i<len; i++){ //设置标题
			String title = titles.get(i);
			cell = getCell(sheet, 0, i);
			cell.setCellStyle(headerStyle);
			setText(cell,title);
		}
		sheet.getRow(0).setHeight(height);
		
		HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
		contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		List<HashMap> varList = (List<HashMap>) model.get("varList");
		int varCount = varList.size();
		for(int i=0; i<varCount; i++){
			HashMap vpd = varList.get(i);
			for(int j=0;j<len;j++){
				String varstr = vpd.get("var"+(j+1)).toString() != null ? vpd.get("var"+(j+1)).toString() : "";
				cell = getCell(sheet, i+1, j);
				cell.setCellStyle(contentStyle);
				setText(cell,varstr);
			}
			
		}
		
	}

}