创建数据库的表,创建实体类,mapper等
public class InvestigateListener extends AnalysisEventListener<InvestigateExcel> {
private DictMapper dictMapper;
private InvestigateObjectMapper investigateMapper;
public InvestigateListener(DictMapper dictMapper, InvestigateObjectMapper investigateMapper) {
this.dictMapper = dictMapper;
this.investigateMapper = investigateMapper;
}
private int num;
private List<InvestigateObject> investigateObjectList=new ArrayList<InvestigateObject>();
public List<ImportError> errorList = new ArrayList<>();
//一行一行读
@Override
public void invoke(InvestigateExcel investigateExcel, AnalysisContext analysisContext) {
ImportError importError = mustFieldIsNull(investigateExcel);
//如果没错就将该行加入集合
if(importError.getErrorMsg()==null&&importError.getParseError()==null&&importError.getTablePosition()==null){
investigateExcel.setId(null);
InvestigateObject investigateObject = new InvestigateObject();
BeanUtil.copyProperties(investigateExcel,investigateObject);
investigateObjectList.add(investigateObject);
}else {
errorList.add(importError);
}
}
/**
* 全部的数据解析完毕后调用次方法 再次方法可做操作数据库动作 提交容器数据
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
num = 0;
// 将数据导入数据库
for (int i = 0; i < investigateObjectList.size(); i++) {
investigateMapper.insert(investigateObjectList.get(i));
}
}
public ImportError mustFieldIsNull(InvestigateExcel demoData) {
if (objectCheckIsNull(demoData)) return null;//判断最后一行是否为Null
num++;
ImportError importError = new ImportError();
String pro = demoData.getPro();
String city = demoData.getCity();
String county = demoData.getCounty();
Long code = demoData.getCode();
String town= demoData.getTown();
if (StringUtils.isBlank(pro)||StringUtils.isBlank(city)||StringUtils.isBlank(county)
||Objects.isNull(code)||StringUtils.isBlank(town)) {
//如果存在空值
importError.setErrorMsg(" [县区市旗*]列存在空职或其他错误");
importError.setTablePosition(num + "行");
return importError;
}
QueryWrapper<InvestigateObject> queryWrapper = new QueryWrapper<>();
InvestigateObject one = investigateMapper.selectOne(queryWrapper.eq("code", demoData.getCode()));
if(one!=null){
importError.setErrorMsg(demoData.getCode()+"该编码已存在!!");
importError.setTablePosition(num+"行");
return importError;
}
QueryWrapper<Dict> wrapper = new QueryWrapper<Dict>();
Dict dict= dictMapper.selectOne(wrapper.eq("name", demoData.getPro()).eq("type", "area"));
if(dict==null){
importError.setErrorMsg(demoData.getPro()+"数据有问题!!");
importError.setTablePosition(num+"行");
return importError;
}
QueryWrapper<Dict> wrapper1 = new QueryWrapper<>();
Dict dict1 = dictMapper.selectOne(wrapper1.eq("name", demoData.getCity()).eq("pid", dict.getValue()));
if(dict1==null){
importError.setErrorMsg(demoData.getCity()+"数据有问题!!");
importError.setTablePosition(num+"行");
return importError;
}
QueryWrapper<Dict> wrapper2 = new QueryWrapper<>();
Dict dict2 = dictMapper.selectOne(wrapper2.eq("pid", dict1.getValue()).eq("name", demoData.getCounty()));
if(dict2==null){
importError.setErrorMsg(demoData.getCounty()+"数据有问题!!");
importError.setTablePosition(num+"行");
return importError;
}
//TODO 没有全部判断完全
return importError;
}
public static boolean objectCheckIsNull(Object object) {
boolean flag = true; //定义返回结果,默认为true
if (Objects.isNull(object)) {
flag = true;
} else {
Class clazz = (Class) object.getClass(); // 得到类对象
Field fields[] = clazz.getDeclaredFields(); // 得到所有属性
for (Field field : fields) {
field.setAccessible(true);
Object fieldValue = null;
try {
fieldValue = field.get(object); //得到属性值
Type fieldType = field.getGenericType();//得到属性类型
String fieldName = field.getName(); // 得到属性名
} catch (IllegalArgumentException e) {
} catch (IllegalAccessException e) {
}
if (fieldValue != null) { //只要有一个属性值不为null 就返回false 表示对象不为null
flag = false;
break;
}
}
}
return flag;
}
}
@Override
@Transactional
public Result<Object> importInvest(MultipartFile file) {
if (
Objects.requireNonNull(file.getOriginalFilename()).contains(".xlsx") || file.getOriginalFilename().contains(".xls")
){
try {
InvestigateListener investigateListener= new InvestigateListener(dictMapper,investigateMapper);
EasyExcel.read(file.getInputStream(), InvestigateExcel.class,investigateListener).sheet().doRead();
List<ImportError> errors = investigateListener.errorList;
if(errors.size()>0){
return Result.error(errors);
}else return Result.ok();
} catch (IOException e) {
e.printStackTrace();
}
}
return Result.error("文件类型错误");
}