使用poi获取数据库数据写入excel

前言

本项目为maven项目，纯后端，实现的功能为获取数据库表中的数据，通过poi写入到excel。
同时，根据我上一篇的文章，进行增大难度，进行配置双数据库，从两个数据库获取内容。
这个最主要的原因还是，在实际的工作中，你很大概率不可能一次只从一个数据库获取数据。依据我下面将要讲解的功能原理实现，可以供大家参考和思考更深一步的实现。

同时还有一个写这个文章的最主要原因就是，我查找资料，网上的绝大部分都是从excel获取数据写入到数据库，因此有了写这篇文章的想法。

在这里贴出来执行成功后的excel文件截图

需要的工具

第一个就是根据需要自己去找一个可视化数据库的管理工具，这里我用的是navicat
我这里是进行java开发，所以是使用idea进行构建

然后就是看你要配置的数据库是什么类型的，进行安装相关的数据库和配置。
这个项目中我是使用的mysql和sqlite数据库

navicat
idea

数据库配置：
mysql
sqlite

第一步：创建相关数据库表

在正式构建项目之前，你肯定需要有数据进行写入，所以需要先进行创建表。

在本项目中，我在mysql数据库创建了student表，字段如下：

create table Student(
	id int(5) not null primary key,
	name varchar(20) default null,
	age varchar(5) default null,
	score int(5) default null,
	time datetime default null
)

往student表随意插入几条数据

---

在sqlite创建Teacher表，字段如下：

create table Student(
	id int(5) not null primary key,
	name varchar(20) default null,
	age varchar(5) default null,
	type int(5) default null,
)

第二步创建项目

1.创建一个空的maven项目

2.填写组织名和自己的项目名创建即可

规划项目结构

如下图，我在src/main/com下面创建了四个文件夹和一个App启动类，并在resource下创建一个mapper文件现分别对其作一个说明。

api 该文件夹为功能实现

model 该文件夹为对应数据库实体类

mapper 实现从数据库查询方法的实现

util 工具类 这里主要实现数据库配置文件的配置

resource/mapper 该文件主要放置数据库查询语句的配置文件

App 启动类，启动项目

引入pom.xml

接下来根据需要引入pom.xml依赖,根据思路我们之所以创建一个空的maven项目就是为了保证项目不凌乱，能够按需引入。

为了连接sql需要sql的依赖

为了连接sqlite，需要sqlite的依赖

为了配置数据库，需要mybatis

为了节省实例化代码，需要引入lombok

为了写入excel需要引入poi依赖

因此最终pom文件代码为：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.Alice</groupId>
    <artifactId>poiToSql</artifactId>
    <version>1.0-SNAPSHOT</version>

<!--    放入依赖-->
    <dependencies>
<!--        poi为将数据库导入到excel的依赖-->
<!--        poi和poi-ooxml区别在于生成的是什么格式的excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
<!--        mybatis为配置数据库的依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>
<!--        mysql为sql的驱动依赖配置-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
        </dependency>

<!--        sqlite驱动-->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.21.0.1</version>
        </dependency>
<!--    lombok    通过添加注解的方式，不需要为类编写getter或setter方法，同时可以自动化日志变量-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.4</version>
            <scope>provided</scope>
        </dependency>

    </dependencies>


</project>

配置数据库

要配置数据库，需要配置的有这几个地方：

com/mapper 作为查询数据库的实现方法

util 编写一个mapperutil工具类，实现引入数据库驱动配置

resource/mapper 编写数据库的查询语句的xml

resource 在这里需要创建对应的数据库驱动配置xml文件，以供在工具类进行引入配置

在下面讲解完剩下的项目结构将贴出来完整项目结构

不同层级需要创建的文件

在api文件夹创建一个POIWrite文件，进行后续的书写写入excel功能

在mapper下创建sql和sqlite的mapper文件，为区分开，可分别在mapper下创建sql和sqlite文件夹 

在mapper/sql 创建poiMapper_sql文件

在mapper/sqlite 创建poiMapper_sqlite文件

在model创建数据库查询的具体实体类，前面说过，在sql查student，sqlite查teacher
所以创建Student和Teacher两个文件

