导出数据生成Excel文件

jsp：

<a id="excelExport"></a>

 js：

var excel=function(){
	var node = $('#groupConfig').tree('getSelected');
	if(node!=null)
		var groupConfigSearch =node.id;
	else node=null;
	
	var data = {
			orgCode:groupConfigSearch,
			helpType:$("#searchType").combobox('getValue'),
			name:$("#searchName").val(),
			youthTel:$("#searchTel").val() 
	};
	
	$.ajax({
		url:basepath+'/hk/importExcel.do', 
		type:'POST',
		dataType:'json',
		data:data,
		success:function(data) {
			console.log(data);
			console.log(data.data);
			console.log(data.success);
			$("#excelExport").attr("href",data.data);
			document.getElementById("excelExport").click();
			$('#searchDiv').form('clear');
	}
	});

}

 sql：

 <select id="getExcel" parameterClass="hk" resultClass="com.fjxhx.business.people.model.HkModel" >
			select id,bankId,youthName,youthTel,
		projectName,appropriationDate,appropriationMoney,subsidizeMonth,firstDate,everyMoney,isCountryOrg,
		monthlyMoney,sumTheoryMoney,sumRealMoney,sumBalance,notOntime,youthId,
		(select b.value from b_status b where b.content=a.helpType and b.code='helpType') as helpType,
		(select b.value from b_status b where b.content=a.projectPlace and b.code='projectPlace') as projectPlace,
		(select c.orgName from ybc_org c where c.orgCode=a.orgCode) as orgCode
		from ybc_hk a where 1=1 
		<dynamic>
			<isNotEmpty prepend="and" property="orgCode">
				orgCode like '%$orgCode$%'
			</isNotEmpty>
			<isNotEmpty prepend="and" property="helpType">
				helpType = #helpType#
			</isNotEmpty>
			<isNotEmpty  prepend="and " property="youthName">
				youthName like concat('%', #youthName#, '%')
			</isNotEmpty>

			<isNotEmpty prepend="and" property="youthTel">
				youthTel like concat('%', #youthTel#, '%')
			</isNotEmpty>
		</dynamic>
		ORDER BY id DESC
	  </select>

 action：

public void importExcel(){
		try {
			BaseDao dao = getDao();
			List<Object> list = dao.selectForList("hk.getExcel", model);
			String basepath=ServletActionContext.getServletContext().getRealPath("templateFile");
			String s1=basepath+"/hk.xls";//模板
			String s2=basepath+"/hkTemp.xls";
			ExcelUtil.exportToExcel(list, "第一页",s1,s2);
			this.setData("servlet/downloadServlet?path="+s2);
			this.setSuccess(true);
			this.send();
		} catch (Exception e) {
			e.printStackTrace();
			this.setData("操作失败");
			this.setSuccess(false);
			this.send();
		}
	}
	

 

servelt：

package com.fjxhx.business.system.servlet;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class downloadServlet extends HttpServlet {

	/**
	 * Constructor of the object.
	 */
	public downloadServlet() {
		super();
	}

	/**
	 * Destruction of the servlet. <br>
	 */
	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	/**
	 * The doGet method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		
		this.doPost(request, response);
	}

	/**
	 * The doPost method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to post.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String path=request.getParameter("path");
		/**
		 * 如果下载成功删除模版文件
		 */
		System.out.println(path);
		if(download(path, response))
			deleteFile(path);
	}
	
	public static boolean deleteFile(String fileName) {  
		  File file = new File(fileName);  
		  // 如果文件路径所对应的文件存在，并且是一个文件，则直接删除  
		  if (file.exists() && file.isFile()) {  
		   if (file.delete()) {  
		    System.out.println("删除单个文件" + fileName + "成功！");  
		    return true;  
		   } else {  
		    System.out.println("删除单个文件" + fileName + "失败！");  
		    return false;  
		   }  
		  } else {  
		   System.out.println("删除单个文件失败：" + fileName + "不存在！");  
		   return false;  
		  }  
		 }  

	/**
	 * Initialization of the servlet. <br>
	 *
	 * @throws ServletException if an error occurs
	 */
	public void init() throws ServletException {
		// Put your code here
	}
	
	private boolean download(String path, HttpServletResponse response) {
		boolean flag=false;
		try {
			// path是指欲下载的文件的路径。
			File file = new File(path);
			// 取得文件名。
			String filename = file.getName();
			// 以流的形式下载文件。
			InputStream fis = new BufferedInputStream(new FileInputStream(path));
			byte[] buffer = new byte[fis.available()];
			fis.read(buffer);
			fis.close();
			// 清空response
			response.reset();
			// 设置response的Header
			response.addHeader("Content-Disposition", "attachment;filename="
					+ new String(filename.getBytes()));
			response.addHeader("Content-Length", "" + file.length());
			OutputStream toClient = new BufferedOutputStream(
					response.getOutputStream());
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			toClient.write(buffer);
			toClient.flush();
			toClient.close();
			flag=true;
		} catch (IOException ex) {
			ex.printStackTrace();
		}
		return flag;
	}


}

 web.xml

<servlet-mapping>
		<servlet-name>downloadServlet</servlet-name>
		<url-pattern>/servlet/downloadServlet</url-pattern>
</servlet-mapping>

<servlet>
    <description>This is the description of my J2EE component</description>
    <display-name>This is the display name of my J2EE component</display-name>
    <servlet-name>downloadServlet</servlet-name>
    <servlet-class>com.fjxhx.business.system.servlet.downloadServlet</servlet-class>
 </servlet>

 

模板:

中文

model里的属性

 

jar：