Hutool 之 Excel导入导出

前言

详情见官方文档https://hutool.cn/docs/#/poi/Excel%E5%B7%A5%E5%85%B7-ExcelUtil

依赖

<!-- Hutool poi 工具 -->
 <dependency>
      <groupId>cn.hutool</groupId>
      <artifactId>hutool-poi</artifactId>
      <version>5.7.22</version>
  </dependency>
  
<!-- poi-ooxml 必须的依赖，不然会报错-->
 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.2</version>
  </dependency>

导出

简单后端代码

@GetMapping("/eptExcel")
    public void exportExcel(HttpServletResponse response){
        //查询所有数据
        List<User> list = userService.list();
        //通过工具类创建writer
//        ExcelWriter writer = ExcelUtil.getWriter("C:/Users/l/Desktop/code/writeTest.xlsx");
        ExcelWriter writer = ExcelUtil.getWriter(true);
        //通过构造方法创建writer
        //ExcelWriter writer = new ExcelWriter("d:/writeTest.xls");
        //跳过当前行，既第一行，非必须，在此演示用
         writer.passCurrentRow();

         writer.addHeaderAlias("username", "用户名");
         writer.addHeaderAlias("jobNumber", "员工号");
         writer.addHeaderAlias("fullname", "用户姓名");
         writer.addHeaderAlias("email", "邮箱");
         writer.addHeaderAlias("address", "地址");
         writer.addHeaderAlias("phone", "电话");

         writer.setOnlyAlias(true);

        //合并单元格后的标题行，使用默认标题样式
        writer.merge(6 - 1, "测试标题");
        //一次性写出内容，强制输出标题
        writer.write(list, true);

        //设置响应头、类型等
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("员工信息表-" + DateUtil.today() + ".xls", "utf-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        ServletOutputStream os = null;
        try {
            os = response.getOutputStream();
            //将数据刷到response输出流中
            writer.flush(os, true);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            //关闭writer，释放内存
            writer.close();
        }
    }

前端

handleExportExcel(){
      window.open("http://localhost:10086/api/v1/user/eptExcel")
 }

导入

后端

@PostMapping("/importExcel")
    public JsonData importExcel(MultipartFile file){
        try {
            ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
            //用户名	员工号	用户姓名	邮箱	地址	电话
            HashMap<String, String> headerAlias = new HashMap<>(6);
            headerAlias.put("用户名", "username");
            headerAlias.put("员工号", "jobNumber");
            headerAlias.put("用户姓名", "fullname");
            headerAlias.put("邮箱", "email");
            headerAlias.put("地址", "address");
            headerAlias.put("电话", "phone");

            reader.setHeaderAlias(headerAlias);
            List<User> list = reader.read(2, 3, User.class);
            //保存
            userService.saveBatch(list);
            return JsonData.buildSuccess(true);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return JsonData.buildError(-1, "上传失败！");

    }

前端

<el-upload
          class="upload-demo"
          action="http://localhost:10086/api/v1/user/importExcel"
          :show-file-list="false"
          :accept="'.xls,.xlsx'"
          :on-success="importSuccess"
          style="display: inline-block"
        >
        <el-button type="primary" @click="handleImportExcel" style="margin-left:5px" icon="el-icon-document-add">导入</el-button>
      </el-upload>


<script>
export default {
   ....
	methods:{
		importSuccess(){
	      this.$message.success("上传成功！")
	      this.load()  //加载数据的方法
	    }
	}
}

</script>