SSM结合easyexcel导入数据库

1、创建项目

代码结构

2、添加依赖

 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</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.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>

3、application.properties配置

# 端口号
server.port=1313
# 数据库配置
spring.datasource.url=jdbc:mysql:///数据库名?serverTimezone=GMT%2B8&characterEncoding=utf8
# 数据库账号密码
spring.datasource.username=账号
spring.datasource.password=密码
# mybatis配置
mybatis.mapper-locations=classpath:/mapper/*.xml
logging.level.com.zj=debug

4、编写pojo层

@Data
public class User implements Serializable {

    private static final long serialVersionUID = 619270056917451517L;

//  @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")这里用string 去接日期才能格式化。我想接收年月日格式
//  @ColumnWidth(20)设置宽度
    @ExcelProperty(value = "学号",index = 0)
    private Integer id;

    @ExcelProperty(value = "姓名",index = 1)
    private String name;

}

5、编写mapper层

@Mapper
public interface UserMapper {

    int insert(User user);

}

6、编写UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.zj.mapper.UserMapper">
    <insert id="insert">
        insert into user01(id,name)values (#{id},#{name});
    </insert>
</mapper>

7、编写UserService

public interface UserService {

    int insert(User user);

}

8、编写实现类

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public int insert(User user) {
        return userMapper.insert(user);
    }
}

9、重写监听器（不能被spring管理）

public class UserListener extends AnalysisEventListener<User> {

    private UserService userService;

    public UserListener(UserService userService){
        this.userService=userService;
    }

//  一行行读取excel内容
    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        System.out.println("开始执行");
        saveData(user);
    }

    private void saveData(User user) {
        userService.insert(user);
        System.out.println("存储成功");
    }
//  读取表头内容
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头："+headMap);
    }

//  读取完成之后
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("存储完毕");
    }
}

10、controller层编写

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @PostMapping("upload")
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), User.class,new UserListener(userService)).sheet().doRead();
//        sheet()默认是0
        return "success";
    }

}

postman测试

IDEA后台

数据库

成功上传数据