赞
踩
最近看了自己玩过的很多项目,忽然发现有一个在实际开发中我们经常用到的功能,但是我没有正儿八经的玩过这个功能,那就是在Spring Boot中实现一个excel报表的导入导出功能,这篇博客,主要是围绕excel报表数据导入进行,感兴趣的可以看看,有能力的可以在本篇博客的基础上进行改造,实现一个前端报表导入导出功能。
要在Spring Boot中使用Apache POI导入Excel数据到MySQL,我们需要一步一步来,接下来这些是我们的主要步骤。
文件目录如下:
<?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
@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);
}
}
我的数据放在sheel1中,你根据需求进行,更改源代码也行。
"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
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;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。