最近看了自己玩过的很多项目，忽然发现有一个在实际开发中我们经常用到的功能，但是我没有正儿八经的玩过这个功能，那就是在Spring Boot中实现一个excel报表的导入导出功能，这篇博客，主要是围绕excel报表数据导入进行，感兴趣的可以看看，有能力的可以在本篇博客的基础上进行改造，实现一个前端报表导入导出功能。
要在Spring Boot中使用Apache POI导入Excel数据到MySQL，我们需要一步一步来，接下来这些是我们的主要步骤。
- 引入依赖：我们需要确保pom.xml中包含了Apache POI和相关依赖。
- 配置数据源：在Spring Boot的配置文件application.properties或者application.yml中配置MySQL数据源。
- 创建相关实体类：创建一个实体类来映射数据库中对应的表。
- 创建相关接口：用来实现相关的业务功能。
- 创建相关接口实现类
- 新建一个excel表格，填充数据
- 创建对应数据库和数据库表：实际上这一步是放在第二位的，先有表在有实体类
- 测试功能是否实现
文件目录如下：
引入依赖
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.11.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.miaow</groupId>
<artifactId>spring-excel</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-excel</name>
<description>spring-excel</description>
<properties>
<java.version>8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.11</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.9</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
配置数据源
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&allowMultiQueries=true&rewriteBatchedStatements=true
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.initial-size=5
spring.datasource.druid.max-active=20
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-wait=6000
mybatis.mapper-locations=classpath:mapper/**.xml
# 你实体类存放包路径
mybatis.type-aliases-package=com.miaow.model
logging.level.root = error
logging.level.com.miaow = debug
创建实体类Man
@Data
@AllArgsConstructor //有参构造，全部参数的构造方法
@NoArgsConstructor //无参构造
@Builder //@Builder声明实体，表示可以进行Builder方式初始化
public class Man {
private Long id;
private String name;
private String company;
private String sex;
private String lover;
private String address;
}
创建相关接口
@Mapper
public interface ExcelDao {
@Insert("insert into exportexcel(`name`,`company`,`sex`,`lover`,`address`) VALUES(#{name},#{company},#{sex},#{lover},#{address})")
void insert(Man man);
void add(List<Man> mans);
}
对应Mapper文件的映射文件ExcelMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.miaow.dao.ExcelDao">
<insert id="add">
insert into exportexcel(name,company,sex,lover,address) values
<foreach collection="list" item="man" separator=",">
(#{man.name},#{man.company},#{man.sex},#{man.lover},#{man.address})
</foreach>
</insert>
</mapper>
定义时间注解
/**
* Java注解，用于标记一个方法，该方法用于测量代码的执行时间。
*
* @Target(ElementType.METHOD)：该注释适用于方法。
*
* @Retention(RetentionPolicy.RUNTIME)：该注释将被保留在运行时。
*
* @Timing：这是注释的名称，它表示该方法用于测量代码的执行时间。
*/
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Timing {
}
打印执行时间日志的类
/**
* 用于在Java方法执行前后打印方法执行时间
*/
@Aspect
@Component
@Slf4j
public class TimingInterceptor {
// 这段代码用于在方法执行前后打印方法执行时间
@Around("@annotation(com.miaow.until.Timing)")
public Object countTime(ProceedingJoinPoint pjp) throws Throwable {
long startTime = System.currentTimeMillis();
Object result = pjp.proceed();
long endTime = System.currentTimeMillis();
log.debug(pjp.getSignature().getDeclaringTypeName() + "." + pjp.getSignature().getName() + " 执行时间：" + (endTime - startTime) + "ms");
return result;
}
}
业务实现类
@Service
@Slf4j
public class ExcelService {
@Autowired
SqlSessionFactory sqlSessionFactory;
@Autowired
ExcelDao excelDao;
/**
* 该方法用于批量添加数据到数据库中，具体步骤如下：
* 1. 读取Excel文件，获取数据。
* 开启一个新的SqlSession，并执行批量添加操作。
* 计算总共花费的时间。
*/
public String add() {
long start0 = System.currentTimeMillis();
ExcelReader reader = ExcelUtil.getReader(FileUtil.file("C:\\Users\\29283\\Desktop\\test.xlsx"), "sheet1");
List<Man> mans = reader.readAll(Man.class);
long start = System.currentTimeMillis();
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
ExcelDao mapper = sqlSession.getMapper(ExcelDao.class);
mapper.add(mans);
sqlSession.commit();
long end = System.currentTimeMillis();
System.out.println("最终的结果为：" + (start - start0) );
System.out.println("最终的结果为：" + (end - start));
return null;
}
public String add2() {
long start0 = System.currentTimeMillis();
ExcelReader reader = ExcelUtil.getReader(FileUtil.file("C:\\Users\\29283\\Desktop\\test.xlsx"), "sheet1");
List<Man> mans = reader.readAll(Man.class);
long start = System.currentTimeMillis();
excelDao.add(mans);
long end = System.currentTimeMillis();
System.out.println("最终的结果为：" + (start - start0) );
System.out.println("最终的结果为：" + (end - start) );
return null;
}
/**
* 1. 首先使用hutool工具类读取Excel文件，并将读取到的结果转换为List<Man>集合。
* 2. 读取Excel文件的结束时间同时也是将数据插入数据库的开始时间
* 3. 通过SqlSessionFactory获取SqlSession，并使用getMapper方法获取ExcelDao的代理对象。
* 4. 遍历List<Man>集合中的数据，并将其插入到数据库中。
* 5. 提交事务，并关闭SqlSession。
* 6. 打印出读取Excel文件的总时间和插入数据库的时间。
*/
public void insert1() {
//用于记录读取数据所需要的时间
long start0 = System.currentTimeMillis();
//ExcelUtil hutool工具类用来读取Excel文件
ExcelReader reader = ExcelUtil.getReader(FileUtil.file("C:\\Users\\29283\\Desktop\\test.xlsx"), "sheet1");
//将读取到的 reader 转化为 List<Man>集合
List<Man> mans = reader.readAll(Man.class);
//读取数据的结束时间同时也是写入数据库的开始时间
long start = System.currentTimeMillis();
//sqlSessionFactory是通过ioc容器注入的 设置其SqlSession的执行器格式ExecutorType.SIMPLE（默认）
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.SIMPLE);
ExcelDao mapper = sqlSession.getMapper(ExcelDao.class);
//循环将List<Man>中的数据插入数据库
for (Man man : mans) {
// excelDao.insert(man);
mapper.insert(man);
}
sqlSession.commit();
long end = System.currentTimeMillis();
sqlSession.close();
System.out.println("读取最终的结果为：" + (start - start0) );
System.out.println("插入数据库最终的结果为：" + (end - start) );
}
public String insert2() {
long start0 = System.currentTimeMillis();
ExcelReader reader = ExcelUtil.getReader(FileUtil.file("C:\\Users\\29283\\Desktop\\test.xlsx"), "sheet1");
List<Man> mans = reader.readAll(Man.class);
long start = System.currentTimeMillis();
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
ExcelDao mapper = sqlSession.getMapper(ExcelDao.class);
for (Man man : mans) {
mapper.insert(man);
}
sqlSession.commit();
long end = System.currentTimeMillis();
sqlSession.close();
System.out.println("读取最终的结果为：" + (start - start0) );
System.out.println("插入数据库最终的结果为：" + (end - start) );
return null;
}
public void insert3() {
long start0 = System.currentTimeMillis();
ExcelReader reader = ExcelUtil.getReader(FileUtil.file("C:\\Users\\29283\\Desktop\\test.xlsx"), "sheet1");
List<Man> mans = reader.readAll(Man.class);
long start = System.currentTimeMillis();
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.REUSE);
ExcelDao mapper = sqlSession.getMapper(ExcelDao.class);
for (Man man : mans) {
mapper.insert(man);
}
sqlSession.commit();
long end = System.currentTimeMillis();
sqlSession.close();
System.out.println("读取最终的结果为：" + (start - start0) );
System.out.println("插入数据库最终的结果为：" + (end - start) );
}
public String add3() {
ExcelReader reader = ExcelUtil.getReader(FileUtil.file("C:\\Users\\29283\\Desktop\\test.xlsx"), "sheet1");
long start1 = System.currentTimeMillis();
List<Man> mans = reader.readAll(Man.class);
int batchSize = 2000;
List<List<Man>> batches = new ArrayList<>();
List<Man> batch = new ArrayList<>(batchSize);
long start = System.currentTimeMillis();
for (int i = 0; i < mans.size(); i++) {
Man man = mans.get(i);
batch.add(man);
if (batch.size() == batchSize) {
batches.add(batch);
batch = new ArrayList<>(batchSize);
}
}
if (!batch.isEmpty()) {
batches.add(batch);
}
int i = 0;
try {
//设置门栓，目的是：让多线程执行完成后才 走主线程业务
CountDownLatch latch = new CountDownLatch(batches.size());
//利用多线程批量 执行sql语句
for (List<Man> man : batches) {
executor.submit(() -> {
// SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
// ExcelDao mapper = sqlSession.getMapper(ExcelDao.class);
// mapper.add(man);
// sqlSession.commit();
// sqlSession.close();
excelDao.add(man);
latch.countDown(); //每执行一次线程 减1
});
}
//等待门栓为0，执行主线程代码
latch.await();
// Thread.sleep(10000);
} catch (Exception e) {
//sqlSession.rollback();
} finally {
//sqlSession.close();
// 关闭线程池
executor.shutdown();
}
long end = System.currentTimeMillis();
System.out.println("最终的结果为：" + (start - start1));
System.out.println("最终的结果为：" + (end - start));
return null;
}
/**
*1. 读取Excel文件，并获取读取器。
* 2. 设置要读取的Excel页。
* 3. 创建SqlSession并获取对应的Mapper。
* 4. 定义一个存储读取的数据的List。
* 5. 定义一个存储学生数据的List。
* 6. 定义一个存储学生数据的Map，key为学生id，value为学生对象。
* 7. 创建一个线程池，并使用线程池批量添加学生数据。
*/
public Integer add4() {
//1、获取Excel文件（只是创建ExcelReader读取器，还没有开始真正的读）
ExcelReader reader = ExcelUtil.getReader("C:\\Users\\29283\\Desktop\\test.xlsx");
//设置读取的excell页
reader.setSheet("Sheet1");
//优化sql插入 ExecutorType.BATCH
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
ExcelDao mapper = sqlSession.getMapper(ExcelDao.class);
//存储读取的数据
List<List<Object>> readList = null;
List<Man> students = null;
Map<Integer, List<Man>> map = null;
ExecutorService executor = Executors.newFixedThreadPool(10);
try {
//计时器
StopWatch stopWatch = new StopWatch();
stopWatch.start();
int start = 1;
int end = 10000;
while (end <= 200000) {
// 1、 以下for循环，主要就是将读取到的数据，封装到这里
students = new ArrayList<>(10000);
// 2、 在这里控制每次读取多少条数据
readList = reader.read(start, end, true);
for (List<Object> objects : readList) {
students.add(Man.builder()
.name((String) objects.get(0))
.company((String) objects.get(1))
.sex((String) objects.get(2))
.lover((String) objects.get(3))
.address((String) objects.get(4))
.build());
}
int i = 0;
map = new HashMap<>(5);
while (true) {
List<Man> list = students.stream().skip(i * 2000).limit(2000).parallel().collect(Collectors.toList());
if (list.isEmpty()) {
break;
}
map.put(i + 1, list);
i++;
}
CountDownLatch latch = new CountDownLatch(5);
for (List<Man> students1 : map.values()) {
executor.submit(() -> {
mapper.add(students1);
latch.countDown();
});
}
latch.await();
//每10000条提交1次
sqlSession.commit();
students.clear();
start += 10000;
end += 10000;
}
//停止计时
stopWatch.stop();
System.out.println("总共耗时" + stopWatch.getTotalTimeSeconds());
} catch (Exception e) {
sqlSession.rollback();
} finally {
sqlSession.close();
// 关闭线程池
executor.shutdown();
}
return 1;
}
/**
* 定义一个批量添加数据的线程池，并将要添加的数据进行分批次添加。
*/
public String add5() {
ExcelReader reader = ExcelUtil.getReader(FileUtil.file("C:\\Users\\29283\\Desktop\\test.xlsx"), "sheet1");
long startTime1 = System.currentTimeMillis();
try {
CountDownLatch latch = new CountDownLatch(200);
int batch = 1000;
for (int i = 1;i <= 200000;i += batch){
final int start = i; // 将循环变量赋值给新变量 start
final int end = i + batch - 1; // 计算结束位置
executor.submit(() -> {
List<Man> read = reader.read(0, start, end, Man.class);
excelDao.add(read);
latch.countDown();
});
}
latch.await();
long startTime = System.currentTimeMillis();
System.out.println ("最终的结果为：" + (startTime - startTime1));
} catch (Exception e) {
e.printStackTrace();
} finally {
executor.shutdown();
}
return null;
}
ExecutorService executor = Executors.newFixedThreadPool(20);
/**
* 计算批量添加数据的数量，即batch变量，并计算总共需要添加数据的数量size。
* 根据计算的size去创建size个线程去添加数据
*/
@Timing
public String add6() {
ExcelReader reader = ExcelUtil.getReader(FileUtil.file("C:\\Users\\29283\\Desktop\\test.xlsx"), "sheet1");
int batch = 100000;
int size = 200000 % batch == 0 ? 200000 / batch : 200000 / batch +1;
System.out.println(200000 / batch);
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,true);
ExcelDao mapper = sqlSession.getMapper(ExcelDao.class);
try {
CountDownLatch latch = new CountDownLatch(size);
for (int i = 1;i <= size;i++){
final int start = 1 + (i - 1) * batch; // 将循环变量赋值给新变量 start
final int end = i * batch; // 计算结束位置
executor.submit(() -> {
List<Man> read = reader.read(0, start, end, Man.class);
mapper.add(read);
latch.countDown();
});
}
latch.await();
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
executor.shutdown();
}
return null;
}
/**
* 计算批量读取的数量和线程数量,创建一个线程池，用于并发执行批量读取操作
*/
@Timing
public String add7() {
ExcelReader reader = ExcelUtil.getReader(FileUtil.file("C:\\Users\\29283\\Desktop\\test.xlsx"), "sheet1");
int batch = 1000;
int size = 200000 % batch == 0 ? 200000 / batch : 200000 / batch +1;
List<List<Man>> lists = new CopyOnWriteArrayList<>();
try {
CountDownLatch latch = new CountDownLatch(size);
for (int i = 1;i <= size;i++){
final int start = 1 + (i - 1) * batch; // 将循环变量赋值给新变量 start
final int end = i * batch; // 计算结束位置
executor.submit(() -> {
List<Man> read = reader.read(0, start, end, Man.class);
lists.add(read);
latch.countDown();
});
}
latch.await();
write(lists);
} catch (Exception e) {
e.printStackTrace();
} finally {
executor.shutdown();
}
return null;
}
public void write(List<List<Man>> lists) throws InterruptedException {
long start = System.currentTimeMillis();
CountDownLatch latch = new CountDownLatch(lists.size());
for (List<Man> list : lists) {
executor.submit(() -> {
excelDao.add(list);
latch.countDown();
});
}
latch.await();
System.out.println(System.currentTimeMillis() - start);
}
}
在这个service类中，我们可以通过从Windows桌面上获取我们需要导入数据到mysql的excel文件，
"C:\\Users\\29283\\Desktop\\test.xlsx" 表示你存放excel文件的路径，"sheet1"表示excel的第一个工作薄。详细描述我在具体的方法中有进行概述，并且通过多种方式进行数据导入，请根据需要进行选择你想导入的方式。
测试类
@SpringBootTest
@MapperScan("com.miaow.dao")
class ExcelAppTests {
@Autowired
ExcelService excelService;
@Test
void add1() {
excelService.add();
}
@Test
void add2() {
excelService.add2();
}
@Test
void add3() {
excelService.add3();
}
@Test
void insert1() {
excelService.insert1();
}
@Test
void insert2() {
excelService.insert2();
}
@Test
void insert3() {
excelService.insert3();
}
@Test
void add4() {
excelService.add4();
}
@Test
void add5() {
excelService.add5();
}
@Test
void add6() {
excelService.add6();
}
@Test
void add7() {
excelService.add7();
}
}
启动类
@SpringBootApplication
public class ExcelApplication {
public static void main(String[] args) {
SpringApplication.run(ExcelApplication .class, args);
}
}
新建一个Excel文件，放到桌面
我的数据放在sheel1中，你根据需求进行，更改源代码也行。
测试日志 add1()
"C:\Program Files\Java\jdk1.8.0_261\bin\java.exe" -ea -Didea.test.cyclic.buffer.size=1048576 -Dhxl.spring.invoke.port=33336 -javaagent:C:\Users\29283\.config\.cool-request\request\lib\cool-request-agent.jar=test "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2022.1.1\lib\idea_rt.jar=50240:C:\Program Files\JetBrains\IntelliJ IDEA 2022.1.1\bin" -Dfile.encoding=UTF-8 -classpath "C:\Users\29283\.m2\repository\org\junit\platform\junit-platform-launcher\1.6.3\junit-platform-launcher-1.6.3.jar;C:\Users\29283\.m2\repository\org\apiguardian\apiguardian-api\1.1.0\apiguardian-api-1.1.0.jar;C:\Users\29283\.m2\repository\org\junit\platform\junit-platform-engine\1.6.3\junit-platform-engine-1.6.3.jar;C:\Users\29283\.m2\repository\org\opentest4j\opentest4j\1.2.0\opentest4j-1.2.0.jar;C:\Users\29283\.m2\repository\org\junit\platform\junit-platform-commons\1.6.3\junit-platform-commons-1.6.3.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2022.1.1\lib\idea_rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2022.1.1\plugins\junit\lib\junit5-rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2022.1.1\plugins\junit\lib\junit-rt.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_261\jre\lib\rt.jar;D:\myProgram\IDEAProgram\spring-excel\target\test-classes;D:\myProgram\IDEAProgram\spring-excel\target\classes;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot-starter-web\2.3.11.RELEASE\spring-boot-starter-web-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot-starter\2.3.11.RELEASE\spring-boot-starter-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot\2.3.11.RELEASE\spring-boot-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot-starter-logging\2.3.11.RELEASE\spring-boot-starter-logging-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\ch\qos\logback\logback-classic\1.2.3\logback-classic-1.2.3.jar;D:\IDEAMaven\maven_jar\ch\qos\logback\logback-core\1.2.3\logback-core-1.2.3.jar;D:\IDEAMaven\maven_jar\org\apache\logging\log4j\log4j-to-slf4j\2.13.3\log4j-to-slf4j-2.13.3.jar;D:\IDEAMaven\maven_jar\org\slf4j\jul-to-slf4j\1.7.30\jul-to-slf4j-1.7.30.jar;D:\IDEAMaven\maven_jar\jakarta\annotation\jakarta.annotation-api\1.3.5\jakarta.annotation-api-1.3.5.jar;D:\IDEAMaven\maven_jar\org\yaml\snakeyaml\1.26\snakeyaml-1.26.jar;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot-starter-json\2.3.11.RELEASE\spring-boot-starter-json-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\com\fasterxml\jackson\core\jackson-databind\2.11.4\jackson-databind-2.11.4.jar;D:\IDEAMaven\maven_jar\com\fasterxml\jackson\core\jackson-annotations\2.11.4\jackson-annotations-2.11.4.jar;D:\IDEAMaven\maven_jar\com\fasterxml\jackson\core\jackson-core\2.11.4\jackson-core-2.11.4.jar;D:\IDEAMaven\maven_jar\com\fasterxml\jackson\datatype\jackson-datatype-jdk8\2.11.4\jackson-datatype-jdk8-2.11.4.jar;D:\IDEAMaven\maven_jar\com\fasterxml\jackson\datatype\jackson-datatype-jsr310\2.11.4\jackson-datatype-jsr310-2.11.4.jar;D:\IDEAMaven\maven_jar\com\fasterxml\jackson\module\jackson-module-parameter-names\2.11.4\jackson-module-parameter-names-2.11.4.jar;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot-starter-tomcat\2.3.11.RELEASE\spring-boot-starter-tomcat-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\org\apache\tomcat\embed\tomcat-embed-core\9.0.46\tomcat-embed-core-9.0.46.jar;D:\IDEAMaven\maven_jar\org\glassfish\jakarta.el\3.0.3\jakarta.el-3.0.3.jar;D:\IDEAMaven\maven_jar\org\apache\tomcat\embed\tomcat-embed-websocket\9.0.46\tomcat-embed-websocket-9.0.46.jar;D:\IDEAMaven\maven_jar\org\springframework\spring-web\5.2.15.RELEASE\spring-web-5.2.15.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\spring-beans\5.2.15.RELEASE\spring-beans-5.2.15.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\spring-webmvc\5.2.15.RELEASE\spring-webmvc-5.2.15.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\spring-context\5.2.15.RELEASE\spring-context-5.2.15.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\spring-expression\5.2.15.RELEASE\spring-expression-5.2.15.RELEASE.jar;D:\IDEAMaven\maven_jar\org\projectlombok\lombok\1.18.20\lombok-1.18.20.jar;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot-starter-test\2.3.11.RELEASE\spring-boot-starter-test-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot-test\2.3.11.RELEASE\spring-boot-test-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot-test-autoconfigure\2.3.11.RELEASE\spring-boot-test-autoconfigure-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\com\jayway\jsonpath\json-path\2.4.0\json-path-2.4.0.jar;D:\IDEAMaven\maven_jar\net\minidev\json-smart\2.3.1\json-smart-2.3.1.jar;D:\IDEAMaven\maven_jar\net\minidev\accessors-smart\2.3.1\accessors-smart-2.3.1.jar;D:\IDEAMaven\maven_jar\org\ow2\asm\asm\5.0.4\asm-5.0.4.jar;D:\IDEAMaven\maven_jar\jakarta\xml\bind\jakarta.xml.bind-api\2.3.3\jakarta.xml.bind-api-2.3.3.jar;D:\IDEAMaven\maven_jar\jakarta\activation\jakarta.activation-api\1.2.2\jakarta.activation-api-1.2.2.jar;D:\IDEAMaven\maven_jar\org\assertj\assertj-core\3.16.1\assertj-core-3.16.1.jar;D:\IDEAMaven\maven_jar\org\hamcrest\hamcrest\2.2\hamcrest-2.2.jar;D:\IDEAMaven\maven_jar\org\junit\jupiter\junit-jupiter\5.6.3\junit-jupiter-5.6.3.jar;D:\IDEAMaven\maven_jar\org\junit\jupiter\junit-jupiter-api\5.6.3\junit-jupiter-api-5.6.3.jar;D:\IDEAMaven\maven_jar\org\opentest4j\opentest4j\1.2.0\opentest4j-1.2.0.jar;D:\IDEAMaven\maven_jar\org\junit\platform\junit-platform-commons\1.6.3\junit-platform-commons-1.6.3.jar;D:\IDEAMaven\maven_jar\org\junit\jupiter\junit-jupiter-params\5.6.3\junit-jupiter-params-5.6.3.jar;D:\IDEAMaven\maven_jar\org\junit\jupiter\junit-jupiter-engine\5.6.3\junit-jupiter-engine-5.6.3.jar;D:\IDEAMaven\maven_jar\org\junit\vintage\junit-vintage-engine\5.6.3\junit-vintage-engine-5.6.3.jar;D:\IDEAMaven\maven_jar\org\apiguardian\apiguardian-api\1.1.0\apiguardian-api-1.1.0.jar;D:\IDEAMaven\maven_jar\org\junit\platform\junit-platform-engine\1.6.3\junit-platform-engine-1.6.3.jar;D:\IDEAMaven\maven_jar\junit\junit\4.13.2\junit-4.13.2.jar;D:\IDEAMaven\maven_jar\org\mockito\mockito-core\3.3.3\mockito-core-3.3.3.jar;D:\IDEAMaven\maven_jar\net\bytebuddy\byte-buddy\1.10.22\byte-buddy-1.10.22.jar;D:\IDEAMaven\maven_jar\net\bytebuddy\byte-buddy-agent\1.10.22\byte-buddy-agent-1.10.22.jar;D:\IDEAMaven\maven_jar\org\objenesis\objenesis\2.6\objenesis-2.6.jar;D:\IDEAMaven\maven_jar\org\mockito\mockito-junit-jupiter\3.3.3\mockito-junit-jupiter-3.3.3.jar;D:\IDEAMaven\maven_jar\org\skyscreamer\jsonassert\1.5.0\jsonassert-1.5.0.jar;D:\IDEAMaven\maven_jar\com\vaadin\external\google\android-json\0.0.20131108.vaadin1\android-json-0.0.20131108.vaadin1.jar;D:\IDEAMaven\maven_jar\org\springframework\spring-core\5.2.15.RELEASE\spring-core-5.2.15.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\spring-jcl\5.2.15.RELEASE\spring-jcl-5.2.15.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\spring-test\5.2.15.RELEASE\spring-test-5.2.15.RELEASE.jar;D:\IDEAMaven\maven_jar\org\xmlunit\xmlunit-core\2.7.0\xmlunit-core-2.7.0.jar;D:\IDEAMaven\maven_jar\org\apache\poi\poi-ooxml\5.2.3\poi-ooxml-5.2.3.jar;D:\IDEAMaven\maven_jar\org\apache\poi\poi\5.2.3\poi-5.2.3.jar;D:\IDEAMaven\maven_jar\commons-codec\commons-codec\1.14\commons-codec-1.14.jar;D:\IDEAMaven\maven_jar\org\apache\commons\commons-math3\3.6.1\commons-math3-3.6.1.jar;D:\IDEAMaven\maven_jar\com\zaxxer\SparseBitSet\1.2\SparseBitSet-1.2.jar;D:\IDEAMaven\maven_jar\org\apache\poi\poi-ooxml-lite\5.2.3\poi-ooxml-lite-5.2.3.jar;D:\IDEAMaven\maven_jar\org\apache\xmlbeans\xmlbeans\5.1.1\xmlbeans-5.1.1.jar;D:\IDEAMaven\maven_jar\xml-apis\xml-apis\1.4.01\xml-apis-1.4.01.jar;D:\IDEAMaven\maven_jar\org\apache\commons\commons-compress\1.21\commons-compress-1.21.jar;D:\IDEAMaven\maven_jar\commons-io\commons-io\2.11.0\commons-io-2.11.0.jar;D:\IDEAMaven\maven_jar\com\github\virtuald\curvesapi\1.07\curvesapi-1.07.jar;D:\IDEAMaven\maven_jar\org\apache\logging\log4j\log4j-api\2.13.3\log4j-api-2.13.3.jar;D:\IDEAMaven\maven_jar\org\apache\commons\commons-collections4\4.4\commons-collections4-4.4.jar;D:\IDEAMaven\maven_jar\org\mybatis\spring\boot\mybatis-spring-boot-starter\2.3.0\mybatis-spring-boot-starter-2.3.0.jar;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot-starter-jdbc\2.3.11.RELEASE\spring-boot-starter-jdbc-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\com\zaxxer\HikariCP\3.4.5\HikariCP-3.4.5.jar;D:\IDEAMaven\maven_jar\org\springframework\spring-jdbc\5.2.15.RELEASE\spring-jdbc-5.2.15.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\spring-tx\5.2.15.RELEASE\spring-tx-5.2.15.RELEASE.jar;D:\IDEAMaven\maven_jar\org\mybatis\spring\boot\mybatis-spring-boot-autoconfigure\2.3.0\mybatis-spring-boot-autoconfigure-2.3.0.jar;D:\IDEAMaven\maven_jar\org\mybatis\mybatis\3.5.11\mybatis-3.5.11.jar;D:\IDEAMaven\maven_jar\org\mybatis\mybatis-spring\2.1.0\mybatis-spring-2.1.0.jar;D:\IDEAMaven\maven_jar\cn\hutool\hutool-all\5.8.11\hutool-all-5.8.11.jar;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot-starter-aop\2.3.11.RELEASE\spring-boot-starter-aop-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\org\springframework\spring-aop\5.2.15.RELEASE\spring-aop-5.2.15.RELEASE.jar;D:\IDEAMaven\maven_jar\org\aspectj\aspectjweaver\1.9.6\aspectjweaver-1.9.6.jar;D:\IDEAMaven\maven_jar\com\alibaba\druid-spring-boot-starter\1.2.9\druid-spring-boot-starter-1.2.9.jar;D:\IDEAMaven\maven_jar\com\alibaba\druid\1.2.9\druid-1.2.9.jar;D:\IDEAMaven\maven_jar\org\slf4j\slf4j-api\1.7.30\slf4j-api-1.7.30.jar;D:\IDEAMaven\maven_jar\org\springframework\boot\spring-boot-autoconfigure\2.3.11.RELEASE\spring-boot-autoconfigure-2.3.11.RELEASE.jar;D:\IDEAMaven\maven_jar\mysql\mysql-connector-java\8.0.25\mysql-connector-java-8.0.25.jar;C:\Users\29283\.config\.cool-request\request\lib\spring-invoke-starter.jar" com.intellij.rt.junit.JUnitStarter -ideVersion5 -junit5 com.miaow.springexcel.ExcelAppTests,add1
15:01:00.006 [main] DEBUG org.springframework.test.context.BootstrapUtils - Instantiating CacheAwareContextLoaderDelegate from class [org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate]
15:01:00.015 [main] DEBUG org.springframework.test.context.BootstrapUtils - Instantiating BootstrapContext using constructor [public org.springframework.test.context.support.DefaultBootstrapContext(java.lang.Class,org.springframework.test.context.CacheAwareContextLoaderDelegate)]
15:01:00.038 [main] DEBUG org.springframework.test.context.BootstrapUtils - Instantiating TestContextBootstrapper for test class [com.miaow.springexcel.ExcelAppTests] from class [org.springframework.boot.test.context.SpringBootTestContextBootstrapper]
15:01:00.048 [main] INFO org.springframework.boot.test.context.SpringBootTestContextBootstrapper - Neither @ContextConfiguration nor @ContextHierarchy found for test class [com.miaow.springexcel.ExcelAppTests], using SpringBootContextLoader
15:01:00.051 [main] DEBUG org.springframework.test.context.support.AbstractContextLoader - Did not detect default resource location for test class [com.miaow.springexcel.ExcelAppTests]: class path resource [com/miaow/springexcel/ExcelAppTests-context.xml] does not exist
15:01:00.051 [main] DEBUG org.springframework.test.context.support.AbstractContextLoader - Did not detect default resource location for test class [com.miaow.springexcel.ExcelAppTests]: class path resource [com/miaow/springexcel/ExcelAppTestsContext.groovy] does not exist
15:01:00.051 [main] INFO org.springframework.test.context.support.AbstractContextLoader - Could not detect default resource locations for test class [com.miaow.springexcel.ExcelAppTests]: no resource found for suffixes {-context.xml, Context.groovy}.
15:01:00.052 [main] INFO org.springframework.test.context.support.AnnotationConfigContextLoaderUtils - Could not detect default configuration classes for test class [com.miaow.springexcel.ExcelAppTests]: ExcelAppTests does not declare any static, non-private, non-final, nested classes annotated with @Configuration.
15:01:00.088 [main] DEBUG org.springframework.test.context.support.ActiveProfilesUtils - Could not find an 'annotation declaring class' for annotation type [org.springframework.test.context.ActiveProfiles] and class [com.miaow.springexcel.ExcelAppTests]
15:01:00.131 [main] DEBUG org.springframework.context.annotation.ClassPathScanningCandidateComponentProvider - Identified candidate component class: file [D:\myProgram\IDEAProgram\spring-excel\target\classes\com\miaow\ExcelApplication.class]
15:01:00.132 [main] INFO org.springframework.boot.test.context.SpringBootTestContextBootstrapper - Found @SpringBootConfiguration com.miaow.ExcelApplication for test class com.miaow.springexcel.ExcelAppTests
15:01:00.195 [main] DEBUG org.springframework.boot.test.context.SpringBootTestContextBootstrapper - @TestExecutionListeners is not present for class [com.miaow.springexcel.ExcelAppTests]: using defaults.
15:01:00.196 [main] INFO org.springframework.boot.test.context.SpringBootTestContextBootstrapper - Loaded default TestExecutionListener class names from location [META-INF/spring.factories]: [org.springframework.boot.test.mock.mockito.MockitoTestExecutionListener, org.springframework.boot.test.mock.mockito.ResetMocksTestExecutionListener, org.springframework.boot.test.autoconfigure.restdocs.RestDocsTestExecutionListener, org.springframework.boot.test.autoconfigure.web.client.MockRestServiceServerResetTestExecutionListener, org.springframework.boot.test.autoconfigure.web.servlet.MockMvcPrintOnlyOnFailureTestExecutionListener, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverTestExecutionListener, org.springframework.boot.test.autoconfigure.webservices.client.MockWebServiceServerTestExecutionListener, org.springframework.test.context.web.ServletTestExecutionListener, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener, org.springframework.test.context.support.DependencyInjectionTestExecutionListener, org.springframework.test.context.support.DirtiesContextTestExecutionListener, org.springframework.test.context.transaction.TransactionalTestExecutionListener, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener, org.springframework.test.context.event.EventPublishingTestExecutionListener]
15:01:00.206 [main] INFO org.springframework.boot.test.context.SpringBootTestContextBootstrapper - Using TestExecutionListeners: [org.springframework.test.context.web.ServletTestExecutionListener@75437611, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener@350aac89, org.springframework.boot.test.mock.mockito.MockitoTestExecutionListener@1c5920df, org.springframework.boot.test.autoconfigure.SpringBootDependencyInjectionTestExecutionListener@17f9d882, org.springframework.test.context.support.DirtiesContextTestExecutionListener@79e4c792, org.springframework.test.context.transaction.TransactionalTestExecutionListener@196a42c3, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener@4c60d6e9, org.springframework.test.context.event.EventPublishingTestExecutionListener@15043a2f, org.springframework.boot.test.mock.mockito.ResetMocksTestExecutionListener@4a83a74a, org.springframework.boot.test.autoconfigure.restdocs.RestDocsTestExecutionListener@1349883, org.springframework.boot.test.autoconfigure.web.client.MockRestServiceServerResetTestExecutionListener@4b29d1d2, org.springframework.boot.test.autoconfigure.web.servlet.MockMvcPrintOnlyOnFailureTestExecutionListener@7f485fda, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverTestExecutionListener@28261e8e, org.springframework.boot.test.autoconfigure.webservices.client.MockWebServiceServerTestExecutionListener@d737b89]
15:01:00.209 [main] DEBUG org.springframework.test.context.support.AbstractDirtiesContextTestExecutionListener - Before test class: context [DefaultTestContext@2cb4893b testClass = ExcelAppTests, testInstance = [null], testMethod = [null], testException = [null], mergedContextConfiguration = [WebMergedContextConfiguration@cc43f62 testClass = ExcelAppTests, locations = '{}', classes = '{class com.miaow.ExcelApplication}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}', contextCustomizers = set[[ImportsContextCustomizer@5b218417 key = [@org.springframework.boot.test.context.SpringBootTest(args=[], webEnvironment=MOCK, value=[], properties=[], classes=[]), @org.springframework.context.annotation.Import(value=[class org.mybatis.spring.annotation.MapperScannerRegistrar]), @org.apiguardian.api.API(consumers=[*], since=5.0, status=STABLE), @org.springframework.test.context.BootstrapWith(value=class org.springframework.boot.test.context.SpringBootTestContextBootstrapper), @org.junit.jupiter.api.extension.ExtendWith(value=[class org.springframework.test.context.junit.jupiter.SpringExtension]), @org.mybatis.spring.annotation.MapperScan(sqlSessionFactoryRef=, sqlSessionTemplateRef=, lazyInitialization=, annotationClass=interface java.lang.annotation.Annotation, basePackageClasses=[], nameGenerator=interface org.springframework.beans.factory.support.BeanNameGenerator, markerInterface=class java.lang.Class, basePackages=[], value=[com.miaow.dao], factoryBean=class org.mybatis.spring.mapper.MapperFactoryBean, defaultScope=)]], org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@4f6ee6e4, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@6b927fb, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer@5158b42f, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@c8c12ac, org.springframework.boot.test.context.SpringBootTestArgs@1, org.springframework.boot.test.context.SpringBootTestWebEnvironment@52d455b8], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map['org.springframework.test.context.web.ServletTestExecutionListener.activateListener' -> true]], class annotated with @DirtiesContext [false] with mode [null].
15:01:00.226 [main] DEBUG org.springframework.test.context.support.TestPropertySourceUtils - Adding inlined properties to environment: {spring.jmx.enabled=false, org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.3.11.RELEASE)
2024-04-12 15:01:00.404 INFO 1180 --- [ main] com.miaow.springexcel.ExcelAppTests : Starting ExcelAppTests on danfoo with PID 1180 (started by 29283 in D:\myProgram\IDEAProgram\spring-excel)
2024-04-12 15:01:00.405 DEBUG 1180 --- [ main] com.miaow.springexcel.ExcelAppTests : Running with Spring Boot v2.3.11.RELEASE, Spring v5.2.15.RELEASE
2024-04-12 15:01:00.405 INFO 1180 --- [ main] com.miaow.springexcel.ExcelAppTests : No active profile set, falling back to default profiles: default
2024-04-12 15:01:02.615 INFO 1180 --- [ main] com.miaow.springexcel.ExcelAppTests : Started ExcelAppTests in 2.382 seconds (JVM running for 3.035)
2024-04-12 15:01:03.545 DEBUG 1180 --- [ main] com.miaow.dao.ExcelDao.add : ==> Preparing: insert into exportexcel(name,company,sex,lover,address) values (?,?,?,?,?) , (?,?,?,?,?) , (?,?,?,?,?) , (?,?,?,?,?) , (?,?,?,?,?)
2024-04-12 15:01:03.567 DEBUG 1180 --- [ main] com.miaow.dao.ExcelDao.add : ==> Parameters: 王喜凤(String), 人字拖(String), 男(String), 风有杨(String), 深圳福田(String), 张大三(String), 人字拖(String), 男(String), 谷雨(String), 深圳福田(String), 理由四(String), 人字拖(String), 男(String), 听风(String), 深圳福田(String), 和瑞园(String), 人字拖(String), 男(String), 噬渊(String), 深圳福田(String), 巴膳坊(String), 人字拖(String), 男(String), 诚昌(String), 深圳福田(String)
最终的结果为：645
最终的结果为：93
Process finished with exit code 0
SQL表
DROP TABLE IF EXISTS `exportexcel`;
CREATE TABLE `exportexcel` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`company` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`lover` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;