java将数据库里的数据导出到excel（转）

项目中很多都会用到将数据导出到excel,然后对数据进行整理分析,在之前的项目中,多处用到此功能,也走了很多弯路,从一开始的tableExcel到现在的poi,从一开始用HSSFWorkbook

再到XSSFWorkbook,一步步优化,废话少说,直接开始.

项目框架

1,后台:spring+springmvc+mybatis

2,前台: bootstrap+jQuery+ajax

3,项目管理:maven

说明.excel处理函数需要引入poi的jar包,在pom.xml引入一下代码

[html]  view plain  copy

1. <!-- POI -->    
2. <dependency>    
3.     <groupId>org.apache.poi</groupId>    
4.     <artifactId>poi</artifactId>    
5.     <version>3.8</version>    
6.     <exclusions>    
7.         <exclusion>    
8.             <artifactId>commons-codec</artifactId>    
9.             <groupId>commons-codec</groupId>    
10.         </exclusion>    
11.     </exclusions>    
12. </dependency>    
13. <dependency>    
14.     <groupId>org.apache.poi</groupId>    
15.     <artifactId>poi-ooxml</artifactId>    
16.     <version>3.8</version>    
17. </dependency>    

别的框架大体上也是可以的,只需稍微调整,如有问题,大家可留言讨论

实现的功能说明:将数据库中的人员信息(姓名.年龄,电话)导出到excel

具体代码如下

1,前台html代码

[html]  view plain  copy

1. <span>  
2.     <label>姓名：</label>  
3.     <input id="name"  placeholder="请输入姓名"  type="text">  
4. </span>  
5. <span>  
6.     <label>性别：</label>  
7.     <select id="sex"  style="height: 24px;width: 163px;">  
8.         <option value="">请选择性别</option>  
9.         <option value="1">男</option>  
10.         <option value="2">女</option>  
11.     </select>  
12. </span>  
13. <span>  
14.     <label>年龄：</label>  
15.     <input id="age"  placeholder="请输入年龄"  type="text">  
16. </span>  
17. <button class="btn" id="deviceExport">导出</button>        
18. <script type="text/javascript" src="user.js"></script>  

前台页面效果图

2,js代码

[javascript]  view plain  copy

1. var User = function(){  
2.       
3.     this.init = function(){  
4.          // 用于导出excel  
5.         $("#userExport").click(function() {  
6.             var url =  '/user/export/';  
7.             location.href = url + "?queryJson="+JSON.stringify(user.acquireInquireData());  
8.         });  
9.     };  
10.       
11.     //获取查询条件  
12.     this.acquireInquireData = function(){  
13.         var inquireCondition = {  
14.                 name:$('#name').val(),//名称  
15.                 sex: $('#sex').val(),//性别  
16.                 age: $('#age').val(),//年龄  
17.         };  
18.         return inquireCondition;  
19.     };  
20. }  
21.       
22. var user;  
23. $(function(){  
24.     user = new User();  
25.     user.init();  
26. });  

3,domain的user实体类(该实体类如果作为最终导出结果的实体类,则该类的字段必须与需要导出的字段保持一致,且与导出列表的顺序也得保持一致)

[java]  view plain  copy

1. /**   
2.  * @author  李光光(编码小王子)  
3.  * @QQ      826331692 
4.  * @date    2016年11月7日 下午2:57:03   
5.  * @version 1.0     
6.  */    
7. public class User {    
8.     private  String name;    
9.     private String sex;    
10.     private String age;    
11.     public String getName() {    
12.         return name;    
13.     }    
14.     public void setName(String name) {    
15.         this.name = name;    
16.     }    
17.     public String getSex() {    
18.         return sex;    
19.     }    
20.     public void setSex(String sex) {    
21.         this.sex = sex;    
22.     }    
23.     public String getAge() {    
24.         return age;    
25.     }    
26.     public void setAge(String age) {    
27.         this.age = age;    
28.     }    
29. }    

4,controller层代码

[java]  view plain  copy