在util文件夹下创建 MapperUtil工具类

在com下创建App，添加main方法，作为启动类

在resource/mapper下创建poiMapper_sql.xml和poiMapper_sqlite.xml

在resource下创建poi-config-mapper-sql.xml和poi-config-mapper-sqlite.xml

具体的文件项目结构

思路顺序

一下创建了这么多文件有没有觉得比较乱？跟着思路走还是能够很快理清的。

那么在这里进行一个思路讲解

先编写好数据库驱动文件，以便于后面启动数据库

想启动数据库那么就得进行配置，因此在工具类进行引入

然后编写数据库查询文件，以便于从数据库获取数据

获取数据后你想要操作获取的数据，就需要mapper编写具体的实现方法

有了实现方法后，你想写到excel就需要具体的功能实现POI写入

跟着这个思路，我们依次进行编写代码

数据库驱动文件编写

因为要配置两个数据库，所以有两个配置文件

我的是sql和sqlite数据库,这里具体配置哪个就看你需要配置哪个

poi-config-mapper-sql.xml代码

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
<!--本文件进行配置sql的驱动配置-->
    <environments default="default">
        <!--环境变量-->
        <environment id="default">
            <!--
                事务管理器：type
                    1. JDBC: 由JDBC管理事务
                    2. MANAGED：由容器管理事务，Spring
            -->
            <transactionManager type="JDBC"/>
            <!--
                数据源配置信息：type
                    1. UNPOOLED：不使用连接池
                    2. POOLED： 使用连接池
            -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/poi"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>

    </environments>

    <!-- 加载所有的映射文件 -->
    <mappers>
        <!--加载一个接口的映射文件-->
        <mapper resource="mapper/poiMapper_sql.xml"/>
    </mappers>
</configuration>

在这里你注意到了resource了吗？这里的resource指的是
你这个数据库是需要对应实现哪个文件的增删改查语句

以下同理

poi-config-mapper-sqlite.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="org.sqlite.JDBC"/>
                <property name="url" value="jdbc:sqlite:E:\software\sqllite\data\sqlite.db"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>

    </environments>
    <mappers>
        <mapper resource="mapper/poiMapper_sqlite.xml"/>
    </mappers>
</configuration>

sqlite的话，除了resource和上面的同理，还有一个需要特别注意

它的driver对应的为org.sqlite.JDBC
url对应的为jdbc:sqlite:加上sqlite数据的具体路径

编写mapperUtil工具类

该工具类的具体实现如下

package com.util;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import javax.annotation.Resource;
import java.io.IOException;

// 实现运行配置好的数据库驱动文件
public class MapperUtil {
//静态修饰获取数据配置驱动文件
//    获取sql配置数据库文件
    public static final SqlSessionFactory sqlSession_sql=geSqlSessionFactory("poi-config-mapper-sql.xml");
//    获取sqlite配置文件驱动
    public static final SqlSessionFactory sqlSession_sqlite=geSqlSessionFactory("poi-config-mapper-sqlite.xml");
    
    //以下为具体获取Session配置文件的方法
    private static SqlSessionFactory geSqlSessionFactory(String configFile){
       //初始化
        SqlSessionFactory sqlSessionFactory = null;
        try{
            sqlSessionFactory =new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(configFile));
        }catch (IOException e){
            e.printStackTrace();
        }
        return sqlSessionFactory;
    }
}

这里的配置文件填写的是刚刚数据库驱动配置的文件

编写数据库增删改查配置文件

同样有两个数据库查询文件

poiMapper_sql.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="com.mapper.sql.poiMapper_sql">

<!--    从sql数据库查找student表数据，并映射到model实体-->
    <select id="findStudentInfo" resultType="com.model.Student">
        select name,age,score,time from student
    </select>
    
</mapper>

poiMapper_sqlite.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="com.mapper.sqlite.poiMapper_sqlite">

    <select id="findTeacherInfo" resultType="com.model.Teacher">
        select name,age,type from teacher
    </select>
    
</mapper>

这里需要注意的点，id为mapper实现对应的方法名，
resultType为查询对应的实体映射，
如果是增加和更新等就不是使用resultType
而是使用parameterType=""

