Springbooot实现Excel数据导入数据库

敲不动滴神

已于 2023-09-27 17:36:05 修改

阅读量680

点赞数

于 2023-09-27 17:35:13 首次发布

本文链接：https://blog.csdn.net/qq_55622403/article/details/133358637

版权

一、pom.xml 加入依赖

<!--        poi  hutool-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.20</version>
        </dependency>

二、userMapper，批量添加

由于id和creatTime自动添加数据所以省略

<!--    Integer addUsers(List<User> users);-->
        <insert id="addUsers">

        insert into sys_user(username, password, nickname, email, phone, address, avater)

            VALUES

            <foreach collection="list" item="user" separator="," >

                (#{user.username},#{user.password},#{user.nickname},#{user.email},#{user.phone},#{user.address},#{user.avater})

            </foreach>

        </insert>

三、controller

//导入Excel
    @PostMapping("import")
    public Integer importExcel(MultipartFile file) throws IOException {

        InputStream is = file.getInputStream();
        ExcelReader reader = ExcelUtil.getReader(is);
        List<User> list = reader.readAll(User.class);
        Integer integer = userService.insertUsers(list);

        return integer;
    }

user实体类，使用@Alias注解导入导出时 excel表头可以中文



import cn.hutool.core.annotation.Alias;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

/**
 * @author 27164
 * @version 1.0
 * @description: TODO
 * @date 2023/9/23 10:34
 */

@Data
public class User {

    @Alias("id")
    private Integer id;
    @Alias("用户名")
    private String username;
    @Alias("密码")
    private String password;
    @Alias("昵称")
    private String nickname;
    @Alias("邮箱")
    private String email;
    @Alias("手机")
    private String phone;
    @Alias("地址")
    private String address;
    @Alias("创建时间")
    @JsonFormat(pattern = "yyyy年MM月dd日 HH时mm分ss秒")
    private String creatTime;
    @Alias("头像")
    private String avater;
}