java操作excel

1：导入依赖

　　　　　<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.5</version>
        </dependency>
 
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>

　　

2：创建实体，mapper，service

 

3：编写导出excel代码

writer.addHeaderAlias("name","名字");根据自己实体进行设置

@GetMapping("/downE")
    public void downExcel(HttpServletResponse response) throws IOException{
        ArrayList<Myself> myselves = CollUtil.newArrayList(myselfServe.list());
        ExcelWriter writer = ExcelUtil.getWriter(true);
        writer.addHeaderAlias("name","名字");
        writer.addHeaderAlias("age","年龄");
        writer.addHeaderAlias("price","身价");
        writer.setOnlyAlias(true);
        writer.write(myselves,true);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode("用户.xlsx","UTF-8"));
        ServletOutputStream out=response.getOutputStream();
        writer.flush(out,true);
        writer.close();
        IoUtil.close(out);
    }

 

 4：前端代码导出

（1）下载方式一：location.href = "后端接口";window.open("后端接口")

可以实现下载，本人测试，点击后可能短时间没反应，可能是网络电脑问题，过一段时间，几秒后下载完成

 

 

downE() {
      location.href = "http://localhost:9090/hutool/downE";
      // window.open();
    },

 

5：excel导入数据到数据库

 

@PostMapping("/readE")
    public List<Myself> readExcel(@RequestParam("file") MultipartFile file) {
        redisUtil.del("data");
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        reader.addHeaderAlias("名字","name");
        reader.addHeaderAlias("年龄","age");
        reader.addHeaderAlias("身价","price");
        List<Myself> myselves = reader.readAll(Myself.class);
        for (Myself myself : myselves) {
            myselfServe.save(myself);
        }
        reader.close();
        return myselfServe.list();
    }

　　

 

 

 

 

导入后