基于struts2、spring、ibatis、poi的案例，将数据导出致Excel中

基于struts2、spring、ibatis、poi的案例，将数据导出致Excel中

整个工程目录结构图

需要用到的相关jar包结构图

web.xml的配置

 	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>
			classpath*:spring-ds.xml,
			classpath*:spring-data.xml,
			classpath*:spring-biz.xml,
			classpath*:spring-action.xml
		</param-value>
	</context-param>
	
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>	

	<filter>
		<filter-name>struts2</filter-name>
		<filter-class>org.apache.struts2.dispatcher.FilterDispatcher</filter-class>
	</filter>  
	
	<filter-mapping>
		<filter-name>struts2</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>

index.jsp页面

	<form action="findUser.action" method="post">
		<fieldset>
			<div id="select">
				<label>用户名:</label><input id="uname" type="text" name="userDO.username"><input type="submit" value="查询" />
			</div>
			<table border="1" cellpadding="0" cellspacing="0" style="bordercolor:#bed;">
				<tr>
					<th>编号</th>
					<th>用户名</th>
					<th>密码</th>
					<th>真实姓名</th>
				</tr>
				<s:iterator id="userDO" value="userList">
				<tr>
					<td><s:property value="#userDO.id"/></td>
					<td><s:property value="#userDO.username"/></td>
					<td><s:property value="#userDO.password"/></td>
					<td><s:property value="#userDO.realname"/></td>
				</tr>
				</s:iterator>
			</table>
			<a href="export.action">导出Excel</a>
		</fieldset>
	</form>

将ssi的配置文件放置到src目录下，具体配置文如下：

jdbc.properties，spring-action.xml，spring-biz.xml，spring-data.xml，spring-ds.xml，sqlmap-config.xml，struts.xml

jdbc.properties

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=root

spring-action.xml

<bean id="userAction" class="com.xdl.action.UserAction" scope="prototype">
		<property name="userBO" ref="userBO" />
</bean>

spring-biz.xml

	<!-- 配置事务 -->
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>

	<bean id="transactionProxy" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean" abstract="true">
		<property name="transactionManager" ref="transactionManager"/>
		<property name="transactionAttributes">
			<props>
				<prop key="add*">PROPAGATION_REQUIRED</prop>
				<prop key="delete*">PROPAGATION_REQUIRED</prop>
				<prop key="update*">PROPAGATION_REQUIRED</prop>
				<prop key="get*">PROPAGATION_REQUIRED</prop>
				<prop key="find*">PROPAGATION_REQUIRED,readOnly</prop>
				<prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
			</props>
		</property>
	</bean>
	
	<bean id="userBO" class="com.xdl.biz.impl.UserBOImpl">
		<property name="userDAO" ref="userDAO" />
	</bean>

spring-data.xml

	<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="configLocation">
			<value>classpath:sqlmap-config.xml</value>
		</property>
	</bean>

	<bean id="userDAO" class="com.xdl.dao.impl.UserDAOImpl">
		<property name="sqlMapClient" ref="sqlMapClient" />
	</bean>

spring-ds.xml

	<!-- 加载jdbc的配置文件jdbc.properties -->
	<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>classpath:/jdbc.properties</value>
			</list>
		</property>	  
	</bean>

	<!-- 配置数据源 -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="${jdbc.driverClassName}"/>
		<property name="url" value="${jdbc.url}"/>
		<property name="username" value="${jdbc.username}"/>
		<property name="password" value="${jdbc.password}"/>
	</bean>

sqlmap-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map 
Config 2.0/" "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<!-- /WEB-INF/dtds/sql-map-config-2.dtd -->

<sqlMapConfig>
	<sqlMap resource="com/xdl/dao/maps/sqlmap-user.xml"/>
</sqlMapConfig>

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="default" extends="struts-default">
		<action name="findUser" class="userAction" method="queryUser">
			<result name="success">/index.jsp</result>
		</action>
		
		<action name="export" class="userAction" method="exportExcel">
			<result name="success" type="stream">
				<param name="contentType">application/vnd.ms-excel</param>
				<param name="inputName">inputStream</param>
				<param name="contentDisposition">attachment;filename="user.xls"</param>
				<param name="bufferSize">4096</param>
			</result>
		</action>
	</package>
</struts>

UserAction.java

package com.xdl.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts2.interceptor.ServletResponseAware;

import com.opensymphony.xwork2.ActionSupport;
import com.xdl.biz.IUserBO;
import com.xdl.dataobject.UserDO;

public class UserAction extends ActionSupport implements ServletResponseAware {

	private static final long serialVersionUID = 7311940547161671031L;

	private UserDO userDO;
	private IUserBO userBO;
	private List<UserDO> userList;
	private HttpServletResponse response;

