1.导入依赖
<!-- Excel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
2.Excel 上传工具类
public class ExcelListener extends AnalysisEventListener<User> {
@Resource
private UserMapper userMapper;
@Resource
private RoleMapper roleMapper;
private static final int BATCH_COUNT = 5;
List<User> list = new ArrayList<>();
List<Integer> userIdList = new ArrayList<>();
@Override
public void invoke(User data, AnalysisContext analysisContext) {
list.add(data);
}
private void saveData() {
userMapper.insertMore(list);
for (User user : list) {
userIdList.add(user.getUserId());
}
roleMapper.insertRoleMore(userIdList);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
}
}
3.Excel 下载案例实体类
@Data
public class UserExcel {
// index 会影响excel数据排列顺序
@ExcelProperty(value = "user_id",index = 0)
private Integer userId;
@ExcelProperty(value = "头像",index = 1)
private String userImage;
@ExcelProperty(value = "用户名",index = 2)
private String username;
@ExcelProperty(value = "密码",index = 3)
private String password;
@ExcelProperty(value = "年龄",index = 4)
private Integer userAge;
@ExcelProperty(value = "性别",index = 5)
private String userGender;
@ExcelProperty(value = "状态",index = 6)
private Integer userState;
}
4.案例
User 类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer userId;
private String userImage;
private String username;
private String password;
private Integer userAge;
private String userGender;
private Integer userState;
}
Role 类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Role {
private Integer roleId;
private String roleName;
}
UserMapper 接口
@Insert("<script>INSERT INTO `user`(`username`,`password`,`user_age`,`user_gender`) VALUES " +
"<foreach collection='list' separator=',' item='item'> " +
"(#{item.username},#{item.password},#{item.userAge},#{item.userGender}) " +
"</foreach> " +
"</script>")
@Options(useGeneratedKeys = true,keyColumn = "user_id",keyProperty = "userId")
void insertMore(@Param("list") List<User> list);
RoleMapper 接口
@Insert("<script>INSERT INTO `user_role_rel`(`user_id`,`role_id`) VALUES " +
"<foreach collection='list' separator=',' item='item'> " +
"(#{item},2) " +
"</foreach> " +
"</script>")
void insertRoleMore(List<Integer> userIdList);
UserService 接口等省略
UserController
@GetMapping("/excelDownload")
public void download(HttpServletResponse response) {
List<User> list = userService.selectList();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = null;
try {
fileName = URLEncoder.encode("userInfo", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), UserExcel.class).sheet("userInfo").doWrite(list);
} catch (IOException e) {
e.printStackTrace();
}
}
@PostMapping("/excelUpload")
public ResponseMessage upload(MultipartFile file) {
try {
EasyExcel.read(file.getInputStream(),User.class,excelListener).sheet().doRead();
return new ResponseMessage(200,"上传完成");
} catch (IOException e) {
return new ResponseMessage(500,"上传失败",e.getMessage());
}
}
前端下载
<el-button type="info" @click="excelDownload" style="float:left;">导出数据</el-button>
excelDownload(){
let _this = this;
this.$axios({
method: 'get',
url: 'user/excelDownload',
responseType: 'arraybuffer',
}).then((rs) => {
if (!rs) return alert('文件下载失败');
let url = window.URL.createObjectURL(new Blob([rs.data], {type: '.xlsx'}));
let a = document.createElement('a');
a.style.display = 'none';
a.href = url;
a.setAttribute('download', `userInfo.xlsx`);
document.body.appendChild(a);
a.click();
url = window.URL.revokeObjectURL(url);
document.body.removeChild(a)
}).catch((error) => {
_this.$message(error);
})
},
前端上传
<el-upload
class="avatar-uploader"
action
:limit="1"
:show-file-list="false"
:http-request="uploadExcel"
:on-success="handleAvatarSuccess">
<el-button type="default" style="float:left;margin-left: 8px">上传数据</el-button>
</el-upload>
uploadExcel(param){
let _this = this;
const formData = new FormData()
formData.append('file', param.file)
this.$axios({
method: 'post',
url: 'user/excelUpload',
headers:{
'content-type': 'multipart/form-data'
},
data:formData
}).then((rs) => {
_this.$message(rs.data.msg);
setTimeout(function () {
_this.$router.go(0);
}, 2000);
}).catch((error) => {
_this.$message(error);
})
},
handleAvatarSuccess(rs, file, fileList){
this.$message(rs.data.msg);
}