EasyPoi

在实际开发中经常会出现需要将数据库数据导出报表等功能，一般会用到poi库。poi是一个专门给Java程序提供格式文档读写功能的API接口，包括各种微软的格式文档入excel、word等。
Easypoi是在poi接口基础上进行了封装，通过注解的形式导入、导出。

注解说明

@Excel：用于filed字段上，说明一个Excel的列属性。常用参数有

@ExcelCollection：表示一个集合，可以用于表示一对多关系。

@ExcelIgnore：导入导出时需要忽略的字段。

@ExcelEntity：标记为实体类。

依赖

<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-spring-boot-starter</artifactId>
  <version>版本号</version>
</dependency>

导出excel

创建部门VO

@Data
public class DeptVO implements Serializable {
    private static final long serialVersionUID = -93083285253332352L;

    @Excel(name = "部门名称", width = 10, needMerge = true)
    private String dName;

    @Excel(name = "部门成立时间", width = 20, format = "yyyy-MM-dd HH:mm:ss" , needMerge = true)
    private LocalDateTime dBirthDay;

    @ExcelEntity
    @ExcelCollection(name = "部门成员")
    private List<UserVO> userVOS;
}

 创建用户VO

@Data
public class UserVO implements Serializable {
    private static final long serialVersionUID = 5460387575308454847L;

    @Excel(name = "姓名", width = 10)
    private String uName;

    @Excel(name = "年龄", width = 10)
    private Integer uAge;

    @Excel(name = "出生日期", width = 20, format = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime uBirthDay;

}

获取数据

@Service
public class DeptServiceImpl implements DeptService {

    @Autowired
    private DeptMapper deptMapper;

    @Autowired
    private UserMapper userMapper;

    @Override
    public List<DeptVO> queryDeptInfo() {
        return deptMapper.selectList(null).stream().map(dept -> {
            // 查询部门成员
            QueryWrapper<User> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("d_id", dept.getDId());

            DeptVO deptVO = new DeptVO();
            deptVO.setDName(dept.getDName());
            deptVO.setDBirthDay(dept.getDBirthDay());
            deptVO.setUserVOS(
                    userMapper.selectList(queryWrapper).stream().map(user -> {
                        UserVO userVO = new UserVO();
                        userVO.setUName(user.getUName());
                        userVO.setUAge(user.getUAge());
                        userVO.setUBirthDay(user.getUBirthDay());
                        return userVO;
                    }).collect(Collectors.toList())
            );
            return deptVO;
        }).collect(Collectors.toList());
    }
}

 导出逻辑

@Service
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private DeptServiceImpl deptService;

