java使用easyExcel实现Excel导入和导出

r囧r小猫 2024-03-30 11:22 91阅读 0赞

### 1.引入依赖 ###

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.1.3</version>
    </dependency>

### 2.准备用于封装导入导出数据的entity或dto或vo ###

@Data
    @TableName("user")
    //@ColumnWidth用于设置全局单元格的统一宽度
    @ColumnWidth(25)
    public class User {
    
        @TableId("id")
        //@ExcelIgnore用于修饰无需进行导入导出的属性
        @ExcelIgnore
        private String id;
    
        @TableField("account")
        //@ExcelProperty中的value属性用于设置当前数据列对应的表头名称，index属性用于设置当前数据列所在Excel表格中的第几列，0代表第1列，1代表第2列，以此类推。
        @ExcelProperty(value = "账号", index = 0)
        private String account;
    
        @TableField("pwd")
        @ExcelProperty(value = "密码", index = 1)
        private String pwd;
    }

### 3.controller代码 ###

@ApiOperation(value = "easyExcel导入用户信息",httpMethod = "POST")
        @PostMapping("test6")
        public void test6(@RequestPart("file") MultipartFile file) throws IOException {
            //easyExcel导入用户信息
            userService.importUser(file);
        }
    
        @ApiOperation(value = "easyExcel导出用户信息",httpMethod = "GET")
        @GetMapping("/test7")
        private void test7(HttpServletResponse httpServletResponse) throws IOException {
            //easyExcel导出用户信息
            userService.exportUser(httpServletResponse);
        }

### 4.service 代码 ###

//easyExcel导入用户信息
        @Override
        public void importUser(MultipartFile file) throws IOException {
            //读取Excel文件中的数据
            List<User> userList = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(User.class).sheet().doReadSync();
            for(User user : userList){
                //将用户信息写入到数据库
                userMapper.insert(user);
            }
        }
    
        //easyExcel导出用户信息
        @Override
        public void exportUser(HttpServletResponse httpServletResponse) throws IOException {
            //从数据库中查询出需要进行导出的用户信息
            QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
            List<User> userList = userMapper.selectList(userQueryWrapper);
            //将数据用户信息导出成Excel文件并以流的形式返回
            httpServletResponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            httpServletResponse.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("用户信息", "UTF-8").replaceAll("\\+", "%20");
            httpServletResponse.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(httpServletResponse.getOutputStream(), User.class).sheet("用户信息").doWrite(userList);
        }

###  ###