1. /** 
2.  * @author 李光光(编码小王子) 
3.  * @date 2015年12月29日 下午4:04:00 
4.  * @qq  826331692 
5.  * @version 1.0 
6.  * @return 
7.  */  
8. @Controller  
9. @RequestMapping("/user")  
10. public class UserController {  
11.       
12.     @Autowired  
13.     private UserService userService;  
14.     /** 
15.      * 用于导出excel的查询结果 
16.      * @param queryJson 
17.      * @return 
18.      */  
19.     @RequestMapping("/export")  
20.     public void export(HttpServletRequest request, HttpServletResponse response,  
21.                         @RequestParam(value = "queryJson") String queryJson) {  
22.         User user = JSON.parseObject(queryJson, User.class);  
23.         List<User> userlList = userService.getUserForExcel(user);  
24.         ExportExcel<User> ee= new ExportExcel<User>();  
25.         String[] headers = { "序号", "姓名", "性别", "年龄" };  
26.         String fileName = "用户信息表";  
27.         ee.exportExcel(headers,userlList,fileName,response);  
28.     }  
29. }  

5,service层代码

[java]  view plain  copy

1. public interface UserService {    
2.     
3.     /** 
4.      * 根据查询条件查询出所有的记录,不用分页,用于excel导出功能   
5.      * @param userDeviceVo 
6.      * @return 
7.      */  
8.     public List<User> getUserDeviceForExcel(User user);   
9.     
10. }    

6,service实现层代码

[java]  view plain  copy

1. /** 
2.  * @author  李光光(编码小王子) 
3.  * @date 2015年12月29日 下午3:43:08  
4.  * @Email 826331692@qq.com  
5.  * @version 1.0 
6.  * @return 
7.  */  
8. @service  
9. public class UserServiceImpl implements UserService {  
10.       
11.     @Autowired  
12.     private UserDao  userDao;  
13. /** 
14.      * 根据查询条件查询出所有的记录,不用分页,用于excel导出功能   
15.      * @param userDeviceVo 
16.      * @return 
17.      */  
18.     @Override  
19.     public List<User> getUserDeviceForExcel(User user) {  
20.         List<User> list = userDao.getUserForExcel(user);  
21.         Integer order;  
22.         for (int i = 0; i < list.size(); i++) {  
23.             order = i + 1;  
24.             list.get(i).setOrder(order.toString());  
25.             if (list.get(i).getSex().equals("1")) {  
26.                 list.get(i).setSex("男");  
27.             } else {  
28.                 list.get(i).setSex("女");  
29.             }  
30.         }  
31.         return list;  
32.     }  
33. }  

6dao层代码

[java]  view plain  copy

1. public interface UserDao {    
2.     
3.   /** 
4.      * 根据查询条件查询出所有的记录,不用分页,用于excel导出功能   
5.      * @param userDeviceVo 
6.      * @return 
7.      */  
8.     List<User> getUserForExcel(User user);  
9.   
10.     
11. }    

需要在配置文件中加载UserDao的信息,否则无法找到UserDao,配置如下:

