一.建立下載Excel File頁面(downloadExcel.jsp):
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'downloadExcel.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<s:a href="generateExcel.action">下載使用者基本資料到Excel File.</s:a><br>
</body>
</html>
二、DAO for 產生Excel File，如下所示：
1.建立User bean(User.java):
package com.bean.user;
public class User {
private String account;
private String password;
private String firstname;
private String lastname;
private String email;
public String getAccount() {
return account;
}
public void setAccount(String account) {
this.account = account;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getFirstname() {
return firstname;
}
public void setFirstname(String firstname) {
this.firstname = firstname;
}
public String getLastname() {
return lastname;
}
public void setLastname(String lastname) {
this.lastname = lastname;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
2.建立Login DAO(LoginDAO.java):
package com.dao.user;
import com.bean.user.User;
import java.util.List;
public interface LoginDAO {
public User findByProperty(String[] columnNames, String[] values);
public List<User> findAll();
}
3.建立LoginDAO Implement(LoginDAOImp.java):
package com.dao.impl.user;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.bean.user.User;
import com.dao.user.LoginDAO;
public class LoginDAOImpl extends HibernateDaoSupport implements LoginDAO {
private static final Log log = LogFactory.getLog(LoginDAOImpl.class);
@SuppressWarnings("unchecked")
public User findByProperty(String[] columnNames, String[] values) {
User user = null;
log.debug("finding by Names of tbl_user with native sql");
Session session = this.getSession();
try {
String queryString = "select model.account,model.password,model.firstname,model.lastname,model.email from cmsdb.tbl_user model where";
for(int i=0;i<columnNames.length;i++){
queryString+=" model."+columnNames[i]+"=? and";
}
queryString=StringUtils.removeEnd(queryString, "and");
Query queryObject = session.createSQLQuery(queryString);
for (int i = 0; i < values.length; i++) {
queryObject.setParameter(i, values[i]);
}
List<User> list = queryObject.setResultTransformer(Transformers.aliasToBean(User.class)).list();
if(list.size()>0){
user = (User)list.get(0);
}
log.debug("find by property Names successful");
return user;
} catch (RuntimeException re) {
log.error("find by property Names failed", re);
throw re;
}finally{
session.close();
}
}
@SuppressWarnings("unchecked")
public List<User> findAll() {
log.debug("finding all of tbl_user with native sql");
Session session = this.getSession();
try {
String queryString = "select model.account,model.password,model.firstname,model.lastname,model.email from cmsdb.tbl_user model";
Query queryObject = session.createSQLQuery(queryString);
List<User> list = queryObject.setResultTransformer(Transformers.aliasToBean(User.class)).list();
log.debug("find all successful");
return list;
} catch (RuntimeException re) {
log.error("find all failed", re);
throw re;
}finally{
session.close();
}
}
}
4.於WEB-INF\applicationContext.xml加入DAO設定：
<!-- DAO --> <bean id="loginDAO" class="com.dao.impl.user.LoginDAOImpl"> <property name="sessionFactory" ref="sessionFactory"></property> </bean>
三、Service for login建立，如下所示：
1.建立Login Service(LoginService.java):
package com.service.user;
import java.io.InputStream;
import java.util.List;
import com.bean.user.User;
public interface LoginService {
public User findAccount(String[] columnNames, String[] values);
public List<User> findAll();
public InputStream getInputStream();
}
2.建立Login Service Implement(LoginServiceImpl.java):
package com.service.impl.user;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.bean.user.User;
import com.dao.user.LoginDAO;
import com.service.user.LoginService;
public class LoginServiceImpl implements LoginService {
private LoginDAO loginDao;
public User findAccount(String[] columnNames, String[] values) {
return this.loginDao.findByProperty(columnNames, values);
}
public List<User> findAll() {
return this.loginDao.findAll();
}
public InputStream getInputStream() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell((short)0);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("帳號");
cell = row.createCell((short)1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("姓氏");
cell = row.createCell((short)2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("名字");
cell = row.createCell((short)3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("電子郵件信箱");
List<User> list = this.findAll();
for(int i=0;i<list.size();i++){
User user = list.get(i);
row = sheet.createRow(i+1);
cell = row.createCell((short)0);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(user.getAccount());
cell = row.createCell((short)1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(user.getFirstname());
cell = row.createCell((short)2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(user.getLastname());
cell = row.createCell((short)3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(user.getEmail());
}
//直接將Excel文件放進InputStream中
//此方法最好，在Applicaton Server上不會產生Excel檔案
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
return is;
// //此方法會在Applicaton Server(Tomcat)上產生一個Excel文件，需定時去刪除此文件
// //此方法不是很好
//解決文件刪除之方法有兩種：
//1.直接使用另一個Thread來刪除文件，如下如示
//2.寫一個DeleteFileServlet來刪除文件，於Application Server(Tomcat)啟動時來刪除文件
// //String fileName = CharacterUtil.getRandomString2(10);
// String fileName = RandomStringUtils.randomAlphanumeric(10);
// fileName = new StringBuffer(fileName).append(".xls").toString();
// final File file = new File(fileName);
// try{
// FileOutputStream os = new FileOutputStream(file);
// wb.write(os);
// os.close();
// }catch(Exception e){
// e.printStackTrace();
// }
//
// InputStream is = null;
// try{
// is = new FileInputStream(file);
//
// }catch(Exception e){
// e.printStackTrace();
// }
//
// //產生一個Thread，等15秒後再執行刪除瀏覽文件
// new Thread(new Runnable(){
// public void run(){
// try{
// Thread.sleep(15000);
// }catch(InterruptedException e){
// e.printStackTrace();
// }
// file.delete();//刪除瀏覽文件
// }
// }).start();
//
// return is;
}
public LoginDAO getLoginDao() {
return loginDao;
}
public void setLoginDao(LoginDAO loginDao) {
this.loginDao = loginDao;
}
}
3.於WEB-INF\applicationContext.xml加入Service設定：
<!-- Service --> <bean id="loginService" class="com.service.impl.user.LoginServiceImpl"> <property name="loginDao" ref="loginDAO"></property> </bean>
四、產生Excel File Action建立，如下所示：
1.產生Excel File Action(GenerateExcelAction.java):
package com.action.test;
import java.io.InputStream;
import com.opensymphony.xwork2.ActionSupport;
import com.service.user.LoginService;
public class GenerateExcelAction extends ActionSupport {
/**
*
*/
private static final long serialVersionUID = 1587516332074747682L;
private LoginService service;
public InputStream getDownloadFile(){
return this.service.getInputStream();
}
@Override
public String execute() throws Exception {
return SUCCESS;
}
public LoginService getService() {
return service;
}
public void setService(LoginService service) {
this.service = service;
}
}
2.於struts.xml(src目錄下)設定Struts2 Action:
<action name="generateExcel" class="GenerateExcelAction"> <result name="success" type="stream"> <param name="contentType">application/vnd.ms-excel</param> <param name="contentDisposition">attachment;filename="AllUserList.xls"</param> <param name="inputName">downloadFile</param> </result> </action>
3.於applicationContext.xml(WEB-INF目錄下)設定Struts2 Action:
<bean id="GenerateExcelAction" class="com.action.test.GenerateExcelAction" scope="singleton"> <property name="service" ref="loginService"></property> </bean>
五、測試運行是否正確，如下圖所示：
http://localhost:8080/Demo/test/downloadExcel.jsp
看到此畫面，恭喜您整合開發環境設定成功！
沒有留言:
張貼留言