把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