6.导入导出
6.1导入依赖
<!--工具类-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.3</version>
</dependency>
<!--excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>
6.2导出
后端:XXXcontroller.java
@GetMapping("/export")
public void exportUser(HttpServletResponse response) throws Exception {
// 从数据库查询出所有的数据
List<User> list = userService.getAllUser();
// 通过工具类创建writer 写出到磁盘路径
// ExcelWriter writer = ExcelUtil.getWriter(filesUploadPath + "/用户信息.xlsx");
// 在内存操作,写出到浏览器
ExcelWriter writer = ExcelUtil.getWriter(true);
//自定义标题别名
writer.addHeaderAlias("id","id");
writer.addHeaderAlias("name","姓名");
writer.addHeaderAlias("username","会员编号");
writer.addHeaderAlias("age","年龄");
writer.addHeaderAlias("sex","性别");
writer.addHeaderAlias("phone","联系方式");
writer.addHeaderAlias("address","地址");
writer.addHeaderAlias("account","账户余额");
writer.addHeaderAlias("score","上次交易积分变化");
writer.addHeaderAlias("status","状态");
writer.addHeaderAlias("createtime","建号时间");
writer.addHeaderAlias("updatetime","修改时间");
// 一次性写出list内的对象到excel,使用默认样式,强制输出标题
writer.write(list, true);
// 设置浏览器响应的格式
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
String fileName = URLEncoder.encode("用户信息", "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
out.close();
writer.close();
}
前端:XXXList.vue
<!--html -->
<el-button type="success" style="margin-left: 5px" @click="exportBook">
<i class="el-icon-download"></i> 导出
</el-button>
<!--script里面的methods -->
exportBook(){
window.open(`http://localhost:8081/user/export`)
this.$message({
type: 'success',
message: '导出成功!!!'
})
},
6.3导入
要确定好导入的字段
eg:向book表导入这个excel表
后端:XXXcontroller.java
@PostMapping("/import")
public Result importBook(MultipartFile file) throws Exception {
InputStream inputStream = file.getInputStream();
ExcelReader reader = ExcelUtil.getReader(inputStream);
// 方式1:(推荐) 通过 javabean的方式读取Excel内的对象,但是要求表头必须是英文,跟javabean的属性要对应起来
// List<User> list = reader.readAll(User.class);
// 方式2:忽略表头的中文,直接读取表的内容
List<List<Object>> list = reader.read(1);
List<Book> books = CollUtil.newArrayList();
for (List<Object> row : list) {
Book book = new Book();
book.setName(row.get(0).toString());
book.setDescription(row.get(1).toString());
book.setPublishDate(row.get(2).toString());
book.setAuthor(row.get(3).toString());
book.setPublisher(row.get(4).toString());
book.setBookNo(row.get(5).toString());
book.setCover(row.get(6).toString());
book.setScore(Integer.valueOf(row.get(7).toString()));
book.setNums(Integer.valueOf(row.get(8).toString()));
books.add(book);
}
/* try {
bookService.saveBatch(books);
}catch (Exception e){
System.out.println(e);
return Result.error("2","系统正在升级,请您稍后重新尝试,请谅解!!!");
}*/
bookService.saveBatch(books);
return Result.success(true);
}
前端:XXXList.vue
<!--html -->
<el-upload :action="'http://localhost:8081/book/import'" :show-file-list="false" accept="xlsx" :on-success="handleExcelImportSuccess" style="display: inline-block">
<el-button type="primary" class="ml-5"><i class="el-icon-upload2"></i> 导入</el-button>
</el-upload>
<!--script里面的methods -->
handleExcelImportSuccess(){
this.$message({
type: 'success',
message: '导入成功!!!'
})
this.load();
}
返回类型:Result.java
package com.wxy.common;
public class Result<T> {
private String code;
private String msg;
private T data;
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public T getData() {
return data;
}
public void setData(T data) {
this.data = data;
}
public Result() {
}
public Result(T data) {
this.data = data;
}
public static Result success() {
Result result = new Result<>();
result.setCode("0");
result.setMsg("成功");
return result;
}
public static <T> Result<T> success(T data) {
Result<T> result = new Result<>(data);
result.setCode("0");
result.setMsg("成功");
return result;
}
public static Result error(String code, String msg) {
Result result = new Result();
result.setCode(code);
result.setMsg(msg);
return result;
}
}