使用SSH2和POI写出Excel文件

最新推荐文章于 2021-04-02 12:45:05 发布

玄玉

最新推荐文章于 2021-04-02 12:45:05 发布

阅读量2.6k

点赞数

分类专栏： Struts2 小试Struts2 文章标签： excel ssh class file struts

本文链接：https://blog.csdn.net/jadyer/article/details/6196294

版权

Struts2 同时被 2 个专栏收录

23 篇文章 0 订阅

订阅专栏

小试Struts2

23 篇文章 1 订阅

订阅专栏

完整版见https://jadyer.github.io/

这是一个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>    <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener>   <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>     <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>     <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);