导入依赖:
<!-- 要有这个包,才能使用excel -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.18</version>
</dependency>
<!-- 导出导入excel也要使用这个 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
导出
Controlller:(其余不用改)
/**
* 导出Excel表格
*/
@GetMapping("/export")
public Result export(Department department, HttpServletResponse response) throws IOException {
// 将一行一行的数据放到list集合里面
// 每一行数据代表java的一个实体类
// 1、从数据库中查询所有数据
List<Department> departments = departmentService.selectAll(department);
// 判断是否为空-为空不生成excel
// 2、定义一个List和Map<key,value>出来,存储处理之后的数据,用于塞到List里
List<Map<String,Object>> list = new ArrayList<>(departments.size());
// 3、遍历每一条数据,然后封装到 Map<key,value>里,把这个map塞到list里
for (Department department1 : departments) {
Map<String,Object> row = new HashMap<>();
row.put("社团名称",department1.getName());
row.put("社团介绍",department1.getDescription());
row.put("社长名称",department1.getUserName());
row.put("社团创建时间",department1.getTime());
row.put("指导老师",department1.getTeacherName());
list.add(row);
}
// 4、创建一个ExcelWriter,把 list数据用这个writer写出来
ExcelWriter wr = ExcelUtil.getWriter(true);
// write()第二个参数为true,使用row的key作为表头,然后key一样就会放到同一列上
wr.write(list,true);
//5、把这个excel下载下来
response.setContentType("application");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
// filename=department.xlsx" 设置导出excel表格文件名
response.setHeader("Content-Disposition","attachment;filename=department.xlsx");
ServletOutputStream out = response.getOutputStream();
wr.flush(out, true);
wr.close();
IoUtil.close(System.out);
return Result.success();
}
Vue:
<el-button type="success" plain @click="exp()">批量导出</el-button>
// 批量导出
exp(){
// 拿到user,将其封装成JSON对象(如果一开始user是Joson就不用转了,我的不是)
let userJson = JSON.stringify(this.user);
// 路径+token就行
location.href = "http://localhost:9090/department/export?token="+JSON.parse(userJson).token
}
导入
从excel表格中导入:
package com.example.entity;
import cn.hutool.core.annotation.Alias;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import java.io.Serializable;
/**
* 社团信息表
*/
@Data
public class Department implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 社团id
*/
/**
* 主键id
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 社团名称
*/
@Alias("社团名称")
private String name;
/**
* 社团介绍
*/
@Alias("社团介绍")
private String description;
/**
* 社团的发布时间(其实也是社团的创建时间)
*/
@Alias("社团创建时间")
private String time;
}
Controller:(Service和Mapper自己写插入即可)
@PostMapping("/upload")
public Result upload(MultipartFile file) throws IOException{
// Department.class 这个导入时要转换成的实体类,我的是Department类
List<Department> departments = ExcelUtil.getReader(file.getInputStream()).readAll(Department.class);
if(!CollectionUtil.isEmpty(departments)){
for (Department department : departments) {
try{
// 写自己service添加数据的方法就行
departmentService.add(department);
}catch (Exception e){
e.printStackTrace();
}
}
}
return Result.success();
}
前端:
<el-upload
:action="$baseUrl + '/department/upload'"
style="display: inline-block;margin-left:10px "
:show-file-list="false"
:headers="{ token: user.token }" //没用到token就不加或者放行也行
:on-success="successUpload"
>
<el-button type="primary">批量导入</el-button>
</el-upload>
方法:
successUpload(res){
this.$message.success("批量导入成功")
this.load(1);
}