关于实现将excel表中的数据以文件上传的方式添加到数据库中

导入easyexcel依赖即可完成

实现将数据保存到excel表格

 实现将excel表格中的内容读取

1.实现一个监听器类，重写方法

 

但是上面只是将excel表格中的数据拿到，并未添加数据库操作，接下来实现更吊的，通过页面将数据加入到数据库中：

首先，新建一个SpringBoot项目，导入下面的依赖

 <dependencies>
        <!--mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
            
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        
        <!--easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>
        
        <!--mail-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-mail</artifactId>
        </dependency>
        
        <!--thymeleaf-->
        <dependency>
            <groupId>org.thymeleaf</groupId>
            <artifactId>thymeleaf-spring5</artifactId>
        </dependency>
        <dependency>
            <groupId>org.thymeleaf.extras</groupId>
            <artifactId>thymeleaf-extras-java8time</artifactId>
        </dependency>
        
        <!--SpringMVC-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

1.其中

@ExcelProperty(value="用户编号",index = 0) index=0表示在excel表中的表头第一个

@Data
@TableName("user")
public class User {
    @TableField("uid")
    @ExcelProperty(value="用户编号",index = 0)
    private int uid;
    @TableField("username")
    @ExcelProperty(value="用户名称",index = 1)
    private String username;
}

 

UserServiceImpl

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper,User>
        implements UserService {
    @Autowired
    private UserMapper userMapper;
    @Override
    public void importUserData(MultipartFile file) {
        try {
            EasyExcel.read(file.getInputStream(), User.class,new ExcellListener(baseMapper)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

 userMapper

@Repository
public interface UserMapper extends BaseMapper<User>{

}

   @Controller
public class SendController {
    @Autowired
    UserService userService;
 @PostMapping({"/import"})
    public String importUser(@RequestParam("importFile") MultipartFile file){
        userService.importUserData(file);
        return "success";
    }
}

启动类

package com.kuang;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;
@EnableAsync
@EnableScheduling //开启定时功能注解
@SpringBootApplication
@MapperScan("com.kuang.mapper")
public class EmailApplication {

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

}

监听器

package com.kuang;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import com.kuang.mapper.UserMapper;
import org.springframework.beans.BeanUtils;

import java.util.Map;

public class ExcellListener extends AnalysisEventListener<User> {
    //作用就是一行一行读取excel内容，从第二行读取

    private UserMapper userMapper;

    public ExcellListener(UserMapper userMapper) {
        this.userMapper=userMapper;
    }

    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        //调用方法添加数据库
        /*User user=new User();*/
      /*  BeanUtils.copyProperties(userDate,user);*/
        userMapper.insert(user);
      /*userDateMapper.insert(userDate);*/
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头信息"+headMap);
    }

    //读取之后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }


}

结果

 

要注意文件提交的类型啊，一开始写传进来是空