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
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
<exclusions>
<exclusion>
<artifactId>commons-codec</artifactId>
<groupId>commons-codec</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<span>
<label>姓名：</label>
<input id="name" placeholder="请输入姓名" type="text">
</span>
<span>
<label>性别：</label>
<select id="sex" style="height: 24px;width: 163px;">
<option value="">请选择性别</option>
<option value="1">男</option>
<option value="2">女</option>
</select>
</span>
<span>
<label>年龄：</label>
<input id="age" placeholder="请输入年龄" type="text">
</span>
<button class="btn" id="deviceExport">导出</button>
<script type="text/javascript" src="user.js"></script>
- 前台页面效果图
var User = function(){
this.init = function(){
// 用于导出excel
$("#userExport").click(function() {
var url = '/user/export/';
location.href = url + "?queryJson="+JSON.stringify(user.acquireInquireData());
});
};
//获取查询条件
this.acquireInquireData = function(){
var inquireCondition = {
name:$('#name').val(),//名称
sex: $('#sex').val(),//性别
age: $('#age').val(),//年龄
};
return inquireCondition;
};
}
var user;
$(function(){
user = new User();
user.init();
});
/**
* @author 李光光(编码小王子)
* @QQ 826331692
* @date 2016年11月7日 下午2:57:03
* @version 1.0
*/
public class User {
private String name;
private String sex;
private String age;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
}
- 4,controller层代码
/**
* @author 李光光(编码小王子)
* @date 2015年12月29日 下午4:04:00
* @qq 826331692
* @version 1.0
* @return
*/
@Controller
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
/**
* 用于导出excel的查询结果
* @param queryJson
* @return
*/
@RequestMapping("/export")
public void export(HttpServletRequest request, HttpServletResponse response,
@RequestParam(value = "queryJson") String queryJson) {
User user = JSON.parseObject(queryJson, User.class);
List<User> userlList = userService.getUserForExcel(user);
ExportExcel<User> ee= new ExportExcel<User>();
String[] headers = { "序号", "姓名", "性别", "年龄" };
String fileName = "用户信息表";
ee.exportExcel(headers,userlList,fileName,response);
}
}
- 5,service层代码
public interface UserService {
/**
* 根据查询条件查询出所有的记录,不用分页,用于excel导出功能
* @param userDeviceVo
* @return
*/
public List<User> getUserDeviceForExcel(User user);
}
- 6,service实现层代码
/**
* @author 李光光(编码小王子)
* @date 2015年12月29日 下午3:43:08
* @Email 826331692@qq.com
* @version 1.0
* @return
*/
@service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
/**
* 根据查询条件查询出所有的记录,不用分页,用于excel导出功能
* @param userDeviceVo
* @return
*/
@Override
public List<User> getUserDeviceForExcel(User user) {
List<User> list = userDao.getUserForExcel(user);
Integer order;
for (int i = 0; i < list.size(); i++) {
order = i + 1;
list.get(i).setOrder(order.toString());
if (list.get(i).getSex().equals("1")) {
list.get(i).setSex("男");
} else {
list.get(i).setSex("女");
}
}
return list;
}
}
- 6dao层代码
public interface UserDao {
/**
* 根据查询条件查询出所有的记录,不用分页,用于excel导出功能
* @param userDeviceVo
* @return
*/
List<User> getUserForExcel(User user);
}
<?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-3.0.xsd">
<bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>${DB_MSSQL_DRIVER}</value>
</property>
<property name="url">
<value>${DB_MSSQL_URL}</value>
</property>
<property name="username">
<value>${DB_MSSQL_USER}</value>
</property>
<property name="password">
<value>${DB_MSSQL_PW}</value>
</property>
<property name="maxActive">
<value>${maxActive}</value>
</property>
<property name="maxIdle">
<value>${maxIdle}</value>
</property>
<property name="minIdle">
<value>${minIdle}</value>
</property>
<property name="maxWait">
<value>${maxWait}</value>
</property>
<property name="timeBetweenEvictionRunsMillis">
<value>${timeBetweenEvictionRunsMillis}</value>
</property>
<property name="minEvictableIdleTimeMillis">
<value>${minEvictableIdleTimeMillis}</value>
</property>
<property name="testWhileIdle">
<value>${testWhileIdle}</value>
</property>
<property name="testOnReturn" value="true" />
<property name="testOnBorrow" value="true"/>
<property name="validationQuery">
<value>${validationQuery}</value>
</property>
<property name="removeAbandoned">
<value>${removeAbandoned}</value>
</property>
<property name="removeAbandonedTimeout">
<value>${removeAbandonedTimeout}</value>
</property>
</bean><!-- org.mybatis.spring.SqlSessionFactoryBean-->
<bean id="dataSourceCms" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>${DB_MSSQL_CMS_DRIVER}</value>
</property>
<property name="url">
<value>${DB_MSSQL_CMS_URL}</value>
</property>
<property name="username">
<value>${DB_MSSQL_CMS_USER}</value>
</property>
<property name="password">
<value>${DB_MSSQL_CMS_PW}</value>
</property>
<property name="maxActive">
<value>${maxActive}</value>
</property>
<property name="maxIdle">
<value>${maxIdle}</value>
</property>
<property name="minIdle">
<value>${minIdle}</value>
</property>
<property name="maxWait">
<value>${maxWait}</value>
</property>
<property name="timeBetweenEvictionRunsMillis">
<value>${timeBetweenEvictionRunsMillis}</value>
</property>
<property name="minEvictableIdleTimeMillis">
<value>${minEvictableIdleTimeMillis}</value>
</property>
<property name="testWhileIdle">
<value>${testWhileIdle}</value>
</property>
<property name="testOnReturn" value="true" />
<property name="testOnBorrow" value="true"/>
<property name="validationQuery">
<value>${validationQuery}</value>
</property>
<property name="removeAbandoned">
<value>${removeAbandoned}</value>
</property>
<property name="removeAbandonedTimeout">
<value>${removeAbandonedTimeout}</value>
</property>
</bean>
<!-- 编写spring 配置文件的配置多数源映射关系 -->
<bean class="com.jd.iapp.utils.DynamicDataSource" id="dynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry value-ref="dataSource" key="dataSource"></entry>
<entry value-ref="dataSourceCms" key="dataSourceCms"></entry>
</map>
</property>
<property name="defaultTargetDataSource" ref="dataSource">
</property>
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dynamicDataSource" />
<property name="configLocation" value="classpath:spring/sqlMapConfig.xml"></property>
</bean>
<!-- 事务管理器 -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dynamicDataSource"/>
</bean>
<!-- 用户表 -->
<bean id="userDao" class="org.mybatis.spring.mapper.MapperFactoryBean">
<property name="mapperInterface" value="com.jd.xe.web.dao.UserDao" />
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>
</beans>
- 7mybatis代码
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="XXX .UserDao">
<!--根据查询条件查询出所有的记录,不用分页,用于excel导出功能 -->
<select id="getUserForExcel" parameterType="User" resultType="User">
select name,sex,age
from juser_table
where 1=1 and
<if test="name != null and name !=''">and name=#{name}</if>
<if test="sex != null and sex !=''">and sex=#{sex}</if>
<if test="age != null and age !=''">and age=#{age}</if>
</select>
</mapper>
- 8,重头戏来了,将List数据写入到excel的代码如下:
package com.jd.xe.web.service.userDevice;
import java.io.BufferedOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.jd.xe.web.utils.DateUtil;
/**
* @author 李光光(编码小王子)
* @Email 826331692@qq.com
* @date 2016年7月18日 下午9:03:29
* @version 1.0
*/
public class ExportExcel<T> {
public void exportExcel(String[] headers,Collection<T> dataset, String fileName,HttpServletResponse response) {
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(fileName);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 20);
// 产生表格标题行
XSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
try {
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
// 其它数据类型都当作字符串简单处理
if(value != null && value != ""){
textValue = value.toString();
}
if (textValue != null) {
XSSFRichTextString richString = new XSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
}
getExportedFile(workbook, fileName,response);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
*
* 方法说明: 指定路径下生成EXCEL文件
* @return
*/
public void getExportedFile(XSSFWorkbook workbook, String name,HttpServletResponse response) throws Exception {
BufferedOutputStream fos = null;
try {
String fileName = name + ".xlsx";
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ));
fos = new BufferedOutputStream(response.getOutputStream());
workbook.write(fos);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fos != null) {
fos.close();
}
}
}
}
别的框架大体上也是可以的,只需稍微调整,如有问题,大家可留言讨论
实现的功能说明:将数据库中的人员信息(姓名.年龄,电话)导出到excel
具体代码如下
1,前台html代码
[html] view plain copy
2,js代码
[javascript] view plain copy
3,domain的user实体类(该实体类如果作为最终导出结果的实体类,则该类的字段必须与需要导出的字段保持一致,且与导出列表的顺序也得保持一致)
[java] view plain copy
[java] view plain copy
[java] view plain copy
[java] view plain copy
[java] view plain copy
需要在配置文件中加载UserDao的信息,否则无法找到UserDao,配置如下:
[html] view plain copy
[html] view plain copy
[java] view plain copy