    @Override
    public boolean exportExcel(HttpServletResponse response) {
        ServletOutputStream outputStream = null;
        Workbook workbook = null;
        try {
            // 获取数据
            List<DeptVO> deptVOS = deptService.queryDeptInfo();
            workbook = ExcelExportUtil.exportExcel(new ExportParams("部门信息", "部门"), DeptVO.class, deptVOS);
            // 写出
            outputStream = response.getOutputStream();
            //设置请求头,解决文件名中文乱码问题
            response.setHeader("content-disposition", "attachment;fileName=" + URLEncoder.encode("部门信息.xls", "utf-8"));
            workbook.write(outputStream);
            return true;
        } catch (Exception e) {
            return false;
        } finally {
            try {
                outputStream.close();
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

@RestController
@RequestMapping("/template")
public class TemplateController extends BaseController{

    @Autowired
    private ExcelServiceImpl excelService;

    @GetMapping("/exportExcel")
    public ResponseResult exportExcel(HttpServletResponse response) {
        if (excelService.exportExcel(response)) {
            return success();
        }
        return fail();
    }
}

 导出的excel

导入import，并效验参数

IExcelModel，IExcelDataModel 用来获取错误信息。

@Data
public class ExcelVerify implements Serializable, IExcelModel, IExcelDataModel {

    private static final long serialVersionUID = -3782136435626200200L;

    /**
     * 行数
     */
    private Integer rowNum;

    /**
     * 错误信息
     */
    private String errorMsg;
}

因为 EasyPOI 支持 Hibernate Validator ，所以直接使用就可以了。引入spring-boot-starter-validation

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-validation</artifactId>
    <version>版本号</version>
</dependency>

 实体类

@Data
public class DeptVO extends ExcelVerify {

    @NotBlank(message = "dName is not null")
    @Excel(name = "部门名称", width = 10, needMerge = true)
    private String dName;

    @Excel(name = "部门成立时间", width = 20, format = "yyyy-MM-dd HH:mm:ss" , needMerge = true)
    private LocalDateTime dBirthDay;

    @ExcelEntity
    @ExcelCollection(name = "部门成员")
    private List<UserVO> userVOS;

}

@Data
public class UserVO extends ExcelVerify {

    @Excel(name = "姓名", width = 10)
    private String uName;

    @Excel(name = "年龄", width = 10)
    private Integer uAge;

    @Excel(name = "出生日期", width = 20, format = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime uBirthDay;

}

 创建自定义效验逻辑

@Component
public class ExcelVerifyHandler implements IExcelVerifyHandler<DeptVO> {

    @Override
    public ExcelVerifyHandlerResult verifyHandler(DeptVO deptVO) {
        /**
         * 自定义效验规则
         * 假设添加数据，现在去数据库查询，如果存在则表示校验不通过。
         */
        return new ExcelVerifyHandlerResult(true);
    }

}

导入逻辑

@Service
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private DeptMapper deptMapper;

    @Autowired
    private ExcelVerifyHandler excelVerifyHandler;

    @Override
    public String importExcel(MultipartFile file) {
        StringBuffer verifyReslut = new StringBuffer();
        if (file == null || file.getSize() == 0) {
            return verifyReslut.toString();
        }
        InputStream stream = null;
        try {
            // 文件流
            stream = file.getInputStream();

            // 导入类型
            ImportParams params = new ImportParams();
            // 标题占用多少行
            params.setTitleRows(1);
            // 头部属性占用多少行
            params.setHeadRows(2);
            // 从指定的sheet的下标开始读取
            // params.setStartSheetIndex(1);
            // 读取sheet的数量，需要和上面的配合
            // params.setSheetNum(1);
            // 开启效验
            params.setNeedVerify(true);
            // 自定义的验证器
            params.setVerifyHandler(excelVerifyHandler);

            // 根据流导入
            ExcelImportResult<DeptVO> excelImportResult = ExcelImportUtil.importExcelMore(stream, DeptVO.class, params);

            // 是否效验失败
            if (excelImportResult.isVerifyFail()) {
                excelImportResult.getFailList().forEach(deptVO -> {
                    verifyReslut.append("错误行" + deptVO.getRowNum() + "错误信息" + deptVO.getErrorMsg() + "\n");
                });
            }

            // 效验成功
            List<DeptVO> deptVOS = excelImportResult.getList();

            // 入库
            deptVOS.forEach(deptVO -> {
                // 部门
                Dept dept = new Dept();
                dept.setDName(deptVO.getDName());
                dept.setDBirthDay(deptVO.getDBirthDay());
                deptMapper.insert(dept);
                // 用户
                UserVO userVO = deptVO.getUserVOS().get(0);
                User user = new User();
                user.setUName(userVO.getUName());
                user.setUAge(userVO.getUAge());
                user.setUBirthDay(userVO.getUBirthDay());
                userMapper.insert(user);
            });
            return verifyReslut.toString();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stream.close();
            } catch (IOException io) {
            }
        }
        return verifyReslut.toString();
    }
}

@RestController
@RequestMapping("/template")
public class TemplateController extends BaseController{

    @Autowired
    private ExcelServiceImpl excelService;

    @PostMapping("/importExcel")
    public ResponseResult importExcel(@RequestParam("file") MultipartFile file) {
        return success(excelService.importExcel(file));
    }

}

测试