SpringBoot集成easypoi并对导入校验

在开发中导入或者导出Excel时，使用jxl或者poi的jar包需要要写一大段代码，而Easypoi对poi进行了封装，在导出的实体类上加入注解即可。

1、pom.xml中加入依赖

创建好springboot后，加上Easypoi依赖，本文使用的springboot是2.2.0.RELEASE版本。

   <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-spring-boot-starter</artifactId>
      <version>3.3.0</version>
    </dependency>

如果启动时报

***************************
AppLICATION FAILED TO START
***************************

Description:

The bean 'beanNameViewResolver', defined in class path resource[cn/afterturn/easypoi/configuration
/EasyPoiAutoConfiguration.class], could not be registered. A bean with that name has already been 
defined in class path resource [org/springframework/boot/autoconfigure/web/servlet/error/ErrorMvcAutoConfiguration$WhitelabelErrorViewConfiguration.class] 
and overriding is disabled.

Action:

Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true

请在application.properties文件中加入

spring.main.allow-bean-definition-overriding=true

2、创建实体类

创建一个people的实体类(导出)

@Data
public class People implements Serializable {

  @Excel(name = "姓名" ,height = 20, width = 30)
  private String name;

  @Excel(name = "年龄")
  private Integer age;

  @Excel(name = "生日",exportFormat = "yyyy-MM-dd")
  private String birthday;
}

@Excel 作用到filed上面,是对Excel一列的一个描述。可以设置长宽，日期格式等属性，具体请看文档。
此外还有@ExcelCollection @ExcelEntity @ExcelIgnore@ExcelTarget 等注解。

3、导出工具

@Slf4j
public class ExcelUtil {
  /**
   *  excel   导出
   *
   * @param list      导出的数据
   * @param sheetName sheetName
   * @param pojoClass pojo类型
   * @param fileName  文件名称
   * @param response
   */
  public static void exportExcel(List<?> list, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
    ExportParams exportParams = new ExportParams();//导出基本采用ExportParams 这个对象，进行参数配置；
    exportParams.setSheetName(sheetName);//sheetName
    exportParams.setType( ExcelType.XSSF);//配置导出excel版本格式  ExcelType.XSSF后缀名为.xlsx ExcelType.HSSF后缀名为.xls
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
    try {
      response.setCharacterEncoding("UTF-8");
      response.setHeader("content-Type", "application/vnd.ms-excel");
      response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder
          .encode(fileName + ".xlsx", "UTF-8"));//这里根据上面ExcelType.XSSF配置来配，如果是 ExcelType.XSSF 对应 .xlsx ExcelType.HSSF对应.xls
      workbook.write(response.getOutputStream());
    } catch (Exception e) {
      log.error("error {}",e.getMessage());
      throw new IOException(e.getMessage());
    }
  }

4、导出示例

  @GetMapping("export")
  public void export(HttpServletResponse response){
    List<People> peopleList = new ArrayList<>();
    People data1 = new People();
    People data2 = new People();
    data1.setName("隔壁老王");
    data1.setAge(30);
    data1.setBirthday("1997-10-01 00:00:00");
    data2.setName("钻石老王");
    data2.setAge(40);
    data2.setBirthday("1997-10-01 00:00:00");
    peopleList.add(data1);
    peopleList.add(data2);
    try {
      ExcelUtil.exportExcel(peopleList, "个人信息",People.class ,"个人信息" ,response );
    } catch (IOException e) {
      log.error("导出失败");
    }
  }

5、导入的实体

如果我们想对导入的数据进行校验，easypoi自带接口ExcelModel 获取错误信息, IExcelDataModel获取错误信息所在的行数。
自己创一个类去实现这两个接口

public class ExcelVerifyInfo implements IExcelModel, IExcelDataModel {

  private String errorMsg;

  private int rowNum;

  @Override
  public int getRowNum() {
    return rowNum;
  }

  @Override
  public void setRowNum(int rowNum) {
    this.rowNum = rowNum;
  }

  @Override
  public String getErrorMsg() {
    return errorMsg;
  }

  @Override
  public void setErrorMsg(String errorMsg) {
    this.errorMsg = errorMsg;
  }
}

对导入excel进行校验，对他的实体需要加一些注解

@Data
public class People  extends ExcelVerifyInfo implements Serializable {

  @Excel(name = "姓名" ,height = 20, width = 30)
  @NotNull(message = "姓名不能为空")
  private String name;

  @Excel(name = "年龄")
  @Max(value = 100,message = "年龄 最大值不能超过100" )
  @NotNull(message = "年龄不能为空")
  //@Pattern(regexp = "[u4E00-u9FA5]*", message = "不是中文")或者正则校验
  private Integer age;

  @Excel(name = "生日",exportFormat = "yyyy-MM-dd")
  private String birthday;
}

@NotNull，@Max，@Min，@Pattern这些注解在message属性上加入你想输出的错误信息。

6、导入工具

  /**
   * excel 导入
   *
   * @param headerRows  表头行
   * @param needVerfiy  是否检验excel内容
   * @param pojoClass   pojo类型
   * @param <T>
   * @return
   */
  public static <T> ExcelImportResult<T> importExcel(MultipartFile file, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
    if (file == null) {
      log.info("文件为空");
      return null;
    }
    ImportParams params = new ImportParams();
    params.setHeadRows(headerRows); //头行忽略的行数
    params.setNeedVerfiy(needVerfiy); //是否开启校验
    try {
      return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
    } catch (Exception e) {
      log.error("importExcel IOException {} ",e.getMessage());
      throw new IOException(e.getMessage());
    }
  }

ExcelImportResult是一个导入返回的类，里面有很多属性，讲一下常用的。

    /**
     * 结果集
     */
    private List<T>  list;
    /**
     * 失败数据
     */
    private List<T>  failList;

    /**
     * 是否存在校验失败
     */
    private boolean  verfiyFail;

7、导入示例

@PostMapping("/import")
  public void importExcel(MultipartFile file){
    if (file==null){
      log.info("file 无数据");
      return;
    }
    ExcelImportResult<People> result = null;
    try {
      result = ExcelUtil
          .importExcel(file, 1, true, People.class);
    } catch (IOException e) {
      e.printStackTrace();
    }
    List<People> failList = result.getFailList();//获取失败的数据
    if (failList.size()>0){
      for ( People people : failList) {
        log.info("第{}行,{}",people.getRowNum(),people.getErrorMsg());//打印失败的行 和失败的信息
      }
    }
    //如果没有错误，可以存入文件服务系统 或者数据库 ，这里只是将数据打印出来
    List<People> list = result.getList();
    log.info("成功导入数据 {}",JSON.toJSONString(list));
  }

附easypoi文档：http://easypoi.mydoc.io/