使用SSH2和POI写出Excel文件

这是一个Web Project，使用了jdk1.6.0_13和JavaEE5.0

首先列出的是该工程所使用的Jar包

/**
 * 本工程所用Jar包，如下所示
 * @see --------------------------------------------------------
 * @see 【Struts2.1.8.1】
 * @see commons-fileupload-1.2.1.jar
 * @see commons-io-1.3.2.jar
 * @see commons-logging-1.0.4.jar
 * @see freemarker-2.3.15.jar
 * @see ognl-2.7.3.jar
 * @see struts2-core-2.1.8.1.jar
 * @see xwork-core-2.1.6.jar
 * @see --------------------------------------------------------
 * @see 【Spring2.5.6.SEC01】
 * @see commons-logging.jar
 * @see log4j-1.2.15.jar
 * @see spring.jar
 * @see --------------------------------------------------------
 * @see 【Hibernate3.3.2.GA】
 * @see antlr-2.7.6.jar
 * @see commons-collections-3.1.jar
 * @see dom4j-1.6.1.jar
 * @see hibernate3.jar
 * @see javassist-3.9.0.GA.jar
 * @see jta-1.1.jar
 * @see slf4j-api-1.5.11.jar
 * @see slf4j-simple-1.5.11.jar
 * @see --------------------------------------------------------
 * @see 【commons-dbcp-1.4】
 * @see commons-dbcp-1.4.jar
 * @see commons-pool-1.5.4.jar
 * @see --------------------------------------------------------
 * @see 【其它】
 * @see struts2-spring-plugin-2.1.8.1.jar
 * @see ojdbc6.jar
 * @see commons-lang-2.5.jar
 * @see poi-2.5.1.jar
 * @see --------------------------------------------------------
 */

然后是web.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
	http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
	<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>

	<!-- 实例化Spring容器 -->
	<!-- 当应用启动的时候，ContextLoaderListener就会被执行，然后就会读取Spring的相关配置文件 -->
	<!-- 它默认会到//当前Web应用//WebRoot//WEB-INF//目录中查找applicationContext.xml文件 -->
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>

	<!-- 让该Servlet在服务器启动时，被加载执行 -->
	<!-- load-on-startup用来设定启动的先后顺序 -->
	<servlet>
		<servlet-name>DeleteFilesServlet</servlet-name>
		<servlet-class>com.jadyer.servlet.DeleteFilesServlet</servlet-class>
		<load-on-startup>8</load-on-startup>
	</servlet>
</web-app>

然后是位于//WebRoot//WEB-INF//下的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"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
	
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="oracle.jdbc.OracleDriver" />
		<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:jadyer" />
		<property name="username" value="scott" />
		<property name="password" value="jadyer" />
		<property name="maxActive" value="100" />
		<property name="maxIdle" value="30" />
		<property name="maxWait" value="500" />
	</bean>

	<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
				<prop key="hibernate.show_sql">true</prop>
				<prop key="hibernate.format_sql">true</prop>
			</props>
		</property>
		<property name="mappingResources">
			<list>
				<value>com/jadyer/model/User.hbm.xml</value>
			</list>
		</property>
	</bean>

	<bean id="userDao" class="com.jadyer.dao.impl.UserDAOImpl" scope="singleton">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>

	<bean id="userService" class="com.jadyer.service.impl.UserServiceImpl">
		<property name="userDao" ref="userDao" />
	</bean>

	<bean id="listUserAction" class="com.jadyer.action.ListUserAction" scope="prototype">
		<property name="service" ref="userService" />
	</bean>
	<bean id="generateExcelAction" class="com.jadyer.action.GenerateExcelAction" scope="singleton">
		<property name="service" ref="userService" />
	</bean>
</beans>

接着是位于classpath中的struts.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
	"-//Apache Software Foundation//DTD Struts Configuration 2.0//EN"
	"http://struts.apache.org/dtds/struts-2.0.dtd">
