<properties>
<easyexcel.version>3.0.5</easyexcel.version>
<poi.version>4.1.2</poi.version>
<commons-compress.version>1.21</commons-compress.version>
</properties>
<!-- excel工具 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<!-- 修复poi漏洞 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-compress</artifactId>
<version>${commons-compress.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.version}</version>
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</exclusion>
</exclusions>
</dependency>
VO
package com.ruoyi.children.domain.vo.backend;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.ruoyi.common.validate.IdCard;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.hibernate.validator.constraints.Length;
import javax.validation.constraints.NotBlank;
@Data
@ExcelIgnoreUnannotated
public class ChildrenDetailsExcelVO {
@ExcelProperty("儿童类别")
@ApiModelProperty("儿童类别")
private String tag;
@ExcelProperty("儿童姓名")
@NotBlank(message = "儿童姓名不能为空")
@Length(min = 2,message = "儿童姓名不允许小于2个字")
@ApiModelProperty("儿童姓名")
private String name;
@ExcelProperty("身份证号")
@NotBlank(message = "身份证号不能为空")
@IdCard
@ApiModelProperty("身份证号")
private String idCard;
@ExcelProperty("县市区")
@ApiModelProperty("县市区")
private String county;
@ExcelProperty("街道/乡镇")
@ApiModelProperty("街道/乡镇")
private String country;
@ExcelProperty("村/社区")
@ApiModelProperty("村/社区")
private String village;
@ExcelProperty(value = "户口分类")
@ApiModelProperty("户口分类")
private String accountTypes;
@ExcelProperty(value = "户籍性质")
@ApiModelProperty("户籍性质")
private String accounts;
@ExcelProperty(value = "就读情况")
@ApiModelProperty("就读情况")
private String studySituations;
@ExcelProperty("户籍地址")
@ApiModelProperty("户籍地址")
private String householdAddress;
@ExcelProperty("现居地址")
@ApiModelProperty("现居地址")
@NotBlank(message = "现居地址不允许为空")
private String liveAddress;
@ExcelProperty(value = "健康状况")
@ApiModelProperty("健康状况")
private String healthConditions;
@ExcelProperty(value = "病残情况")
@ApiModelProperty("病残情况")
private String disabilitys;
@ExcelProperty(value = "残疾等级")
@ApiModelProperty("残疾等级")
private String disabilityLevels;
@ExcelProperty(value = "健康情况描述")
@ApiModelProperty("健康情况描述")
private String healthConditionDescription;
@ExcelProperty(value = "养育类型")
@ApiModelProperty("养育类型")
private String nurtureTypes;
@ExcelProperty(value = "登记日期")
@ApiModelProperty("登记日期")
private String registerDate;
@ExcelProperty(value = "人员状态")
@ApiModelProperty("登记状态")
private String registerStatuss;
@ExcelProperty(value = "父亲")
@ApiModelProperty("父亲姓名")
private String fatherName;
@ExcelProperty(value = "父亲机号码")
@ApiModelProperty("父亲联系方式")
private String fatherContact;
@ExcelProperty("父亲情况")
@ApiModelProperty("父亲监护情况:DEAD死亡,MISSING失踪,OUT_OF_CONTACT失联,DIVORCE离异/再婚(不抚养),SERIOUS_ILLNESS重病,SEVERELY_DISABLED重残,SERVE_SENTENCE服刑,FORCE_DETOXIFICATION强制隔离戒毒")
private String fatherMonitoringSituation;
@ExcelProperty(value = "母亲")
@ApiModelProperty("母亲姓名")
private String motherName;
@ExcelProperty(value = "母亲机号码")
@ApiModelProperty("母亲联系方式")
private String motherContact;
@ExcelProperty( "母亲情况")
@ApiModelProperty( "母亲监护情况:DEAD死亡,MISSING失踪,OUT_OF_CONTACT失联,DIVORCE离异/再婚(不抚养),SERIOUS_ILLNESS重病,SEVERELY_DISABLED重残,SERVE_SENTENCE服刑,FORCE_DETOXIFICATION强制隔离戒毒")
private String motherMonitoringSituation;
@ExcelProperty(value = "受委托监护人姓名")
@ApiModelProperty(value = "监护人姓名")
private String guardianName;
@ExcelProperty("受委托监护人与儿童关系")
@ApiModelProperty("受委托监护人与儿童关系 实际监护人关系:PARENT父母,GRANDPARENT(外)祖父母,RELATIVES_AND_FRIENDS亲戚朋友,OTHER其他 ")
private String actualGuardianRelationship;
@ExcelProperty(value = "受委托监护人手机号码")
@ApiModelProperty(value = "监护人联系方式")
private String guardianContact;
@ExcelProperty(value = "失败原因")
private String reasonFailure;
}
annotation
package com.alibaba.excel.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Ignore all unannotated fields.
*
* @author Jiaju Zhuang
*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelIgnoreUnannotated {
}
package com.ruoyi.common.validate;
import javax.validation.Constraint;
import javax.validation.Payload;
import java.lang.annotation.Documented;
import java.lang.annotation.Retention;
import java.lang.annotation.Target;
import static java.lang.annotation.ElementType.FIELD;
import static java.lang.annotation.RetentionPolicy.RUNTIME;
/**
* @author baobao
* @create 2021-07-14 17:31
* @description 身份证号校验注解
*/
@Target(FIELD)
@Retention(RUNTIME)
@Documented
@Constraint(validatedBy = IdCardValidator.class)
public @interface IdCard {
// 默认提示的消息
String message() default "身份证号不合法";
Class<?>[] groups() default { };
Class<? extends Payload>[] payload() default { };
}
package com.ruoyi.common.validate;
import cn.hutool.core.util.IdcardUtil;
import cn.hutool.core.util.StrUtil;
import javax.validation.ConstraintValidator;
import javax.validation.ConstraintValidatorContext;
/**
* @author baobao
* @create 2021-07-14 17:32
* @description 身份证号校验器
*/
public class IdCardValidator implements ConstraintValidator<IdCard, String> {
@Override
public boolean isValid(String idCard, ConstraintValidatorContext constraintValidatorContext) {
if (StrUtil.isBlank(idCard)){
return true;
}
return IdcardUtil.isValidCard(idCard);
}
}
controller
@ApiOperation("导入未成年人详细信息")
@PostMapping("importDetails")
@RepeatSubmit
public R importDetails(@RequestPart("file") MultipartFile file) {
try {
ExcelResult<ChildrenDetailsExcelVO> result = ExcelUtil.importExcel(file.getInputStream(), ChildrenDetailsExcelVO.class, new ChildrenDetailsImportListener());
EasyExcel.write("d:\\Users\\Administrator\\Desktop\\导入失败.xlsx", ChildrenDetailsExcelVO.class).sheet().doWrite(result.getList());
return R.ok();
} catch (Exception e) {
throw new IllegalArgumentException("文件格式不正确,导入失败", e);
}
}
listenter
import com.alibaba.excel.read.listener.ReadListener;
/**
* Excel 导入监听
*
* @author Lion Li
*/
public interface ExcelListener<T> extends ReadListener<T> {
ExcelResult<T> getExcelResult();
}
package com.ruoyi.common.excel;
import java.util.List;
/**
* excel返回对象
*
* @author Lion Li
*/
public interface ExcelResult<T> {
/**
* 对象列表
*/
List<T> getList();
/**
* 错误列表
*/
List<String> getErrorList();
/**
* 导入回执
*/
String getAnalysis();
}
package com.ruoyi.common.excel;
import cn.hutool.core.util.StrUtil;
import lombok.Setter;
import java.util.ArrayList;
import java.util.List;
/**
* 默认excel返回对象
*
* @author Yjoioooo
* @author Lion Li
*/
public class DefautExcelResult<T> implements ExcelResult<T> {
/**
* 数据对象list
*/
@Setter
private List<T> list;
/**
* 错误信息列表
*/
@Setter
private List<String> errorList;
public DefautExcelResult() {
this.list = new ArrayList<>();
this.errorList = new ArrayList<>();
}
public DefautExcelResult(List<T> list, List<String> errorList) {
this.list = list;
this.errorList = errorList;
}
public DefautExcelResult(ExcelResult<T> excelResult) {
this.list = excelResult.getList();
this.errorList = excelResult.getErrorList();
}
@Override
public List<T> getList() {
return list;
}
@Override
public List<String> getErrorList() {
return errorList;
}
/**
* 获取导入回执
*
* @return 导入回执
*/
@Override
public String getAnalysis() {
int successCount = list.size();
int errorCount = errorList.size();
if (successCount == 0) {
return "读取失败,未解析到数据";
} else {
if (errorCount == 0) {
return StrUtil.format("恭喜您,全部读取成功!共{}条", successCount);
} else {
return "";
}
}
}
}
package com.alibaba.excel.event;
import java.util.Map;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ConverterUtils;
/**
* Receives the return of each piece of data parsed
*
* @author jipengfei
*/
public abstract class AnalysisEventListener<T> implements ReadListener<T> {
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
invokeHeadMap(ConverterUtils.convertToStringMap(headMap, context), context);
}
/**
* Returns the header as a map.Override the current method to receive header data.
*
* @param headMap
* @param context
*/
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {}
}
package com.alibaba.excel.event;
import java.util.ArrayList;
import java.util.List;
import com.alibaba.excel.context.AnalysisContext;
/**
* Synchronous data reading
*
* @author Jiaju Zhuang
*/
public class SyncReadListener extends AnalysisEventListener<Object> {
private List<Object> list = new ArrayList<Object>();
@Override
public void invoke(Object object, AnalysisContext context) {
list.add(object);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {}
public List<Object> getList() {
return list;
}
public void setList(List<Object> list) {
this.list = list;
}
}
package com.ruoyi.common.excel;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.ruoyi.common.utils.JsonUtils;
import com.ruoyi.common.utils.ValidatorUtils;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import javax.validation.ConstraintViolation;
import javax.validation.ConstraintViolationException;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;
/**
* Excel 导入监听
*
* @author Yjoioooo
* @author Lion Li
*/
@Slf4j
@NoArgsConstructor
public class DefaultExcelListener<T> extends AnalysisEventListener<T> implements ExcelListener<T> {
/**
* 是否Validator检验,默认为是
*/
private Boolean isValidate = Boolean.TRUE;
/**
* excel 表头数据
*/
private Map<Integer, String> headMap;
/**
* 导入回执
*/
private ExcelResult<T> excelResult;
public DefaultExcelListener(boolean isValidate) {
this.excelResult = new DefautExcelResult<>();
this.isValidate = isValidate;
}
/**
* 处理异常
*
* @param exception ExcelDataConvertException
* @param context Excel 上下文
*/
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
String errMsg = null;
if (exception instanceof ExcelDataConvertException) {
// 如果是某一个单元格的转换异常 能获取到具体行号
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
Integer rowIndex = excelDataConvertException.getRowIndex();
Integer columnIndex = excelDataConvertException.getColumnIndex();
errMsg = StrUtil.format("第{}行-第{}列-表头{}: 解析异常<br/>",
rowIndex + 1, columnIndex + 1, headMap.get(columnIndex));
if (log.isDebugEnabled()) {
log.error(errMsg);
}
}
if (exception instanceof ConstraintViolationException) {
ConstraintViolationException constraintViolationException = (ConstraintViolationException) exception;
Set<ConstraintViolation<?>> constraintViolations = constraintViolationException.getConstraintViolations();
String constraintViolationsMsg = constraintViolations.stream()
.map(ConstraintViolation::getMessage)
.collect(Collectors.joining(", "));
errMsg = StrUtil.format("第{}行数据校验异常: {}", context.readRowHolder().getRowIndex() + 1, constraintViolationsMsg);
if (log.isDebugEnabled()) {
log.error(errMsg);
}
}
excelResult.getErrorList().add(errMsg);
throw new ExcelAnalysisException(errMsg);
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
this.headMap = headMap;
log.debug("解析到一条表头数据: {}", JsonUtils.toJsonString(headMap));
}
@Override
public void invoke(T data, AnalysisContext context) {
if (isValidate) {
ValidatorUtils.validate(data);
}
excelResult.getList().add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.debug("所有数据解析完成!");
}
@Override
public ExcelResult<T> getExcelResult() {
return excelResult;
}
}
package com.ruoyi.children.excel.listener;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.EnumUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ruoyi.children.domain.enums.*;
import com.ruoyi.children.domain.vo.backend.ChildrenDetailsExcelVO;
import com.ruoyi.children.service.backend.ChildrenInfoService;
import com.ruoyi.children.service.backend.ITagService;
import com.ruoyi.common.excel.ExcelListener;
import com.ruoyi.common.excel.ExcelResult;
import com.ruoyi.common.utils.spring.SpringUtils;
import com.ruoyi.system.service.ISysDeptService;
import javax.validation.ConstraintViolation;
import javax.validation.Validator;
import java.util.*;
import java.util.stream.Collectors;
public class ChildrenDetailsImportListener extends AnalysisEventListener<ChildrenDetailsExcelVO> implements ExcelListener<ChildrenDetailsExcelVO> {
private final List<ChildrenDetailsExcelVO> importList = new ArrayList<>();
private final List<ChildrenDetailsExcelVO> errorList = new ArrayList<>();
private final ITagService tagService = SpringUtils.getBean(ITagService.class);
private final ISysDeptService deptService = SpringUtils.getBean(ISysDeptService.class);
private final Validator validator = SpringUtils.getBean(Validator.class);
private final ChildrenInfoService childrenInfoService = SpringUtils.getBean(ChildrenInfoService.class);
@Override
public ExcelResult<ChildrenDetailsExcelVO> getExcelResult() {
return new ExcelResult<>() {
@Override
public List<ChildrenDetailsExcelVO> getList() {
return errorList;
}
@Override
public List<String> getErrorList() {
return null;
}
@Override
public String getAnalysis() {
return null;
}
};
}
@Override
public void invoke(ChildrenDetailsExcelVO data, AnalysisContext context) {
// 校验
Integer rowIndex = context.readRowHolder().getRowIndex();
StringBuilder errMsg = new StringBuilder("第" + rowIndex + "条:");
boolean valid = true;
Set<ConstraintViolation<ChildrenDetailsExcelVO>> violations = validator.validate(data);
if (CollUtil.isNotEmpty(violations)) {
valid = false;
for (ConstraintViolation<ChildrenDetailsExcelVO> violation : violations) {
errMsg.append(violation.getMessage()).append(",");
}
}
if (StrUtil.isNotBlank(data.getHealthConditions())) {
boolean flag = false;
for (HealthConditionEnum value : HealthConditionEnum.values()) {
if (value.getDesc().equals(data.getHealthConditions())) {
flag = true;
break;
}
}
if (!flag) {
valid = false;
errMsg.append("健康状况不存在,");
}
}
if (StrUtil.isNotBlank(data.getDisabilitys())) {
boolean flag = false;
for (DisabilityEnum value : DisabilityEnum.values()) {
if (value.getDesc().equals(data.getDisabilitys())) {
flag = true;
break;
}
}
if (!flag) {
valid = false;
errMsg.append("病残情况不存在,");
}
}
if (StrUtil.isNotBlank(data.getDisabilityLevels())) {
boolean flag = false;
if (EnumUtil.likeValueOf(DisabilityLevelEnum.class, data.getDisabilityLevels()) != null) {
flag = true;
}
if (!flag) {
valid = false;
errMsg.append("残疾等级不存在,");
}
}
if (StrUtil.isNotBlank(data.getFatherMonitoringSituation())) {
boolean flag = false;
for (MonitoringSituationEnum value : MonitoringSituationEnum.values()) {
if (value.getDesc().equals(data.getFatherMonitoringSituation())) {
flag = true;
}
}
if (!flag) {
valid = false;
errMsg.append("父亲状况不存在,");
}
}
if (StrUtil.isNotBlank(data.getMotherMonitoringSituation())) {
boolean flag = false;
for (MonitoringSituationEnum value : MonitoringSituationEnum.values()) {
if (value.getDesc().equals(data.getMotherMonitoringSituation())) {
flag = true;
}
}
if (!flag) {
valid = false;
errMsg.append("母亲状况不存在,");
}
}
if (StrUtil.isNotBlank(data.getActualGuardianRelationship())) {
boolean flag = false;
for (ActualGuardianRelationshipEnum value : ActualGuardianRelationshipEnum.values()) {
if (value.getDesc().equals(data.getActualGuardianRelationship())) {
flag = true;
}
}
if (!flag) {
valid = false;
errMsg.append("监护人关系不存在,");
}
}
if (StrUtil.isNotBlank(data.getAccountTypes())) {
boolean flag = false;
for (AccountTypeEnum value : AccountTypeEnum.values()) {
if (value.getDesc().equals(data.getAccountTypes())) {
flag = true;
}
}
if (!flag) {
valid = false;
errMsg.append("户口分类不存在,");
}
}
if (StrUtil.isNotBlank(data.getRegisterStatuss())) {
boolean flag = false;
for (RegisterStatusEnum value : RegisterStatusEnum.values()) {
if (value.getDesc().equals(data.getRegisterStatuss())) {
flag = true;
}
}
if (!flag) {
valid = false;
errMsg.append("人员状态不存在,");
}
}
if (importList.stream().map(ChildrenDetailsExcelVO::getIdCard).collect(Collectors.toList()).contains(data.getIdCard())) {
valid = false;
errMsg.append("该身份证导入已存在,无法重新导入");
}
if (valid) {
importList.add(data);
} else {
data.setReasonFailure(errMsg.toString());
errorList.add(data);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
childrenInfoService.importChildrenDetailsExcel(importList);
}
}