学做毕设 2022 第十三讲 excel导入导出_毕业设计选题系统可实现excel导入-优快云博客

这篇博客展示了如何利用Hutool库和Apache POI实现Excel文件的导出与导入功能。在导出过程中，详细说明了如何设置标题别名和写入数据到Excel。在导入部分，演示了从上传的Excel文件中读取数据并打印到控制台的方法。同时，博客还包含了前端Vue.js组件用于触发文件上传和下载操作。

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
  })

},