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

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