[html]  view plain  copy

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <beans xmlns="http://www.springframework.org/schema/beans"  
3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
4.     xsi:schemaLocation="  
5.     http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">  
6.   
7.     <bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">  
8.         <property name="driverClassName">  
9.             <value>${DB_MSSQL_DRIVER}</value>  
10.         </property>  
11.         <property name="url">  
12.             <value>${DB_MSSQL_URL}</value>  
13.         </property>  
14.         <property name="username">  
15.             <value>${DB_MSSQL_USER}</value>  
16.         </property>  
17.         <property name="password">  
18.             <value>${DB_MSSQL_PW}</value>  
19.         </property>  
20.         <property name="maxActive">  
21.             <value>${maxActive}</value>  
22.         </property>  
23.         <property name="maxIdle">  
24.             <value>${maxIdle}</value>  
25.         </property>  
26.         <property name="minIdle">  
27.             <value>${minIdle}</value>  
28.         </property>  
29.         <property name="maxWait">  
30.             <value>${maxWait}</value>  
31.         </property>  
32.         <property name="timeBetweenEvictionRunsMillis">  
33.             <value>${timeBetweenEvictionRunsMillis}</value>  
34.         </property>  
35.         <property name="minEvictableIdleTimeMillis">  
36.             <value>${minEvictableIdleTimeMillis}</value>  
37.         </property>  
38.         <property name="testWhileIdle">  
39.             <value>${testWhileIdle}</value>  
40.         </property>  
41.         <property name="testOnReturn" value="true" />  
42.         <property name="testOnBorrow" value="true"/>   
43.         <property name="validationQuery">  
44.             <value>${validationQuery}</value>  
45.         </property>  
46.         <property name="removeAbandoned">  
47.             <value>${removeAbandoned}</value>  
48.         </property>   
49.         <property name="removeAbandonedTimeout">  
50.             <value>${removeAbandonedTimeout}</value>  
51.         </property>   
52.     </bean><!-- org.mybatis.spring.SqlSessionFactoryBean-->  
53.     <bean id="dataSourceCms" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">  
54.         <property name="driverClassName">  
55.             <value>${DB_MSSQL_CMS_DRIVER}</value>  
56.         </property>  
57.         <property name="url">  
58.             <value>${DB_MSSQL_CMS_URL}</value>  
59.         </property>  
60.         <property name="username">  
61.             <value>${DB_MSSQL_CMS_USER}</value>  
62.         </property>  
63.         <property name="password">  
64.             <value>${DB_MSSQL_CMS_PW}</value>  
65.         </property>  
66.         <property name="maxActive">  
67.             <value>${maxActive}</value>  
68.         </property>  
69.         <property name="maxIdle">  
70.             <value>${maxIdle}</value>  
71.         </property>  
72.         <property name="minIdle">  
73.             <value>${minIdle}</value>  
74.         </property>  
75.         <property name="maxWait">  
76.             <value>${maxWait}</value>  
77.         </property>  
78.         <property name="timeBetweenEvictionRunsMillis">  
79.             <value>${timeBetweenEvictionRunsMillis}</value>  
80.         </property>  
81.         <property name="minEvictableIdleTimeMillis">  
82.             <value>${minEvictableIdleTimeMillis}</value>  
83.         </property>  
84.         <property name="testWhileIdle">  
85.             <value>${testWhileIdle}</value>  
86.         </property>  
87.         <property name="testOnReturn" value="true" />  
88.         <property name="testOnBorrow" value="true"/>  
89.         <property name="validationQuery">  
90.             <value>${validationQuery}</value>  
91.         </property>  
92.         <property name="removeAbandoned">  
93.             <value>${removeAbandoned}</value>  
94.         </property>  
95.         <property name="removeAbandonedTimeout">  
96.             <value>${removeAbandonedTimeout}</value>  
97.         </property>  
98.     </bean>  
99.     <!-- 编写spring 配置文件的配置多数源映射关系 -->  
100.     <bean class="com.jd.iapp.utils.DynamicDataSource" id="dynamicDataSource">  
101.         <property name="targetDataSources">  
102.             <map key-type="java.lang.String">  
103.                 <entry value-ref="dataSource" key="dataSource"></entry>  
104.                 <entry value-ref="dataSourceCms" key="dataSourceCms"></entry>  
105.             </map>  
106.   
107.         </property>  
108.         <property name="defaultTargetDataSource" ref="dataSource">  
109.         </property>  
110.     </bean>  
111.     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
112.         <property name="dataSource" ref="dynamicDataSource" />  
113.         <property name="configLocation" value="classpath:spring/sqlMapConfig.xml"></property>  
114.     </bean>  
115.     <!-- 事务管理器 -->  
116.     <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
117.         <property name="dataSource" ref="dynamicDataSource"/>  
118.     </bean>  
119.     <!-- 用户表 -->  
120.     <bean id="userDao" class="org.mybatis.spring.mapper.MapperFactoryBean">  
121.         <property name="mapperInterface" value="com.jd.xe.web.dao.UserDao" />  
122.         <property name="sqlSessionFactory" ref="sqlSessionFactory" />  
123.     </bean>  
124. </beans>  

7mybatis代码

[html]  view plain  copy

1.   <?xml version="1.0" encoding="UTF-8"?>  
2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  
3. <mapper namespace="XXX .UserDao">  
4.   
5.   <!--根据查询条件查询出所有的记录,不用分页,用于excel导出功能  -->  
6.     <select id="getUserForExcel" parameterType="User" resultType="User">  
7.         select name,sex,age  
8.         from juser_table   
9.         where  1=1 and   
10.         <if test="name != null and name !=''">and  name=#{name}</if>  
11.         <if test="sex != null and sex !=''">and  sex=#{sex}</if>  
12.         <if test="age != null and age !=''">and  age=#{age}</if>  
13.     </select>  
14. </mapper>  

