spring+vue实现excel导入导出

批量导出

1. 导出按钮

//按钮
 <el-button class="user-btn" type="primary" @click="exp()">批量导出</elbutton>
 //导出⽅法
 exp(){
 location.href='http://localhost:8080/api/user/exp'
 }

2. 引入依赖

<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi-ooxml</artifactId>
 <version>4.1.2</version>
</dependency>

3. 后端导出表格接⼝

/**
 * 导出表格
 * @return
 */
@GetMapping("/exp")
public Result exp(HttpServletResponse response) throws IOException {
 //1.从数据库中查询所有数据
 List<User> users = userService.selectAll("", "");
 //2.对应表格的表头和数据，我们使⽤Map的key和value进⾏封装，多条数据使⽤list进⾏
封装
 List<Map<String,Object>> list = new ArrayList<>();
 //3.遍历查询到的数据，封装到map中，进⽽封装到list中
 for (User user : users) {
 Map<String, Object> map = new HashMap<>();
 map.put("姓名", user.getName());
 map.put("电话", user.getPhone());
 map.put("⽤户类型", user.getType());
 list.add(map);
 }
 //4.获取ExcelWriter,⽣成excel表
 ExcelWriter writer = ExcelUtil.getWriter(true);
 writer.write(list, true);
 //5.下载excel
 response.setContentType("application/vnd.openxmlformats-officedocumen
t.spreadsheetml.sheet;charset=utf-8");
 response.setHeader("Content-Disposition","attachment;filename=test.xls
x");
 ServletOutputStream outputStream = response.getOutputStream();
 writer.flush(outputStream,true);
 writer.close();
 IoUtil.close(System.out);
 return Result.success();
}

批量导⼊

1. 导⼊按钮

<!-- ⽂件上传/批量导⼊ -->
 <el-upload action="http://localhost:8080/api/user/upload" :show-fil
e-list="false" :on-success="successUpload" style="display:inline-block;">
 <el-button class="user-btn" type="primary">批量导⼊</el-button>
 </el-upload>
 
 //⽂件上传成功回调函数
successUpload(res) {
 if(res.code==='0'){
 this.$message.success('批量导⼊成功')
 this.selectAll()
 }else{
 this.$message.error(res.msg)
 }
}

2. 实体类添加导⼊映射注解

@Alias("姓名")//导⼊的表表头和对应的字段通过注解关联
private String name;
//账户
@Alias("账户")
private String username;

3. 后端表格导⼊接⼝

/**
 * ⽂件上传接⼝
 * @param file
 * @return Result
 * @throws IOException
 */
@PostMapping("/upload")
public Result upload(MultipartFile file) throws IOException {
 //读取⽂件内容
 List<User> users = ExcelUtil.getReader(file.getInputStream()).readAll(
User.class);
 if (!CollectionUtil.isEmpty(users)) {
 for (User user : users) {
 try {
 userService.add(user);
 } catch (Exception e) {
 e.printStackTrace();
 }
 }
 }
 return Result.success();
}