
<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.1</version>
        </dependency>
 
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.0.31</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>4.0.1</version>
        </dependency>
    </dependencies>

3.3 创建数据库和数据表

创建一个名为excel_demo_db数据库，以及user数据表。


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
CREATE DATABASE `excel_demo_db`;
USE `excel_demo_db`;
 
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
SET FOREIGN_KEY_CHECKS = 1;

成功创建数据库和表后的数据结构如下：

3.4 编写application.properties文件


# 配置数据库
spring.datasource.url=jdbc:mysql://localhost:3306/excel_demo_db?useSSL=false&serverTimezone=UTC
# 用户名
spring.datasource.username=你的数据库
# 密码
spring.datasource.password=你的密码
# 页面文件位置
spring.thymeleaf.prefix=classpath:/templates/
spring.thymeleaf.suffix=.html

四、开始编写

4.1 目录结构

4.2 MyBatis配置类

创建config包，在config 包下创建名为MyBatisConfig类，代码如下：


package com.saycode.demo.config;
 
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
 
import javax.sql.DataSource;
 
@Configuration
@MapperScan("com.saycode.demo.mapper") // 扫描Mapper接口所在的包
public class MyBatisConfig {
 
    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
 
        Resource[] resources = new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/*.xml");
        sessionFactory.setMapperLocations(resources);
 
        return sessionFactory.getObject();
    }
}

4.3 用户实体类

在entity包下创建User类，代码如下：

@ExcelProperty 便是EasyExcel 提供的注解，用于将 Java 对象的字段与 Excel 文件的列进行映射。


package com.saycode.demo.entity;
 
import com.alibaba.excel.annotation.ExcelProperty;
 
public class User {
 
    private Long id;
 
    @ExcelProperty("姓名")
    private String name;
 
    @ExcelProperty("年龄")
    private Integer age;
 
    public Long getId() {
        return id;
    }
 
    public void setId(Long id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public Integer getAge() {
        return age;
    }
 
    public void setAge(Integer age) {
        this.age = age;
    }
}

4.4 mapper接口

在mapper 包下创建UserMapper 接口，代码如下：


package com.saycode.demo.mapper;
 
import com.saycode.demo.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
 
import java.util.List;
 
 
public interface UserMapper {
    void insertUser(User user);
 
    List<User> getAllUsers();
}

4.5 编写数据操作文件

在resources 下创建mapper包，在mapper包中创建名为userMapper.xml的文件，用于编写sql语句。


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.saycode.demo.mapper.UserMapper">
    <resultMap id="userResultMap" type="com.saycode.demo.entity.User">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
    </resultMap>
 
    <select id="getAllUsers" resultMap="userResultMap">
        SELECT * FROM users
    </select>
 
    <insert id="insertUser">
        INSERT INTO users (name, age) VALUES (#{name}, #{age})
    </insert>
</mapper>

resources 目录如下：

4.6 编写Service 业务层

在service包下创建 UserService类，代码如下：


package com.saycode.demo.service;
 
import com.saycode.demo.entity.User;
import com.saycode.demo.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import java.util.List;
 
@Service
public class UserService {
 
    @Autowired
    private UserMapper userMapper;
 
    public void insertUser(User user) {
        userMapper.insertUser(user);
    }
 
    public List<User> getAllUsers() {
        return userMapper.getAllUsers();
    }
}

4.7 控制器层

在controller 下创建一个名为UserExcelListener的类，这是一个自定义的监听器类，用于处理 Excel 文件读取时的事件和逻辑。

以及创建一个UserController 类，处理用户的请求。代码如下：

UserExcelListener


package com.saycode.demo.controller;
 
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.saycode.demo.entity.User;
import com.saycode.demo.service.UserService;
 
public class UserExcelListener extends AnalysisEventListener<User> {
 
    private UserService userService;
 
    public UserExcelListener(UserService userService) {
        this.userService = userService;
    }
 
    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        userService.insertUser(user);
    }
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

UserController


package com.saycode.demo.controller;
 
import com.alibaba.excel.EasyExcel;
import com.saycode.demo.entity.User;
import com.saycode.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.multipart.MultipartFile;
 
import java.io.IOException;
import java.util.List;
 
@Controller
public class UserController {
 
    @Autowired
    private UserService userService;
 
    @GetMapping("/")
    public String index(Model model) {
        List<User> users = userService.getAllUsers();
        model.addAttribute("users", users);
        return "index";
    }
 
    @PostMapping("/upload")
    public String upload(MultipartFile file) {
        try {
            EasyExcel.read(file.getInputStream(), User.class, new UserExcelListener(userService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "redirect:/";
    }
}

4.8 编写主页面

在resources 下的 templates包中创建index.html文件，代码如下：


<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <title>用户列表信息</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
        }
 
        h1 {
            color: #333;
            text-align: center;
        }
 
        form {
            margin-bottom: 20px;
        }
 
        table {
            width: 80%;
            margin: 0 auto;
            border-collapse: collapse;
            border: 1px solid #ccc;
        }
 
        th, td {
            padding: 10px;
            text-align: left;
        }
 
        th {
            background-color: #f2f2f2;
        }
 
        tr:nth-child(even) {
            background-color: #f9f9f9;
        }
 
        input[type="file"] {
            margin-right: 10px;
        }
 
        button[type="submit"] {
            padding: 8px 16px;
            background-color: #4CAF50;
            color: white;
            border: none;
            cursor: pointer;
        }
 
        button[type="submit"]:hover {
            background-color: #45a049;
        }
    </style>
</head>
<body>
<h1>用户列表</h1>
<form action="#" th:action="@{/upload}" method="post" enctype="multipart/form-data">
    <input type="file" name="file" />
    <button type="submit">上传</button>
</form>
<table border="1">
    <tr>
        <th>姓名</th>
        <th>年龄</th>
    </tr>
    <tr th:each="user : ${users}">
        <td th:text="${user.name}"></td>
        <td th:text="${user.age}"></td>
    </tr>
</table>
</body>
</html>
</title>
</head>
<body>
 
</body>
</html>

五、运行

在对应主程序类中，运行程序。

在浏览器中输入: localhost:8080 进行访问

点击选择文件，选择excel文件。

文件格式如下:

序号	姓名	年龄
1	张三	20
2	王五	23

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/喵喵爱编程/article/detail/835431?site