使用poi工具将sql查询结果写入excel文件(报表的导出数据功能)

这里以查询mysql数据并导出为excel为例子

关于创建数据库及表数据导入这里没讲，可以自行创建和插入测试数据
这里用到的数据库是poi
mysql表为test

1 首先创建一个maven项目 demo_poi

2 pom.xml中引入mysql连接的驱动包，以及poi需要使用的jar包

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.25</version>
</dependency>     
<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.0</version>
</dependency>

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

3 在resource目录下创建db.properties配置文件，配置jdbc连接相关信息

jdbcName=com.mysql.jdbc.Driver
dburl=jdbc:mysql://localhost:3306/poi?useUnicode=true&characterEncoding=utf8&serverTimeZone=GMT%2B8&userSSL=false
dbName=root
dbPwd=123456

4 创建一个数据库连接的工具类DBUtil

package com.mmz.note.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author admin
 * @version 1.0.0
 * @ClassName DBUtil.java
 * @Description 
 * @createTime 2022年01月24日 23:20:00
 */
public class DBUtil {

    //定义一个静态的Properties
    private static Properties properties = new Properties();

    static  {
        try {
            //加载配置文件
            InputStream in = DBUtil.class.getClassLoader().getResourceAsStream("db.properties");
            // 通过load()方法将输入流的内容加载到配置文件对象中
            properties.load(in);
            // 通过配置文件对象的getProoperties方法获取驱动名，并加载驱动
            Class.forName(properties.getProperty("jdbcName"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取数据库连接
    public static Connection getConnection(){
        Connection connection  = null;


        try {
            // 得到数据库连接的相关信息
            String dburl = properties.getProperty("dburl");
            String dbName = properties.getProperty("dbName");
            String dbPwd = properties.getProperty("dbPwd");
            // 得到数据库连接
            connection = DriverManager.getConnection(dburl, dbName, dbPwd);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return  connection;

    }

    // 关闭数据库连接相关的资源
    public  static void close(ResultSet resultSet, PreparedStatement preparedStatement,Connection connection){
        // 判断资源如果不为null则关闭

            try {
                if(resultSet != null){
                resultSet.close();
                }

                if(preparedStatement != null){
                    preparedStatement.close();
                }

                if(connection != null){
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
        }
    }
}

5 这里介绍下mysql表的结构及数据

 CREATE TABLE poi.test (
  provincename varchar(255) DEFAULT NULL,
  cityname varchar(255) DEFAULT NULL,
  counts int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 数据
mysql> select  * from poi.test limit 4;
+----------------+----------+--------+
| provincename   | cityname | counts |
+----------------+----------+--------+
| 湖北省         | 荆州市   |      2 |
| 江西省         | 宜春市   |      5 |
| 宁夏回族自治区 | 石嘴山市 |      3 |
| 山西省         | 临汾市   |      1 |
+----------------+----------+--------+

6 创建一个数据库表到javaBean的映射类Province

package com.mmz.note.service;

/**
 * @author admin
 * @version 1.0.0
 * @ClassName Province.java
 * @Description TODO
 * @createTime 2022年02月08日 22:44:00
 */
public class Province {
    public String provinceName ;
    public String cityName ;
    public int counts ;

    public Province(String provinceName, String cityName, int counts) {
        this.provinceName = provinceName;
        this.cityName = cityName;
        this.counts = counts;
    }

    public String getProvinceName() {
        return provinceName;
    }

    public void setProvinceName(String provinceName) {
        this.provinceName = provinceName;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public int getCounts() {
        return counts;
    }

    public void setCounts(int counts) {
        this.counts = counts;
    }
}

7 创建一个测试类Test

package com.mmz.note.service;

import com.mmz.note.util.DBUtil;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.math.BigInteger;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;

/**
 * @author admin
 * @version 1.0.0
 * @ClassName Test.java
 * @Description TODO
 * @createTime 2022年02月08日 22:36:00
 */
public class Test {


    public static void main(String[] args) throws Exception {
        //返回数据库连接
        Connection conn = DBUtil.getConnection();

        //执行sql查询并返回查询结果
        PreparedStatement preparedStatement = conn.prepareStatement("select * from ad_info.test");
        ResultSet resultSet = preparedStatement.executeQuery();

        //通过反射拿到Class中所有的字段
        Field[] declaredFields = Province.class.getDeclaredFields();
        //创建一个集合，反省为Province
        ArrayList<Province> dataList = new ArrayList<Province>();

        //判断查询结果中是否有下一条数据
        while (resultSet.next()){
            //将查询结果的一条完整数据封装到province对象中
            Province province = new Province(resultSet.getString(1), resultSet.getString(2), resultSet.getInt(3));
            //将封装的province对象放入之前创建的集合中
            dataList.add(province);
        }

        // 将集合返回一个迭代器用于遍历
        Iterator<Province> iterator = dataList.iterator();

        //创建工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook();

        //创建sheet
        SXSSFSheet sheet = workbook.createSheet("临时表结果");
        
        //不限制内存中缓存记录数
        sheet.setRandomAccessWindowSize(-1);

        //设置行数的初始值为0
        Integer startRows =  0;
        // 判断迭代器是否有下一条数据
        while (iterator.hasNext()){
            //取到当前的封装对象
            Province next = iterator.next();
            //创建行 从0开始
            SXSSFRow row = sheet.createRow(startRows);
            //遍历表对象的属性
            for (int i = 0; i < declaredFields.length; i++) {
                // 创建单元格 从0开始
                SXSSFCell cell = row.createCell(i);
                // 判断当行数为0是，将表对象的属性名称依次赋值
                if(startRows == 0){
                    cell.setCellValue(declaredFields[i].getName());
                }else{
                //当行数不为0时，取得表对象对应属性的值    
                    cell.setCellValue(String.valueOf(declaredFields[i].get(next)));
                }
            }
            //行数自增1
            startRows++;
        }
        //创建一个文件输出流，并指定输出文件路径和文件名称,确保目录路径存在
        FileOutputStream fileOutputStream = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\test\\获取表查询数据.xlsx"));
        //将数据写入excel中
        workbook.write(fileOutputStream);
    }
}

8 点击main方法运行，可以看到在test目录下确实生成了获取表查询数据.xlsx文件

9 到这里mysql查询结果数据写入excel的demo就结束了