当前位置：首页 > 文章列表 > 数据库 > MySQL > SSM结合easyexcel导入数据库

SSM结合easyexcel导入数据库

来源：SegmentFault 2023-02-16 15:16:22 0浏览收藏

积累知识，胜过积蓄金银！毕竟在##column_title##开发的过程中，会遇到各种各样的问题，往往都是一些细节知识点还没有掌握好而导致的，因此基础知识点的积累是很重要的。下面本文《SSM结合easyexcel导入数据库》，就带大家讲解一下MySQL、Java、springboot、mybatis、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" ?><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("存储完毕");
    }
}
</integer></user>

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";
    }

}