mapper实现

mapper注意这里是接口，使用的是interface

poiMapper_sql

package com.mapper.sql;
import com.model.Student;
import java.util.List;
//实现查询数据库语句的实现，并映射到student实体
//查询多个使用List<>集合，单个直接使用实体对象即可
//如 Student findStudentInfo();
public interface poiMapper_sql {
    //    映射到Student实体，实现查找学生表数据信息
    List<Student> findStudentInfo();
}

poiMapper_sqlite

package com.mapper.sqlite;
import com.model.Teacher;
import java.util.List;

public interface poiMapper_sqlite {
//    映射到teacher实体
    List<Teacher> findTeacherInfo();
}

poi写入Excel

数据库这样配置完就已经能够正确的连接上了，连接完就该实现poi写入excel的功能了。

package com.api;

import com.mapper.sql.poiMapper_sql;
import com.mapper.sqlite.poiMapper_sqlite;
import com.model.Student;
import com.model.Teacher;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class POIWrite {
    //以形参的方式传入sql、sqlite调用
    public static String poiListExcel(poiMapper_sql sqlMapper, poiMapper_sqlite sqliteMapper)throws Exception{

       //创建格式化时间内容(当数据库表有时间的情况)
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf1=new SimpleDateFormat("yyy-MM-dd hh:mm:ss");

        //创建一个workbook 对应一个Excel文件
        HSSFWorkbook workbook=new HSSFWorkbook();

        //创建excel的标题行,将student和teacher信息发、分别放置在不同的sheet上
        //学生字段有name,age,score,time
        //老师字段有name,age,type
        String[] title_Student={"序号","学生姓名","学生年龄","学生分数","发布时间"};
        String[] title_Teacher={"序号","老师姓名","老师年龄","教书类型"};

        //设置sheet名称，并创建新的sheet对象
        String sheetName_student="学生信息一览";
        Sheet stuSheet =workbook.createSheet(sheetName_student);

        String sheetName_teacher="教师信息一览";
        Sheet teaSheet =workbook.createSheet(sheetName_teacher);

        //获取表头行
        //poi以row、cell表示横纵
        Row titleRow=stuSheet.createRow(0);
        Row titleRow_tea=teaSheet.createRow(0);
        //创建单元格，设置style居中，字体、单元格大小
        CellStyle style = workbook.createCellStyle();
        Cell cell=null;

        //把写好的标题行写入excel
        for(int i =0;i<title_Student.length;i++){
            cell=titleRow.createCell(i);
            cell.setCellValue(title_Student[i]);
            cell.setCellStyle(style);
        }
        for(int i=0;i<title_Teacher.length;i++){
            cell=titleRow_tea.createCell(i);
            cell.setCellValue(title_Teacher[i]);
            cell.setCellStyle(style);
        }

        //创建一个List集合接收从数据库获取的student信息
        //并从形参获取的数据库获取查询
        List<Student> studentInfo=sqlMapper.findStudentInfo();
        System.out.println(studentInfo);
        //创建一个List集合接收从数据库获取的teacher信息
        List<Teacher> teacherInfo=sqliteMapper.findTeacherInfo();
        System.out.println(teacherInfo);
        //把数据库取到的数据写入excel
        Row row=null;
//        遍历student信息加入excel
        for(int i=0;i<studentInfo.size();i++){
            //创建list.size()行数据
            row=stuSheet.createRow(i+1);

            //把值一一写入单元格
            //设置第一列自动递增的序号
            row.createCell(0).setCellValue(i+1);
            row.createCell(1).setCellValue(studentInfo.get(i).getName());
            row.createCell(2).setCellValue(studentInfo.get(i).getAge());
            row.createCell(3).setCellValue(studentInfo.get(i).getScore());
            row.createCell(4).setCellValue(sdf1.format(studentInfo.get(i).getTime()));

            //遍历teacher信息加入excel
            for(int j=0;j<teacherInfo.size();j++){
                //创建list.size()行数据
                row=teaSheet.createRow(j+1);

                //把值一一写入单元格
                //设置第一列自动递增的序号
                row.createCell(0).setCellValue(j+1);
                row.createCell(1).setCellValue(teacherInfo.get(j).getName());
                row.createCell(2).setCellValue(teacherInfo.get(j).getAge());
                row.createCell(3).setCellValue(teacherInfo.get(j).getType());
            }
        }


        //单元格自适应
        for(int i=0;i<title_Student.length;i++){
            stuSheet.autoSizeColumn(i,true);
            stuSheet.setColumnWidth(i,stuSheet.getColumnWidth(i)*15/10);
        }
        for(int i=0;i<title_Teacher.length;i++){
            teaSheet.autoSizeColumn(i,true);
            teaSheet.setColumnWidth(i,teaSheet.getColumnWidth(i)*15/10);
        }

        //获取配置文件中保存对应excel文件的路径，本地写成如：D：xxx/xxx
        String folderPath ="D:\\教师学生信息";
        //创建上传文件目录
        File folder =new File(folderPath);

        //判断文件已经存在
        if(!folder.exists()){
            folder.mkdir();
        }
        //设置文件名。这里以文本+时间的形式创建
        String excelName="学生教师信息";
        String time=sdf.format(new Date());
        String fileName=excelName+time+".xlsx";

        //设置文件输出路径
        String savePath =folderPath+File.separator+fileName;
        OutputStream fileOut=new FileOutputStream(savePath);
        workbook.write(fileOut);
        fileOut.close();

        return savePath;
    }
}

