Emp 实体类和映射文件
package cn.entity;
import java.util.Date;
@SuppressWarnings("serial")
public class Emp implements java.io.Serializable {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptno;
@Override
public String toString() {
return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job
+ ", mgr=" + mgr + ", hiredate=" + hiredate + ", sal=" + sal
+ ", comm=" + comm + ", deptno=" + deptno + "]";
}
public Emp() {
}
public Emp(String ename, String job, Integer mgr, Date hiredate,
Double sal, Double comm, Integer deptno) {
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public Emp(Integer empno, String ename, String job, Integer mgr,
Date hiredate, Double sal, Double comm, Integer deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Integer getMgr() {
return mgr;
}
public void setMgr(Integer mgr) {
this.mgr = mgr;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public Double getSal() {
return sal;
}
public void setSal(Double sal) {
this.sal = sal;
}
public Double getComm() {
return comm;
}
public void setComm(Double comm) {
this.comm = comm;
}
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
}
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Mapping file autogenerated by MyEclipse Persistence Tools -->
<hibernate-mapping>
<class name="cn.entity.Emp" table="EMP" schema="SCOTT">
<id name="empno" type="java.lang.Integer">
<column name="EMPNO" precision="4" scale="0" />
<generator class="sequence">
<param name="sequence">seq_emp</param>
</generator>
</id>
<property name="ename" type="java.lang.String">
<column name="ENAME" length="10" />
</property>
<property name="job" type="java.lang.String">
<column name="JOB" length="9" />
</property>
<property name="mgr" type="java.lang.Integer">
<column name="MGR" precision="4" scale="0" />
</property>
<property name="hiredate" type="java.util.Date">
<column name="HIREDATE" length="7" />
</property>
<property name="sal" type="java.lang.Double">
<column name="SAL" precision="7" />
</property>
<property name="comm" type="java.lang.Double">
<column name="COMM" precision="7" />
</property>
<property name="deptno" type="java.lang.Integer">
<column name="DEPTNO" precision="2" scale="0" />
</property>
</class>
</hibernate-mapping>
EmpDao DAO类
package cn.dao;
import java.util.List;
import cn.entity.Emp;
public interface EmpDao {
List<Emp> findAll();
}
package cn.dao.impl;
import java.util.List;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import cn.dao.EmpDao;
import cn.entity.Emp;
public class EmpDaoImpl extends HibernateDaoSupport implements EmpDao {
@SuppressWarnings("unchecked")
@Override
public List<Emp> findAll() {
return super.getHibernateTemplate().find("from Emp");
}
}
EmpBiz 业务类
package cn.biz;
import java.util.List;
import cn.entity.Emp;
public interface EmpBiz {
List<Emp> findAll();
}
package cn.biz.impl;
import java.util.List;
import cn.biz.EmpBiz;
import cn.dao.EmpDao;
import cn.entity.Emp;
public class EmpBizImpl implements EmpBiz {
private EmpDao empDao;
public void setEmpDao(EmpDao empDao) {
this.empDao = empDao;
}
@Override
public List<Emp> findAll() {
return empDao.findAll();
}
}
ExcelAction 控制类
package cn.action;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.List;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import cn.biz.EmpBiz;
import cn.entity.Emp;
import com.opensymphony.xwork2.ActionSupport;
@SuppressWarnings("serial")
public class ExcelAction extends ActionSupport {
//建立一个输入流对象,用于 Excel 文件下载
private EmpBiz empBiz;
private InputStream inputStream;
//调用业务类生成 Excel
public String execute(){
List<Emp> emps = this.empBiz.findAll();
this.inputStream = generateExcel(emps);
return SUCCESS;
}
/**
* 调用 JXL 生成 Excel 文件
* */
private static InputStream generateExcel(List<Emp> emps){
Label label = null;
WritableWorkbook workbook = null;
//字节数组的输出流
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet("第 1 页", 0);
sheet.mergeCells(0, 0, 3, 0);
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD,false,
jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.GREEN);
jxl.write.WritableCellFormat wfC = new jxl.write.WritableCellFormat(wf);
label = new jxl.write.Label(0, 0, "Emp 列表",wfC);
sheet.addCell(label);
int count = 1;
jxl.write.WritableFont wfF = new jxl.write.WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false,
jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLUE2);
jxl.write.WritableCellFormat wfFC = new jxl.write.WritableCellFormat(wfF);
for (Emp emp : emps) {
jxl.write.Number number = new jxl.write.Number(0 ,count ,emp.getEmpno());
sheet.addCell(number);
label = new jxl.write.Label(1 ,count ,emp.getEname(),wfFC);
sheet.addCell(label);
label = new jxl.write.Label(2 ,count ,emp.getJob(),wfFC);
sheet.addCell(label);
++count;
}
workbook.write();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
InputStream is = new ByteArrayInputStream(os.toByteArray());
return is;
}
public InputStream getInputStream() {
return inputStream;
}
public void setInputStream(InputStream inputStream) {
this.inputStream = inputStream;
}
public EmpBiz getEmpBiz() {
return empBiz;
}
public void setEmpBiz(EmpBiz empBiz) {
this.empBiz = empBiz;
}
}
web.xml 配置
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
version="3.0">
<display-name>Spring13Struts2Jxl</display-name>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:applicationContext.xml</param-value>
</context-param>
<filter>
<filter-name>struts2</filter-name>
<filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>struts2</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
applicationContext.xml 配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<!-- 数据源配置 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="oracle.jdbc.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@localhost:1521:oracle11" />
<property name="username" value="scott" />
<property name="password" value="tiger" />
</bean>
<!-- 会话工厂 -->
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource" />
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
org.hibernate.dialect.Oracle10gDialect
</prop>
<prop key="hibernate.show_sql">
true
</prop>
</props>
</property>
<property name="mappingResources">
<list>
<value>cn/entity/Emp.hbm.xml</value>
</list>
</property>
</bean>
<!-- 配置事务管理器 -->
<bean id="txManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory">
<ref bean="sessionFactory" />
</property>
</bean>
<!-- 配置事务的传播特性 -->
<tx:advice id="txAdvice" transaction-manager="txManager">
<tx:attributes>
<tx:method name="add*" propagation="REQUIRED" />
<tx:method name="submit*" propagation="REQUIRED" />
<tx:method name="save*" propagation="REQUIRED" />
<tx:method name="insert*" propagation="REQUIRED" />
<tx:method name="del*" propagation="REQUIRED" />
<tx:method name="remove*" propagation="REQUIRED" />
<tx:method name="update*" propagation="REQUIRED" />
<tx:method name="modify*" propagation="REQUIRED" />
<tx:method name="check*" propagation="REQUIRED" />
<tx:method name="do*" propagation="REQUIRED" />
<tx:method name="deal*" propagation="REQUIRED" />
<tx:method name="get*" propagation="SUPPORTS" read-only="true" />
<tx:method name="find*" propagation="SUPPORTS" read-only="true" />
<tx:method name="search*" propagation="SUPPORTS" read-only="true" />
<tx:method name="query*" propagation="SUPPORTS" read-only="true" />
<tx:method name="*" propagation="SUPPORTS" read-only="true" />
</tx:attributes>
</tx:advice>
<!-- 那些类的哪些方法参与事务 -->
<aop:config>
<aop:pointcut id="serviceMethod" expression="execution(* cn.biz.*.*(..))" />
<aop:advisor advice-ref="txAdvice" pointcut-ref="serviceMethod" />
</aop:config>
<!-- 配置DAO -->
<bean id="empDao" class="cn.dao.impl.EmpDaoImpl">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
<!-- 配置服务层 -->
<bean id="empBiz" class="cn.biz.impl.EmpBizImpl">
<property name="empDao" ref="empDao"></property>
</bean>
<!-- 配置控制层 -->
<bean id="excelAction" class="cn.action.ExcelAction" scope="prototype">
<property name="empBiz" ref="empBiz"></property>
</bean>
</beans>
struts.xml 配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts
PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN"
"http://struts.apache.org/dtds/struts-2.1.dtd">
<struts>
<package name="cn.action" extends="struts-default" namespace="/">
<action name="excel" class="cn.action.ExcelAction">
<result type="stream" name="success">
<!-- 返回类型是 Excel -->
<param name="contentType">application/vnd.ms-excel</param>
<param name="inputName">inputStream</param>
<!-- 指定下载的文件名 -->
<param name="contentDisposition">attachment;filename="export.xls"</param>
<param name="bufferSize">1024</param>
</result>
</action>
</package>
</struts>
index.jsp 页面
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
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>下载文档</title>
</head>
<body>
下载文档:
<a href="excel.action">内容</a>
</body>
</html>
效果图: