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: