把Excel表格儲存到數據庫

1瓮床、導入依賴

  <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
   </dependency>

   <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
    </dependency>

    <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
    </dependency>

<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1.tmp</version>
 </dependency>

2惫皱、監(jiān)聽器 DataListener

package com.example.controller;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.dao.UploadDAO;
import com.example.entity.Upload;

import java.util.ArrayList;
import java.util.List;

public class UploadDataListener extends AnalysisEventListener<Upload> {

    private static final int BATCH_COUNT = 5;
    List<Upload> list = new ArrayList<Upload>();
    
    public UploadDataListener(UploadDAO uploadDAO) {
        this.uploadDAO = uploadDAO;
    }
    
    @Override
    public void invoke(Upload data, AnalysisContext context) {
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

        saveData();
    }

    private void saveData() {
        uploadDAO.save(list);
    }
}

3、Dao

package com.example.dao;
import com.example.entity.Upload;
import com.example.mapper.UploadMapper;
import org.apache.shiro.crypto.hash.Md5Hash;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class UploadDAO {

    @Autowired
    public UploadMapper uploadMapper;

    public void save(List<Upload> list) {

       list.forEach(System.out::println);
       for (Upload upload:list
             ) {
            uploadMapper.insert(upload);
        }
       //密碼轉換
       // Md5Hash md5Hash = new Md5Hash(user.getPassword(),user.getName(),256);
       // user.setPassword(md5Hash.toHex());
    }
}

4眷唉、Data層

package com.example.entity;

import lombok.Data;

@Data
public class Upload {

    private int id;
    private String name;
    private Integer age;
}

5予颤、Web調用

    @PostMapping("/upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), Upload.class, new UploadDataListener(uploadDAO)).sheet().doRead();
        return "{\"code\":0,\"msg\":\"上傳成功\"}";
    }

6、Application.yml配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/newdata?useUnicode=true&characterEncoding=utf-8
    username: root
    password: root

  thymeleaf:
    prefix: classpath:/templates/
    suffix: .html