8,重头戏来了,将List数据写入到excel的代码如下:

[java]  view plain  copy

1. package com.jd.xe.web.service.userDevice;  
2.   
3. import java.io.BufferedOutputStream;  
4. import java.lang.reflect.Field;  
5. import java.lang.reflect.Method;  
6. import java.util.Collection;  
7. import java.util.Date;  
8. import java.util.Iterator;  
9.   
10. import javax.servlet.http.HttpServletResponse;  
11.   
12. import org.apache.poi.xssf.usermodel.XSSFCell;  
13. import org.apache.poi.xssf.usermodel.XSSFRichTextString;  
14. import org.apache.poi.xssf.usermodel.XSSFRow;  
15. import org.apache.poi.xssf.usermodel.XSSFSheet;  
16. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
17.   
18. import com.jd.xe.web.utils.DateUtil;  
19.   
20.   
21. /**  
22.  * @author  李光光(编码小王子) 
23.  * @Email   826331692@qq.com  
24.  * @date    2016年7月18日 下午9:03:29  
25.  * @version 1.0    
26.  */  
27. public class ExportExcel<T> {  
28.     public void exportExcel(String[] headers,Collection<T> dataset, String fileName,HttpServletResponse response) {  
29.         // 声明一个工作薄  
30.         XSSFWorkbook workbook = new XSSFWorkbook();  
31.         // 生成一个表格  
32.         XSSFSheet sheet = workbook.createSheet(fileName);  
33.         // 设置表格默认列宽度为15个字节  
34.         sheet.setDefaultColumnWidth((short) 20);  
35.         // 产生表格标题行  
36.         XSSFRow row = sheet.createRow(0);  
37.         for (short i = 0; i < headers.length; i++) {  
38.             XSSFCell cell = row.createCell(i);  
39.             XSSFRichTextString text = new XSSFRichTextString(headers[i]);  
40.             cell.setCellValue(text);  
41.         }  
42.         try {  
43.             // 遍历集合数据，产生数据行  
44.             Iterator<T> it = dataset.iterator();  
45.             int index = 0;  
46.             while (it.hasNext()) {  
47.                 index++;  
48.                 row = sheet.createRow(index);  
49.                 T t = (T) it.next();  
50.                 // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
51.                 Field[] fields = t.getClass().getDeclaredFields();  
52.                 for (short i = 0; i < headers.length; i++) {  
53.                     XSSFCell cell = row.createCell(i);  
54.                     Field field = fields[i];  
55.                     String fieldName = field.getName();  
56.                     String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);  
57.                     Class tCls = t.getClass();  
58.                     Method getMethod = tCls.getMethod(getMethodName, new Class[] {});  
59.                     Object value = getMethod.invoke(t, new Object[] {});  
60.                     // 判断值的类型后进行强制类型转换  
61.                     String textValue = null;  
62.                     // 其它数据类型都当作字符串简单处理  
63.                     if(value != null && value != ""){  
64.                         textValue = value.toString();  
65.                     }  
66.                     if (textValue != null) {  
67.                         XSSFRichTextString richString = new XSSFRichTextString(textValue);  
68.                         cell.setCellValue(richString);  
69.                     }  
70.                 }  
71.             }  
72.             getExportedFile(workbook, fileName,response);  
73.         } catch (Exception e) {  
74.             e.printStackTrace();  
75.         }   
76.     }  
77.       
78.     /** 
79.      *  
80.      * 方法说明: 指定路径下生成EXCEL文件 
81.      * @return 
82.      */  
83.     public void getExportedFile(XSSFWorkbook workbook, String name,HttpServletResponse response) throws Exception {  
84.         BufferedOutputStream fos = null;  
85.         try {  
86.             String fileName = name + ".xlsx";  
87.             response.setContentType("application/x-msdownload");  
88.             response.setHeader("Content-Disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ));  
89.             fos = new BufferedOutputStream(response.getOutputStream());  
90.             workbook.write(fos);  
91.         } catch (Exception e) {  
92.             e.printStackTrace();  
93.         } finally {  
94.             if (fos != null) {  
95.                 fos.close();  
96.             }  
97.         }  
98.     }  
99.   
100. }