在这里我创建了两个sheet，分别接收学生和老师的数据

为了检测有没有获取数据库的值，可以在这个代码的地方
        //创建一个List集合接收从数据库获取的student信息
        //并从形参获取的数据库获取查询
        List<Student> studentInfo=sqlMapper.findStudentInfo();
        System.out.println(studentInfo);
        //创建一个List集合接收从数据库获取的teacher信息
        List<Teacher> teacherInfo=sqliteMapper.findTeacherInfo();
        System.out.println(teacherInfo);
用System.out.println()在控制台输出对应的集合信息

如图：

编写启动类

到这里功能都已经实现了，我们就需要开始编写启动类来执行功能代码

App启动类代码

import com.api.POIWrite;
import com.mapper.sql.poiMapper_sql;
import com.mapper.sqlite.poiMapper_sqlite;
import com.util.MapperUtil;
import org.apache.ibatis.session.SqlSession;

public class App {
    //这里为启动类，在这里调用都需要进行静态修饰
    private static SqlSession sqlSession_sql;
    private static SqlSession sqlSession_sqlite;
    //获取mapper实现
    private static poiMapper_sql mapper_sql;
    private static poiMapper_sqlite mapper_sqlite;

    //获取poi写入excel的具体实现方法
    private static POIWrite poiWrite;

    //判断Session是否为空，为空关闭
    private static void colse(){
        if(sqlSession_sql!=null){
            sqlSession_sql.close();
        }
        if(sqlSession_sqlite!=null){
            sqlSession_sqlite.close();
        }
    }
    
    public static void main(String[] args) throws Exception{
        //sql
        //通过实现的MapperUtil工具开启Session
        sqlSession_sql= MapperUtil.sqlSession_sql.openSession();
        //开启session后获取具体的mapper查询实现文件
        mapper_sql=sqlSession_sql.getMapper(poiMapper_sql.class);

        //sqlite
        sqlSession_sqlite=MapperUtil.sqlSession_sqlite.openSession();
        mapper_sqlite=sqlSession_sqlite.getMapper(poiMapper_sqlite.class);

        //调用poi写入excel方法
        POIWrite.poiListExcel(mapper_sql,mapper_sqlite);
    }
}

注意事项：
这里是会有异常的，使用throws或者try{}catch{}解决即可
在调用poi的方法中，是需要注意形参的类型和个数需要对应上
我这里是为了从形参传入mapper实现，所以在启动类这也有传入
main方法 是需要静态修饰，否则会报错

总结

以上，这个功能就实现了。项目代码已经打包到了gitee。
地址：https://gitee.com/spring-in-huangxian-county/poiToSql

最后给自己立个flag吧，后面找时间完成读取显示的功能吧，和本次功能是反着来的。