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

最新推荐文章于 2024-09-05 17:29:21 发布

oh_EJiao

最新推荐文章于 2024-09-05 17:29:21 发布

阅读量513

点赞数

分类专栏： JAVA

JAVA 专栏收录该内容

44 篇文章

订阅专栏

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

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

项目框架

1,后台:spring+springmvc+mybatis

2,前台: bootstrap+jQuery+ajax

3,项目管理:maven

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

 
   [html]  
   view plaincopy
<!-- 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代码

 
   [html]  
   view plaincopy
<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代码

 
   [html]  
   view plaincopy
var User = function(){  
    this.init = function(){  
         // 用于导出excel  
        $("#userExport").click(function() {  
            var url =  '/user/export/';  
            var params = JSON.stringify(user.acquireInquireData());  
            //如果出现中文乱码情况请添加下面这句代码  
            params = encodeURI(encodeURI(params)  
            location.href = url + "?queryJson="+params;  
        });  
    };  
    //获取查询条件  
    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实体类(该实体类如果作为最终导出结果的实体类,则该类的字段必须与需要导出的字段保持一致,且与导出列表的顺序也得保持一致)

 
   [java]  
   view plaincopy
/**   
 * @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层代码

 
   [java]  
   view plaincopy
/** 
 * @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) {  
        //如果出现中文乱码请添加下面这句  
        queryJson = URLDecoder.decode(queryJson,"utf-8");   
        //需要导入alibaba的fastjson包  
        User user = com.alibaba.fastjson.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层代码

 
   [java]  
   view plaincopy
public interface UserService {    
    
    /** 
     * 根据查询条件查询出所有的记录,不用分页,用于excel导出功能   
     * @param userDeviceVo 
     * @return 
     */  
    public List<User> getUserDeviceForExcel(User user);   
    
}    

6,service实现层代码

 
   [java]  
   view plaincopy
/** 
 * @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层代码

 
   [java]  
   view plaincopy
public interface UserDao {    
    
  /** 
     * 根据查询条件查询出所有的记录,不用分页,用于excel导出功能   
     * @param userDeviceVo 
     * @return 
     */  
    List<User> getUserForExcel(User user);  
  
    
}    

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

 
   [html]  
   view plaincopy
<?xml version="1.0" encoding="UTF-8"?>  
<beans xmlns="http://www.springframework.org/schema/beans"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xmlns:context="http://www.springframework.org/schema/context"  
    xmlns:aop="http://www.springframework.org/schema/aop"  
    xmlns:tx="http://www.springframework.org/schema/tx"  
    xsi:schemaLocation="  
    http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd  
    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd  
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd  
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-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>  
      
  
    <!-- 事务管理器 -->  
    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
        <property name="dataSource" ref="dataSource"/>  
    </bean>  
    <tx:annotation-driven transaction-manager="txManager"/>  
      
  
    <!--自动加载所有的mapper.xml文件,不再需要单独配置  -->  
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
        <property name="dataSource" ref="dataSource"/>  
        <property name="configLocation" value="classpath:spring/sqlMapConfig.xml"/>  
        <property name="mapperLocations">  
            <value>classpath:mybatis/*.xml</value>  
        </property>  
    </bean>  
  
    <!--自动加载所有的mapper.xml所对应的dao层接口,不再需要单独配置  -->  
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">  
        <property name="basePackage" value="com.jd.am.visit.dao"/>  
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>  
    </bean>  
      
      
      
</beans>  

7mybatis代码

 
   [html]  
   view plaincopy
  <?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的代码如下:

 
   [java]  
   view plaincopy
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();  
            }  
        }  
    }  
  
}  