<struts>
	<package name="user" extends="struts-default">
		<action name="listUser" class="listUserAction">
			<result>/list.jsp</result>
		</action>
		<!-- 点击生成Excel时会弹出对话框，询问用户打开或下载。若选择打开，浏览器会调用第三方工具打开Excel文件 -->
		<!-- 但此时//tomcat6//bin//中却产生了两个随机名字的Excel文件。这时我们可以在此增加attachment;参数 -->
		<!-- 也就是说此时无论是什么类型的文件，哪怕是TXT文件，它也会弹出一个下载框，供用户选择打开或者下载 -->
		<!-- 并且，此时的//tomcat6//bin//中产生的就是一个随机名字的Excel文件了 -->
		<action name="generateExcel11" class="generateExcelAction">
			<result name="success" type="stream">
				<param name="contentType">application/vnd.ms-excel</param>
				<param name="contentDisposition">attachment;filename="AllUsers11.xls"</param>
				<param name="inputName">downloadFile11</param>
			</result>
		</action>
		<action name="generateExcel22" class="generateExcelAction">
			<result name="success" type="stream">
				<param name="contentType">application/vnd.ms-excel</param>
				<param name="contentDisposition">attachment;filename="AllUsers22.xls"</param>
				<param name="inputName">downloadFile22</param>
			</result>
		</action>
	</package>
</struts>

然后是index.jsp页面

<%@ page pageEncoding="UTF-8"%>
<%response.sendRedirect(request.getContextPath() + "/listUser.action");%>

然后是list.jsp页面

<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<table border="9" width="28%">
	<tr>
		<td>编号</td>
		<td>姓名</td>
		<td>年龄</td>
	</tr>
	<s:iterator value="#request.list" id="us">
		<tr>
			<td><s:property value="#us.id" /></td>
			<td><s:property value="#us.name" /></td>
			<td><s:property value="#us.age" /></td>
		</tr>
	</s:iterator>
</table>
<br/>
生成Excel文件：
<a href="<%=request.getContextPath()%>/generateExcel11.action">方式一</a>&nbsp;&nbsp;&nbsp;&nbsp;
<a href="<%=request.getContextPath()%>/generateExcel22.action">方式二</a>

该demo工程所用到的实体类User.java

package com.jadyer.model;

public class User {
	private Integer id;
	private String name;
	private Integer age;
	
	/* 三个属性的setter和getter略 */
}

实体类的映射文件User.hbm.xml

<?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">

<hibernate-mapping>
	<class name="com.jadyer.model.User" table="users">
		<id name="id" type="java.lang.Integer" column="id">
			<generator class="sequence">
				<param name="sequence">sequence_users</param>
			</generator>
		</id>
		<property name="name" type="java.lang.String" column="name" length="50" />
		<property name="age" type="java.lang.Integer" column="age" />
	</class>
</hibernate-mapping>

该demo工程所用到的持久层接口和实现类

package com.jadyer.dao;
import java.util.List;
import com.jadyer.model.User;
public interface UserDAO {
	public List<User> findAllUsers();
}

/**
 * 上面的UserDAO为持久层接口
 * 下面的UserDAOImpl为持久层接口的实现类
 */

package com.jadyer.dao.impl;
import java.util.List;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.jadyer.dao.UserDAO;
import com.jadyer.model.User;
public class UserDAOImpl extends HibernateDaoSupport implements UserDAO {
	public List<User> findAllUsers() {
		String hql = "from User user order by user.id";
		return (List<User>)this.getHibernateTemplate().find(hql);
	}
}

该demo工程所用到的服务层接口和实现类

package com.jadyer.service;
import java.io.InputStream;
import java.util.List;
import com.jadyer.model.User;
public interface UserService {	
	public List<User> findAll();
	public InputStream getInputStream11();
	public InputStream getInputStream22();
}

