SSM框架利用POI把数据库文件导出成.xlsx表格文件

一、搭建背景及准备条件
idea，maven3.6.0，jdk1.8，tomcat8，mysql5.6

本文章主要是把数据库文件导出成.xlsx表格文件，搭建SSM框架可以参考上一篇文章，本文章只给出数据库文件导出成.xlsx表格文件所需要的新依赖

 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>

数据库文件jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/tzy01?useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=x5

项目结构

主要加入ExportExcel.java类文件
下面会给出源码

pojo实体类

package com.tzy.pojo;


public class User {
   private Integer id;
   private String name;
   private String email;
   private String sex;
   private String age;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

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

select是本人的方法名，可以更改

controller控制层：

 @RequestMapping("/export")
    public void export(HttpServletRequest request, HttpServletResponse response) {
        User user=new User();
        List<User> list= service.select(user);
        ExportExcel<User> ee= new ExportExcel<User>();
        String[] headers={"学号","姓名","邮箱","性别","年龄"};
        String fileName = "班级.xls";
        ee.exportExcel(headers,list,fileName,response);
    }

dao层接口

 public List<User> select(User user);

service接口

public List<User> select(User user);

service实现类

  @Override
    public List<User> select(User user) {
        return dao.select(user);
    }

ExportExcel.java

package com.tzy;

import org.apache.poi.hssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Iterator;
public class ExportExcel<T> {
    public void exportExcel(String[] headers,Collection<T> dataset, String fileName,HttpServletResponse response) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(fileName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(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++) {
                    HSSFCell 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) {
                        HSSFRichTextString richString = new HSSFRichTextString(textValue);
                        cell.setCellValue(richString);
                    }
                }
            }
            getExportedFile(workbook, fileName,response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * 方法说明: 指定路径下生成EXCEL文件
     * @return
     */
    public void getExportedFile(HSSFWorkbook workbook, String name,HttpServletResponse response) throws Exception {
        BufferedOutputStream fos = null;
        try {
            String fileName = name + ".xls";
            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();
            }
        }
    }
}

xml文件

<?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指定Dao接口的完整类名    mybatis会依据这个接口动态创建一个实现类去实现这个接口，    而这个实现类是一个Mapper对象-->
<mapper namespace="com.tzy.dao.UserDao">
    <select id="select" resultType="com.tzy.pojo.User" parameterType="com.tzy.pojo.User">
        select * from tb_employee
    </select>
</mapper>t>
</mapper>

数据库内容截图

浏览器运行界面

班级.xls文件内容

到此利用SSM框架POI转成.xls文件结束啦