spring boot+vue实现excel导入导出

待我称王封你为后i 2023-10-06 22:07 12阅读 0赞

## 一、后端 ##

### 1.1、使用工具[hutool 读取-ExcelReader][hutool _-ExcelReader] ###

### 1.2、添加依赖 ###

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

### 1.3、bean中添加toString方法 ###

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5rGf5bee55uK5b2k_size_20_color_FFFFFF_t_70_g_se_x_16]  
excel文件  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5rGf5bee55uK5b2k_size_20_color_FFFFFF_t_70_g_se_x_16 1]  
数据库表  
![在这里插入图片描述][4873cb85138b4ef6885a1fa7b9268a51.png]

### 1.4、编写controller ###

//excel导出
        @GetMapping("/exportExcel")
        public void exportExcel(HttpServletResponse response) throws Exception {
        
            //查询所有数据
            List<User> list = userService.list();
            //在内存操作，写出到浏览器，从浏览器下载
            ExcelWriter writer = ExcelUtil.getWriter(true);
            //自定义标题名
            writer.addHeaderAlias("username", "用户名");
            writer.addHeaderAlias("password", "密码");
            writer.addHeaderAlias("nickname", "昵称");
            writer.addHeaderAlias("email", "邮箱");
            writer.addHeaderAlias("phone", "电话");
            writer.addHeaderAlias("address", "地址");
            writer.addHeaderAlias("createTime", "创建时间");
            writer.addHeaderAlias("avatar", "头像");
    
            //一次性写出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 outputStream = response.getOutputStream();
            writer.flush(outputStream,true);
    
            //关闭流
            outputStream.close();
            writer.close();
        }
    
        //excel导入
        @PostMapping("/importExcel")
        public Boolean importExcel(MultipartFile file) throws IOException {
        
            InputStream inputStream = file.getInputStream();
            ExcelReader reader = ExcelUtil.getReader(inputStream);
    
            //方式1：通过JavaBean的方式读取excel内的对象，但是要求表头必须市英文，和JavaBean属性对应
    //        List<User> users = reader.readAll(User.class);
    
            //方式二：忽略表头中文，直接获取表格数据
            List<List<Object>> list = reader.read(1);
            List<User> users = CollUtil.newArrayList();
    
            for(List<Object> row:list){
        
                User user =new User();
                user.setUsername(row.get(1).toString());
                user.setPassword(row.get(2).toString());
                user.setNickname(row.get(3).toString());
                user.setEmail(row.get(4).toString());
                user.setPhone(row.get(5).toString());
                user.setAddress(row.get(6).toString());
                user.setAvatarUrl(row.get(8).toString());
    
                users.add(user);
            }
    
            //将excel导入的数据保存到数据库
            userService.saveBatch(users);
            return true;
        }

## 二、前端 ##

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5rGf5bee55uK5b2k_size_18_color_FFFFFF_t_70_g_se_x_16]  
![在这里插入图片描述][a60d66b401814d889833fac5bd9eee3a.png]

[hutool _-ExcelReader]: https://www.javajike.com/book/hutool/chapter17/c24e90a62ada80842a75f0af1418b9b3.html
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5rGf5bee55uK5b2k_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/1ebc5069fa604abc887e2df0e97790bb.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5rGf5bee55uK5b2k,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5rGf5bee55uK5b2k_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/dcfd29e650454a888d1adf0c1cc74997.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5rGf5bee55uK5b2k,size_20,color_FFFFFF,t_70,g_se,x_16
[4873cb85138b4ef6885a1fa7b9268a51.png]: https://img-blog.csdnimg.cn/4873cb85138b4ef6885a1fa7b9268a51.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5rGf5bee55uK5b2k_size_18_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/860a327e250c466b8588a9f368ea7a6b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5rGf5bee55uK5b2k,size_18,color_FFFFFF,t_70,g_se,x_16
[a60d66b401814d889833fac5bd9eee3a.png]: https://img-blog.csdnimg.cn/a60d66b401814d889833fac5bd9eee3a.png