学做毕设 2022 第十三讲 excel导入导出

hutool 工具  

<!-- https://mvnrepository.com/artifact/cn.hutool/hutool-all -->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.20</version>
</dependency>

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

@GetMapping("/export")
        public void export(HttpServletResponse response) throws Exception{
        //从数据库查询所用数据
        List<User> list=userService.list();
        //通过工具类创建writer
        //ExcelWriter writer = ExcelUtil.getWriter("d:/writeTest.xlsx");
        ExcelWriter writer = ExcelUtil.getWriter(true);
        //自定义标题和别名
        writer.addHeaderAlias("username","用户名");
        writer.addHeaderAlias("password","密码");
        writer.addHeaderAlias("nickname","昵称");
        writer.addHeaderAlias("phone","手机");
        writer.addHeaderAlias("email","邮箱");
        writer.addHeaderAlias("address","地址");
        writer.addHeaderAlias("createTime","创建时间");
        writer.addHeaderAlias("avatarUrl","头像");
        //一次性写入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);
        // 关闭writer，释放内存
        out.close();
        //此处记得关闭输出Servlet流
        writer.close();
}

@PostMapping("/import")
public void imp(MultipartFile file) throws Exception{
        InputStream inputStream=file.getInputStream();
        ExcelReader reader=ExcelUtil.getReader(inputStream);
        List<User> list=reader.readAll(User.class);
        System.out.println(list);
}

<el-upload :show-file-list="false" accept="xlsx" :on-success="handleSuccess"
    action="http://localhost:9090/user/import"
    :limit="1" style="display: inline-block">
  <el-button type="primary" class="ml-5">导入<i class="el-icon-bottom"></i> </el-button>
</el-upload>

  <el-button type="primary" class="ml-5" @click="expExcel">导出<i class="el-icon-top"></i> </el-button>
</div>

handleSuccess(){
  this.$message.success("文件导入成功")
  this.load()
},

expExcel(){
   window.open("http://localhost:9090/user/export")
}

load(){
  //请求分页查询数据
  this.request.get("/user/page",{
    params:{
      pageNum:this.pageNum,
      pageSize:this.pageSize,
      username:this.username,
      email:this.email,
      address:this.address,
    }
  }).then(res=>
  {
    console.log(res)
    this.tableData=res.records
    this.total=res.total
  })

},