• 在Spring Boot中使用POI完成一个excel报表导入数据到MySQL的功能

    最近看了自己玩过的很多项目，忽然发现有一个在实际开发中我们经常用到的功能，但是我没有正儿八经的玩过这个功能，那就是在Spring Boot中实现一个excel报表的导入导出功能，这篇博客，主要是围绕excel报表数据导入进行，感兴趣的可以看看，有能力的可以在本篇博客的基础上进行改造，实现一个前端报表导入导出功能。

    要在Spring Boot中使用Apache POI导入Excel数据到MySQL，我们需要一步一步来，接下来这些是我们的主要步骤。

    1. 引入依赖：我们需要确保pom.xml中包含了Apache POI和相关依赖。
    2. 配置数据源：在Spring Boot的配置文件application.properties或者application.yml中配置MySQL数据源。
    3. 创建相关实体类：创建一个实体类来映射数据库中对应的表。
    4. 创建相关接口：用来实现相关的业务功能。
    5. 创建相关接口实现类
    6. 新建一个excel表格，填充数据
    7. 创建对应数据库和数据库表：实际上这一步是放在第二位的，先有表在有实体类
    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.0modelVersion>
    <parent>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starter-parentartifactId>
        <version>2.3.11.RELEASEversion>
        <relativePath/> 
    parent>
    
    <groupId>com.miaowgroupId>
    <artifactId>spring-excelartifactId>
    <version>0.0.1-SNAPSHOTversion>
    <name>spring-excelname>
    <description>spring-exceldescription>
    <properties>
        <java.version>8java.version>
    properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>
        <dependency>
            <groupId>org.projectlombokgroupId>
            <artifactId>lombokartifactId>
            <optional>trueoptional>
        dependency>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-testartifactId>
            <scope>testscope>
        dependency>
        
        <dependency>
            <groupId>org.apache.poigroupId>
            <artifactId>poi-ooxmlartifactId>
            <version>5.2.3version>
        dependency>
        
        <dependency>
            <groupId>org.mybatis.spring.bootgroupId>
            <artifactId>mybatis-spring-boot-starterartifactId>
            <version>2.3.0version>
        dependency>
        <dependency>
            <groupId>cn.hutoolgroupId>
            <artifactId>hutool-allartifactId>
            <version>5.8.11version>
        dependency>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-aopartifactId>
        dependency>
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>druid-spring-boot-starterartifactId>
            <version>1.2.9version>
        dependency>
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
        dependency>
    dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.bootgroupId>
                <artifactId>spring-boot-maven-pluginartifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombokgroupId>
                            <artifactId>lombokartifactId>
                        exclude>
                    excludes>
                configuration>
            plugin>
        plugins>
    build>

project>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84

    配置数据源

    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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    创建实体类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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    创建相关接口

    @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);

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    对应Mapper文件的映射文件ExcelMapper.xml

    
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>


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    定义时间注解

    /**  
 * Java注解，用于标记一个方法，该方法用于测量代码的执行时间。
 *
 * @Target(ElementType.METHOD)：该注释适用于方法。
 *
 * @Retention(RetentionPolicy.RUNTIME)：该注释将被保留在运行时。
 *
 * @Timing：这是注释的名称，它表示该方法用于测量代码的执行时间。 
 */

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Timing {
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    打印执行时间日志的类

    /**
 * 用于在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;
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    业务实现类

    @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集合。
     *  2. 读取Excel文件的结束时间同时也是将数据插入数据库的开始时间
     *  3. 通过SqlSessionFactory获取SqlSession，并使用getMapper方法获取ExcelDao的代理对象。
     *  4. 遍历List集合中的数据，并将其插入到数据库中。
     *  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集合
        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中的数据插入数据库
        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);
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205
    • 206
    • 207
    • 208
    • 209
    • 210
    • 211
    • 212
    • 213
    • 214
    • 215
    • 216
    • 217
    • 218
    • 219
    • 220
    • 221
    • 222
    • 223
    • 224
    • 225
    • 226
    • 227
    • 228
    • 229
    • 230
    • 231
    • 232
    • 233
    • 234
    • 235
    • 236
    • 237
    • 238
    • 239
    • 240
    • 241
    • 242
    • 243
    • 244
    • 245
    • 246
    • 247
    • 248
    • 249
    • 250
    • 251
    • 252
    • 253
    • 254
    • 255
    • 256
    • 257
    • 258
    • 259
    • 260
    • 261
    • 262
    • 263
    • 264
    • 265
    • 266
    • 267
    • 268
    • 269
    • 270
    • 271
    • 272
    • 273
    • 274
    • 275
    • 276
    • 277
    • 278
    • 279
    • 280
    • 281
    • 282
    • 283
    • 284
    • 285
    • 286
    • 287
    • 288
    • 289
    • 290
    • 291
    • 292
    • 293
    • 294
    • 295
    • 296
    • 297
    • 298
    • 299
    • 300
    • 301
    • 302
    • 303
    • 304
    • 305
    • 306
    • 307
    • 308
    • 309
    • 310
    • 311
    • 312
    • 313
    • 314
    • 315
    • 316
    • 317
    • 318
    • 319
    • 320
    • 321
    • 322
    • 323
    • 324
    • 325
    • 326
    • 327
    • 328
    • 329
    • 330
    • 331
    • 332
    • 333
    • 334
    • 335
    • 336
    • 337
    • 338
    • 339
    • 340
    • 341
    • 342
    • 343
    • 344
    • 345
    • 346
    • 347
    • 348
    • 349
    • 350
    • 351
    • 352
    • 353
    • 354
    • 355
    • 356
    • 357
    • 358
    • 359
    • 360
    • 361
    • 362
    • 363
    • 364
    • 365
    • 366
    • 367
    • 368
    • 369
    • 370
    • 371
    • 372
    • 373
    • 374
    • 375

    在这个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();
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52

    启动类

    @SpringBootApplication
public class ExcelApplication {
    public static void main(String[] args) {
        SpringApplication.run(ExcelApplication .class, args);
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    新建一个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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    在这里插入图片描述

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
  • 相关阅读:
    普通平衡树（Treap）
    TechSmith Camtasia2023屏幕录像和编辑软件更新介绍
    元宇宙电商-NFG系统，是如何让数字藏品流通的？
    qtchooser 配置和使用多个Qt版本
    可见光相机曝光方式
    【已解决】ModuleNotFoundError: No module named ‘dgl‘
    Element-ui
    第2-3-8章 分片上传和分片合并的接口开发-文件存储服务系统-nginx/fastDFS/minio/阿里云oss/七牛云oss
    Harbor安全：cfssl工具为Harbor颁发https证书
    Centos7 Shell编程之函数、消息的发送与接收
  • 原文地址：https://blog.csdn.net/qq_45922256/article/details/137674775