/**
 * 上面的UserService为服务层接口
 * 下面的UserServiceImple为服务层接口的实现类
 */

package com.jadyer.service.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import org.apache.commons.lang.RandomStringUtils;
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.jadyer.dao.UserDAO;
import com.jadyer.model.User;
import com.jadyer.service.UserService;

/**
 * @see 实现Excel的动态生成以及下载，需要分为以下两个步骤
 * @see 1..使用Apache-POI组件将数据库中读取到的信息生成一个Excel文件
 * @see 2..将生成的Excel文件以InputStream形式返回给浏览器端
 */
public class UserServiceImpl implements UserService {
	private UserDAO userDao;
	public void setUserDao(UserDAO userDao) {
		this.userDao = userDao;
	}

	public List<User> findAll() {
		return userDao.findAllUsers();
	}

	/**
	 * 这里是借助内存来存放Excel临时文件。。比较推荐使用这种方式
	 */
	public InputStream getInputStream11() {
		HSSFWorkbook wb = new HSSFWorkbook(); //相当于Excel整个文件
		//HSSFSheet sheet = wb.createSheet("sheet1");
		HSSFSheet sheet = wb.createSheet(); //生成Excel中的sheet
		wb.setSheetName(0, "用户资料", HSSFWorkbook.ENCODING_UTF_16);
		
		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("年龄");

		/**
		 * 至此，表头部分就定义好了
		 */

		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.getId());

			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(user.getName());

			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(user.getAge());
		}
		
		/**
		 * 至此为止，就已经成功的将用户信息set到Excel单元格里面了
		 */

        //byte[] content = wb.getBytes();
		//InputStream is = new ByteArrayInputStream(content);
		//HSSFWorkbook对象提供了一个getBytes()方法，该方法在API文档中的描述如下
		//get the bytes of just the HSSF portions of the XLS file
		//也就是说：得到一个字节，但得到的仅仅是XLS文件的HSSF部分的内容
		//所以该方法并不能得到XLS文件的全部内容，所以若使用getBytes()，则得到的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;
	}
	
	/**
	 * 这里是把Excel临时文件存放在了物理硬盘上
	 */
	public InputStream getInputStream22() {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(); //生成Excel中的sheet
		wb.setSheetName(0, "用户资料", HSSFWorkbook.ENCODING_UTF_16);
		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("年龄");
		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.getId());
			cell = row.createCell((short) 1);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(user.getName());
			cell = row.createCell((short) 2);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(user.getAge());
		}
		/**
		 * 这里test.xls默认会生成并存放在//tomcat6//bin//中，且默认的第二次请求所生成的test.xls会覆盖掉原有文件
		 * 那么在多人同时操作的情况下，就会出问题。这时有两种解决办法
		 * 1..在内存中将输出流转换成输入流，供客户端下载。比如this.getInputStream11()
		 * 2..针对每个用户，产生一个名字随机的文件，并且当信息传送到浏览器之后，将随机文件从硬盘中删掉
		 */
		final File file22 = new File("test.xls"); //这里并没有使用到file22，其仅供演示效用
		String fileName = RandomStringUtils.randomAlphanumeric(10); //或者使用CharacterUtils.getRandomString11(10)
		fileName = new StringBuffer(fileName).append(".xls").toString();
		final File file = new File(fileName);
		try {
			OutputStream os = new FileOutputStream(file); 
			wb.write(os);
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		InputStream is = null;
		try {
			is = new FileInputStream(file);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		//线程休眠15秒，为用户下载文件提供时间
		//如果在这休眠的时间内，服务器关闭的话，那么生成的Excel临时文件将无法删除
		//这时可用DeleteFilesServlet.java在服务器启动时删除生成的Excel临时文件
		new Thread(new Runnable() {
			public void run() {
				try {
					Thread.sleep(15000);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				file.delete();// 删除生成的Excel临时文件
			}
		}).start();
		return is;
	}
}

用于显示所有用户列表的Action类

package com.jadyer.action;

import java.util.Map;

import com.jadyer.service.UserService;
import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;

public class ListUserAction extends ActionSupport {
	private UserService service;
	public void setService(UserService service) {
		this.service = service;
	}

	@Override
	public String execute() throws Exception {
		Map request = (Map) ActionContext.getContext().get("request");
		request.put("list", service.findAll());
		return SUCCESS;
	}
}

用于生成Excel文件的Action类

package com.jadyer.action;

import java.io.InputStream;

import com.jadyer.service.UserService;
import com.opensymphony.xwork2.ActionSupport;

public class GenerateExcelAction extends ActionSupport{
	private UserService service;
	public void setService(UserService service) {
		this.service = service;
	}

	public InputStream getDownloadFile11() {
		return service.getInputStream11();
	}
	
	public InputStream getDownloadFile22() {
		return service.getInputStream22();
	}
}

在服务器启动时删除Excel临时文件的Servlet

package com.jadyer.servlet;

import java.io.File;
import java.io.FileFilter;

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

/**
 * 服务器启动时，删除Excel临时文件
 */
public class DeleteFilesServlet extends HttpServlet {
	
//	@Override
//	public void init(){
//		File file = new File(".");
//		File[] subFiles = file.listFiles();
//		for (File f : subFiles) {
//			if (f.getName().endsWith("xls")) {
//				f.delete();
//			}
//		}
//	}
	
	@Override
	public void init() throws ServletException {
		File file = new File("."); // 这里的点.就是定位到当前目录，即tomcat6/bin中
		File[] subFiles = file.listFiles(new FileFilter() {
			public boolean accept(File pathname) {
				if (pathname.getName().endsWith("xls")) {
					return true; //这样得到的subFiles中的所有文件就都是Excel文件
				}else{
					return false;
				}
			}
		});
		for (File f : subFiles) {
			f.delete();
		}
	}
}

用于获取随机数的工具类

package com.jadyer.util;

import java.util.Random;

/**
 * 获取随机数
 */
public class CharacterUtils {
	public static void main(String[] args) {
		System.out.println(getRandomString11(10));
		System.out.println(getRandomString22(10));
	}
	
	public static String getRandomString11(int length) {
		String str = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
		Random random = new Random();
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < length; ++i) {
			int number = random.nextInt(62);//产生一个随机数
			sb.append(str.charAt(number));
		}
		return sb.toString();
	}

	public static String getRandomString22(int length) {
		Random random = new Random();
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < length; ++i) {
			int number = random.nextInt(3);
			long result = 0;
			switch (number) {
			case 0:
				//如果获取的随机数是0，那么就直接产生一个随机的大写字母
				result = Math.round(Math.random() * 25 + 65);
				//Math.random()*25会产生一个基于0.0到25的随机Double数，但不包含25
				sb.append(String.valueOf((char)result));
				break;
			case 1:
				//如果获取的随机数是1，那么就直接产生一个随机的小写字母
				result = Math.round(Math.random() * 25 + 97);
				sb.append(String.valueOf((char)result));
				break;
			case 2:
				//如果获取的随机数是2，那么就直接产生一个0--9的随机数
				sb.append(String.valueOf(new Random().nextInt(10)));
				break;
			}
		}
		return sb.toString();
	}
}

最后是数据库脚本文件

--Oracle 11g
--创建表格
create table users(
	id number(3) primary key,
	name varchar2(8),
	age number(3)
)

--创建序列
create sequence sequence_users increment by 1 start with 1 nomaxvalue nocycle;

--准备数据
insert into users values(sequence_users.nextval, '张起灵', 20);
insert into users values(sequence_users.nextval, '陈文锦', 22);
insert into users values(sequence_users.nextval, '吴三省', 24);
insert into users values(sequence_users.nextval, '汪藏海', 26);
insert into users values(sequence_users.nextval, '铁面生', 28);