日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

在開發中導入或者導出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一列的一個描述?？梢栽O置長寬，日期格式等屬性，具體請看文檔。
此外還有@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/