java将数据库里的数据导出到excel

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

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

项目框架

1,后台:spring+springmvc+mybatis

2,前台: bootstrap+jQuery+ajax

3,项目管理:maven

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

<!-- 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>  

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

实现的功能说明:将用户信息(姓名,性别,年龄)通过excel上传,并保存到数据库

具体代码如下

1,前台html代码

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

前台页面效果图

2,js代码

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();});

3,domain的user实体类

/**   * @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 {@Autowiredprivate 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 [e-mail]  * @version 1.0 * @return */@servicepublic class UserServiceImpl implements UserService {@Autowiredprivate UserDao  userDao;/** * 根据查询条件查询出所有的记录,不用分页,用于excel导出功能   * @param userDeviceVo * @return */@Overridepublic 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);  }  

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

<?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   [e-mail]  * @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+DateUtil.parseSmallDate(new Date()) + ".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();}}}}

至此整个代码已经完成了,如有什么不明白的地方,可以评论,我会及时回复大家,着急的话也可以QQ:826331692联系我,互相学习,共同进步.