需求
Excel样例.png
Excel文件数据导入，即将表格中的数据存储到数据库中 ，我这里使用了阿里的 easyExcel 实现整个过程，使用Maven工具
思路
1.文件上传，将Excel表格上传
2.读取Excel数据
3.数据存入数据库中
操作
1.导入依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
2.配置文件大小
spring.servlet.multipart.max-file-size=1MB
spring.servlet.multipart.max-request-size=1MB
3.前端
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org" xmlns:sec="http://www.w3.org/1999/xhtml">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE-edge"/>
<title>上传</title>
<link rel="stylesheet" th:href="@{/css/elementUI.css}">
<!-- 引入vue 在 elementUI 之前 -->
<script th:src="@{/js/vue.js}"></script>
<!--引入 axios.js -->
<script th:src="@{/js/axios.js}"></script>
<!-- 引入组件库 -->
<script th:src="@{/js/elementUI.js}"></script>
</head>
<body id="main">
<div style="float: right;" sec:authorize="hasRole('admin')">
<el-button @click="attendanceDialogVisible = true">上传表格</el-button>
<el-dialog title="表格上传" :visible.sync="attendanceDialogVisible" width="40%">
<br><br>
<el-row :gutter="80">
<el-col :span="6" :offset="4">
<el-date-picker
v-model="dateValue"
type="date"
placeholder="选择日期"
format="yyyy年MM月dd日"
value-format="yyyy年MM月dd日">
</el-date-picker>
</el-col>
<el-col :span="12">
<el-upload
class="upload-demo"
ref="upload"
action="/manage/doUploadAttendance"
:data="attendanceTime"
:limit="1"
accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
:on-preview="handlePreview"
:on-remove="handleRemove"
:auto-upload="false"
:on-success="handleSuccess">
<el-button slot="trigger" size="small" type="primary">选取文件</el-button>
<el-button style="margin-left: 10px" size="small" type="success" @click="submitUp">
上传文件
</el-button>
<div slot="tip" class="el-upload__tip">只能上传Excel文件</div>
</el-upload>
</el-col>
</el-row>
<br> <br>
</el-dialog>
</div>
</body>
<script>
new Vue({
el: "#main",
data() {
return {
attendanceDialogVisible: false,
dateValue: '',
attendanceTime: {
upTime: ""
}
}
},
mounted() {
},
methods: {
submitUp() {
if (this.dateValue =="") {
this.$message({
showClose: true,
message: '请先选择日期！',
type: 'error',
offset: 200,
duration: 1000
});
return;
}
this.attendanceTime.upTime = this.dateValue;
this.$refs.upload.submit();
},
handlePreview(file, fileList) {
},
handleRemove(file, fileList) {
console.log(file, fileList);
},
handleSuccess(response, file, fileList) {
this.$message({
showClose: true,
message: '上传成功',
type: 'success',
offset: 200,
duration: 1000
});
this.attendanceDialogVisible=false;
}
}
})
</script>
</html>
4.后端
- 创建实体对象指定列下标号（也可以使用列名） 这里使用lombok简化
@Data
public class User{
/*
* 0 代表当前excel中的第一列，下面依次对应
*/
@ExcelProperty(index = 0)
private String username;
@ExcelProperty(index = 1)
private String password;
@ExcelProperty(index = 2)
private String dept;
}
- UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bcm.task.dao.userDao">
<!--批量插入-->
<insert id="insertList">
insert into user(username, password, dept, upTime)
values
<foreach collection="list" item="user" separator=",">
(
#{user.username}, #{user.password},#{user.dept}, #{user.upTime}
)
</foreach>
</insert>
<!--批量删除-->
<delete id="batchDeleteByID">
delete from user where username in
(
<foreach collection="list" item="username" separator=",">
#{username}
</foreach>
)
</delete>
</mapper>
- 创建数据持久化层
@Mapper
@Repository
public interface UserDao {
Integer insertList(List<User> users);
}
- 创建监听器
public class UserDataListener extends AnalysisEventListener<User> {
@Autowired
UserDao userDao;
/*
* 创建一个List用来存放数据
*/
List<User> userList = new ArrayList<>();
private UserDao UserDao;
private String upTime;
public UserDataListener(UserDao userDao, String upTime) {
this.userDao = userDao;
this.upTime = upTime;
}
/**
* 每一条数据的解析都会调用
* @param user
* @param context
*/
@Override
public void invoke(User user, AnalysisContext context) {
user.setUpTime(upTime);
UserList.add(user);
/**
* 数据量超过一定量后存储数据库，防止过多数据在内存，容易OOM
*/
if (userList.size()>= 300) {
saveData();
userList.clear();
}
saveData();
}
/**
* 所有数据解析完成后会调用此方法
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
}
/*
* 存储数据
*/
private void saveData() {
userDao.insertList(userList);
}
}
- 创建服务层
public interface IUserService {
/*
* 上传接口
*/
void doUpload(String upTime, MultipartFile file) throw IOException;
}
@Service
public class UserServiceImpl implements IUserService {
@Autowired
UserDao userDao;
public void doUpload(String upTime, MultipartFile file) throw IOException {
User user = new User();
user.setUpTime(upTime);
/*
* headRowNumber 等于1 表示从excel第二行开始读取数据（标题忽略）
*/
EasyExcel.read(file.getInputStream(), User.class, new UserDataListener(userDao, upTime)).sheet().headRowNumber(1).doRead();
}
}
- 创建控制器
@Controller
@RequestMapping(value = "/test")
public class TestController {
@Autowired
IUserService userService;
// 去上传页面
@GetMapping("toUpload")
public String toUpload() {
return "common/toUpload";
}
//上传
@PostMapping("doUpload")
@ResponseBody
public String doUpload(@RequestParam String upTime, @RequestParam MultipartFile file) throws IOException {
userService.doUpload(upTime, file);
return "上传成功";
}
}
不足
后端没有对文件进行相应的校验，如文件类型是否为xls 或 xlsx，是否是对应模板的数据......
网友评论