	/***
	 * @description 模糊查询所有用户信息
	 * @return
	 */
	public String queryUser() {
		try {
			userList = this.userBO.findUser(userDO);
			if (userList != null) {
				return SUCCESS;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**导出EXCEL*/
	public String  exportExcel() {
		return SUCCESS;
	}
	
	public InputStream getInputStream() throws Exception {
		return this.getInStream();
	}

	public InputStream getInStream() throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook(); //创建一个工作薄
		HSSFSheet sheet = wb.createSheet("用户列表"); //创建一个表,表的名称为"用户列表"
		
		//设置表格的样式
		HSSFCellStyle style = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short)10); //字体高度
		font.setColor(HSSFFont.COLOR_NORMAL); //字体颜色
		font.setFontName("宋体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
		
		style.setFont(font);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中显示
		style.setWrapText(false);
		
		HSSFRow row = sheet.createRow(0); //创建第一行
		
		HSSFCell cell = row.createCell(0); //创建第一个单元格
		cell.setCellStyle(style); //设置第一个单元格的样式
		cell.setCellValue("编号"); //设置第一个单元格的值
		
		cell = row.createCell(1); //创建第二个单元格
		cell.setCellStyle(style);
		cell.setCellValue("用户名");
		
		cell = row.createCell(2); //创建第三个单元格
		cell.setCellStyle(style);
		cell.setCellValue("密码");
		
		cell = row.createCell(3);  //创建第四个单元格
		cell.setCellStyle(style);
		cell.setCellValue("真实姓名"); 
		
		userList = this.userBO.findUser(userDO);
		System.out.println(userList.size());
		if(userList != null) {
			for(int i=0; i<userList.size(); i++) {
				row = sheet.createRow(i+1);
				cell = row.createCell(i);
				row.createCell(0).setCellValue(userList.get(i).getId());
				row.createCell(1).setCellValue(userList.get(i).getUsername());
				row.createCell(2).setCellValue(userList.get(i).getPassword());
				row.createCell(3).setCellValue(userList.get(i).getRealname());
			}
		}
		String fileName = "user";
		//fileName = new StringBuffer(fileName).append(".xls").toString();
		StringBuffer sb = new StringBuffer(fileName);
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		sb.append(df.format(new Date()));
		sb.append(".xls");
		File file = new File(sb.toString());
		try{
			OutputStream os = new FileOutputStream(file);
			wb.write(os);
			os.flush();
			os.close();
			InputStream is = new FileInputStream(file);
			return is;
		}catch(Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/***
	 * @Description 获取文件名
	 * @return
	 */
	/*public String getFileName() {
		StringBuffer sb = new StringBuffer();
		sb.append("user-");
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		sb.append(df.format(new Date()));
		return sb.toString();
	}*/

	public UserDO getUserDO() {
		return userDO;
	}

	public void setUserDO(UserDO userDO) {
		this.userDO = userDO;
	}

	public IUserBO getUserBO() {
		return userBO;
	}

	public void setUserBO(IUserBO userBO) {
		this.userBO = userBO;
	}

	public List<UserDO> getUserList() {
		return userList;
	}

	public void setUserList(List<UserDO> userList) {
		this.userList = userList;
	}

	@Override
	public void setServletResponse(HttpServletResponse response) {
		this.response = response;
	}
}

IUserBO.java

UserBOImpl.java

IUserDAO.java

IUserDAOImpl.java

将sqlmap-user.xml文件放入到com.xdl.dao.maps包下

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap>
	<typeAlias alias="userDO" type="com.xdl.dataobject.UserDO"/>
	
	<!--将返回的记录逐个字段的映射赋值给对象userDO的属性上 ，class指出结果集要映射的数据类型 -->
	<resultMap class="userDO" id="findAllUser">
		<result property="id" column="id"/>
		<result property="username" column="username"/>
		<result property="password" column="password"/>
		<result property="realname" column="realname"/>
	</resultMap>
	
	<!--查询全部用户  -->
	<select id="FIND-ALL-USER" resultMap="findAllUser">
		<![CDATA[
		SELECT 
			id,username,password,realname 
		FROM 
			user
		]]>
		<dynamic prepend="WHERE">
			<isNotEmpty prepend="AND" property="username">
				username like '%$username$%'
			</isNotEmpty>
		</dynamic>
	</select>
</sqlMap>

实体类UserDO.java

package com.xdl.dataobject;

import java.io.Serializable;

public class UserDO implements Serializable {

	private static final long serialVersionUID = 4385670730685946644L;

	/** 编号 */
	private Integer id;
	/** 用户名 */
	private String username;
	/** 密码 */
	private String password;
	/** 真实姓名 */
	private String realname;

	public UserDO() {
	}

	public UserDO(Integer id, String username, String password, String realname) {
		this.id = id;
		this.username = username;
		this.password = password;
		this.realname = realname;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getRealname() {
		return realname;
	}

	public void setRealname(String realname) {
		this.realname = realname;
	}
}

使用mysql数据库的建表语句

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `username` varchar(10) NOT NULL,
  `password` varchar(15) NOT NULL,
  `realname` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'admin', 'admin', '张三');
INSERT INTO `user` VALUES ('2', 'user', 'user', '李斯');
INSERT INTO `user` VALUES ('3', 'aaa', 'aaa', '王五');
INSERT INTO `user` VALUES ('4', 'bbb', 'bbb', '李思');

写的不是很好！