一、步骤一:定义一个针对excel对应的pojo,可以采用业务名+ExcelDTO命名法,例如UserExcel,例如:
import org.hibernate.validator.constraints.NotBlank;
import cn.afterturn.easypoi.excel.annotation.Excel;
@Data // 引入了lombok,省得写set和get方法
public class UserExcelDTO{
@Excel(name = "id")
@NotBlank(message = "该字段不能为空")
private String id;
@Excel(name = "姓名")
@Pattern(regexp = "[\\u4E00-\\u9FA5]{2,5}", message = "姓名中文2-5位")
private String name;
@Max(value=20)
@Excel(name = "年龄")
private Integer age;
@Excel(name = "生日", importFormat = "yyyy-MM-dd")
}
二、service中定义:
public void importExcel(MultipartFile file){
ImportParams params= new ImportParams();
// 需要验证
params.setNeedVerfiy(true);
params.setTitleRows(1);
params.setHeadRows(1);
try{
List<UserExcelDTO> result= ExcelImportUtil.importExcelMore(
file.getInputStream(),UserExcelDTO.class, params);
List<UserExcelDTO> successList = result.getList();
List<UserExcelDTO> failList = result.getFailList();
log.info("是否存在验证未通过的数据:" + result.isVerfiyFail());
log.info("验证通过的数量:" + successList.size());
log.info("验证未通过的数量:" + failList.size());
for (UserExcelDTO user : successList) {
log.info("成功列表信息:ID=" + user.getId() + user.getName() + "-"
+ new SimpleDateFormat("yyyy-MM-dd").format(user.getBirthday()));
}
for (UserExcelDTO user : failList) {
log.info("失败列表信息:" + user.getName());
}
} catch (IOException e) {
log.error(e.getMessage(), e);
} catch (Exception e) {
log.error(e.getMessage(), e);
}
}