java/zhihuishenqu.git

			@@ -1,42 +1,35 @@
			package com.panzhihua.common.listen;

			import com.alibaba.excel.EasyExcel;
			import com.alibaba.excel.ExcelWriter;
			import java.text.ParseException;
			import java.text.SimpleDateFormat;
			import java.util.ArrayList;
			import java.util.List;
			import java.util.Map;
			import java.util.Objects;
			import java.util.concurrent.TimeUnit;
			import java.util.stream.Collectors;

			import org.springframework.data.redis.core.StringRedisTemplate;
			import org.springframework.data.redis.core.ValueOperations;

			import com.alibaba.excel.context.AnalysisContext;
			import com.alibaba.excel.event.AnalysisEventListener;
			import com.alibaba.excel.write.metadata.WriteSheet;
			import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
			import com.alibaba.fastjson.JSON;
			import com.alibaba.fastjson.JSONArray;
			import com.google.common.collect.Lists;
			import com.panzhihua.common.constants.BcDictionaryConstants;
			import com.panzhihua.common.constants.Constants;
			import com.panzhihua.common.enums.*;
			import com.panzhihua.common.excel.CustomSheetWriteHandler;
			import com.panzhihua.common.exceptions.ServiceException;
			import com.panzhihua.common.model.vos.BcDictionaryVO;
			import com.panzhihua.common.model.vos.R;
			import com.panzhihua.common.model.vos.community.ComMngPopulationImportErrorVO;
			import com.panzhihua.common.model.vos.community.ComMngPopulationMistakeExcelVO;
			import com.panzhihua.common.model.vos.community.ComMngPopulationServeExcelVO;
			import com.panzhihua.common.service.community.CommunityService;
			import com.panzhihua.common.utlis.*;
			import lombok.extern.slf4j.Slf4j;
			import org.springframework.data.redis.core.StringRedisTemplate;
			import org.springframework.data.redis.core.ValueOperations;
			import org.springframework.stereotype.Component;
			import org.springframework.stereotype.Service;
			import org.springframework.util.ObjectUtils;
			import com.panzhihua.common.utlis.ListUtils;
			import com.panzhihua.common.utlis.PayUtil;
			import com.panzhihua.common.utlis.StringUtils;

			import javax.annotation.Resource;
			import java.io.File;
			import java.io.FileInputStream;
			import java.io.InputStream;
			import java.text.ParseException;
			import java.text.SimpleDateFormat;
			import java.util.*;
			import java.util.concurrent.TimeUnit;
			import java.util.stream.Collectors;
			import lombok.extern.slf4j.Slf4j;

			/**
			* @description: 实有人口导入监听
			@@ -44,774 +37,717 @@
			*/
			@Slf4j
			public class ComMngPopulationServeExcelListen extends AnalysisEventListener<Map<Integer, String>> {
			private CommunityService communityService;

			private Long communityId;

			/**
			* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
			*/
			private static final int BATCH_COUNT = 5000;
			private static int headSize = 0;

			List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();
			private CommunityService communityService;
			private Long communityId;
			private Long userId;
			private Map<Integer, String> headData;

			private String userName;
			// FTP 登录密码
			private String password;
			// FTP 服务器地址IP地址
			private String host;
			// FTP 端口
			private int port;
			private String excelUrl;

			private StringRedisTemplate stringRedisTemplate;

			public static String loadUrl;
			public ComMngPopulationServeExcelListen(CommunityService communityService, Long communityId, Long userId,
			StringRedisTemplate stringRedisTemplate) {
			this.communityService = communityService;
			this.communityId = communityId;
			this.userId = userId;
			this.stringRedisTemplate = stringRedisTemplate;
			}

			public ComMngPopulationServeExcelListen(CommunityService communityService, Long communityId, String userName, String password, String host, int port, String excelUrl,StringRedisTemplate stringRedisTemplate) {
			this.communityService = communityService;
			this.communityId = communityId;
			this.userName = userName;
			this.password = password;
			this.host = host;
			this.port = port;
			this.excelUrl = excelUrl;
			this.stringRedisTemplate = stringRedisTemplate;
			}

			/**
			* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
			*/
			private static final int BATCH_COUNT = 5000;
			List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();

			@Override
			public void invoke(Map<Integer, String> data, AnalysisContext context) {
			list.add(data);
			if (list.size() >= BATCH_COUNT) {
			saveData();
			list.clear();
			}
			}

			/**
			* 这里会一行行的返回头
			*
			* @param headMap
			* @param context
			*/
			@Override
			public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
			headSize = headMap.size();
			headData = headMap;
			}

			@Override
			public void doAfterAllAnalysed(AnalysisContext context) {
			@Override
			public void invoke(Map<Integer, String> data, AnalysisContext context) {
			list.add(data);
			if (list.size() >= BATCH_COUNT) {
			saveData();
			log.info("所有数据解析完成！");
			list.clear();
			}
			}

			/**
			* 不是固定的列只能手动处理
			*/
			private void saveData() {
			R<List<BcDictionaryVO>> dictionaryR = communityService.listDictionaryByKey(BcDictionaryConstants.FAMILY);
			log.info("开始导入人口数据");
			log.info("表格总数据：" + list.size());
			if (list.size() == 0) {
			throw new ServiceException("000", "导入数据为空！");
			}
			ValueOperations<String, String> valueOperations = stringRedisTemplate.opsForValue();
			String key = Constants.POPULATION_ERROR_LIST;
			/**
			* 这里会一行行的返回头
			*
			* @param headMap
			* @param context
			*/
			@Override
			public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
			headSize = headMap.size();
			headData = headMap;
			}

			Map<String,String> dictMap = dictionaryR.getData().stream().collect(Collectors.toMap(BcDictionaryVO::getDictName,BcDictionaryVO::getDictValue));
			// Map<String,Integer> dictMap = new HashMap<>();
			// for(BcDictionaryVO vo : dictionaryR.getData()){
			// dictMap.put(vo.getDictName(),Integer.valueOf(vo.getDictValue()));
			// }
			@Override
			public void doAfterAllAnalysed(AnalysisContext context) {
			saveData();
			log.info("所有数据解析完成！");
			}

			int index = 2;
			try {
			ArrayList<ComMngPopulationServeExcelVO> voList = Lists.newArrayList();
			// List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>();
			ArrayList<ComMngPopulationMistakeExcelVO> mistakes = Lists.newArrayList();
			for (Map<Integer, String> oneData : list) {
			ComMngPopulationServeExcelVO vo = new ComMngPopulationServeExcelVO();
			//姓名和身份证都为空,为空户，无需操作,否则就解析年龄，性别，出生年月日
			if(StringUtils.isNotEmpty(oneData.get(0)) \|\| StringUtils.isNotEmpty(oneData.get(1))){
			if(StringUtils.isEmpty(oneData.get(0))){
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第1列");
			// importErrorVO.setErrorMsg("名字不可为空，请填写姓名");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			setMistake(oneData, mistake);
			mistake.setMistake("名字不可为空，请填写姓名");
			mistakes.add(mistake);
			continue;
			}
			vo.setName(oneData.get(0));
			if(StringUtils.isEmpty(oneData.get(1))){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第2列"+oneData.get(1));
			// importErrorVO.setErrorMsg("身份证号不可为空，请填写身份证号");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			setMistake(oneData, mistake);
			mistake.setMistake("身份证号不可为空，请填写身份证号");
			mistakes.add(mistake);
			continue;
			}
			/**
			* 不是固定的列只能手动处理
			*/
			private void saveData() {
			R<List<BcDictionaryVO>> dictionaryR = communityService.listDictionaryByKey(BcDictionaryConstants.FAMILY);
			log.info("开始导入人口数据");
			log.info("表格总数据：" + list.size());
			if (list.size() == 0) {
			throw new ServiceException("000", "导入数据为空！");
			}
			ValueOperations<String, String> valueOperations = stringRedisTemplate.opsForValue();
			String key = Constants.POPULATION_ERROR_LIST;

			//判断身份证号码位数
			if(oneData.get(1).length() != 18){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第2列"+oneData.get(1));
			// importErrorVO.setErrorMsg("身份证号位数有误，请检查身份证号码是否正确");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("身份证号位数有误，请检查身份证号码是否正确");
			mistakes.add(mistake);
			continue;
			}
			vo.setCardNo(oneData.get(1).toUpperCase());
			//根据身份证号码解析年龄以及性别
			//获取用户生日
			String birthday = vo.getCardNo().substring(6, 14);
			if(StringUtils.isNotEmpty(birthday)){
			String year = birthday.substring(0,4);
			String month = birthday.substring(4,6);
			String day = birthday.substring(6,8);
			vo.setBirthday(year + "-" + month + "-" + day);
			}
			// //设置用户年龄
			// vo.setAge(AgeUtils.getAgeFromBirthTime(birthday));
			//获取用户性别
			int sex = Integer.parseInt(vo.getCardNo().substring(16, 17));
			if(sex%2 == 1){
			vo.setSex(PopulSexEnum.nan.getCode());
			}else{
			vo.setSex(PopulSexEnum.nv.getCode());
			}
			}
			if(StringUtils.isNotEmpty(oneData.get(2))){
			// String nation = oneData.get(2);
			// if(!nation.contains("族")){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第3列");
			// importErrorVO.setErrorMsg("您填写的民族格式有误");
			// populationImportErrorVOList.add(importErrorVO);
			// index++;
			// continue;
			// }
			vo.setNation(oneData.get(2));
			}
			if(StringUtils.isNotEmpty(oneData.get(3))){
			vo.setPoliticalOutlook(PopulPoliticalOutlookEnum.getCodeByName(oneData.get(3)));
			Integer isOk = PopulPoliticalOutlookEnum.getCodeByName(oneData.get(3));
			if(isOk.equals(-1)){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第4列");
			// importErrorVO.setErrorMsg("您填写的政治面貌有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的政治面貌有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setPoliticalOutlook(isOk);
			}
			if(StringUtils.isNotEmpty(oneData.get(4))){
			Integer isOk = PopulHouseUseEnum.getCodeByName(oneData.get(4));
			if(isOk.equals(-1)){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第5列");
			// importErrorVO.setErrorMsg("您填写的是否租住有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的是否租住有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setIsRent(isOk);
			}else {
			vo.setIsRent(PopulHouseUseEnum.getCodeByName("否"));
			}
			if(StringUtils.isNotEmpty(oneData.get(5))){
			Map<String, String> dictMap = dictionaryR.getData().stream()
			.collect(Collectors.toMap(BcDictionaryVO::getDictName, BcDictionaryVO::getDictValue));
			// Map<String,Integer> dictMap = new HashMap<>();
			// for(BcDictionaryVO vo : dictionaryR.getData()){
			// dictMap.put(vo.getDictName(),Integer.valueOf(vo.getDictValue()));
			// }

			// String ra = convertRelation(oneData.get(5));
			//
			// String relation = dictMap.get(ra);
			// if(StringUtils.isEmpty(relation)){
			//// vo.setRelationStr(ra);
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第6列");
			// importErrorVO.setErrorMsg("您填写的与户主关系有误");
			// populationImportErrorVOList.add(importErrorVO);
			// index++;
			// continue;
			// }else{
			// vo.setRelation(Integer.valueOf(relation));
			// }
			// String relation = convertMarriage(oneData.get(5));
			Integer isOk = PopulRelationEnum.getCodeByName(oneData.get(5));

			if(isOk.equals(-1)){
			// vo.setMarriageStr(ma);
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第6列");
			// importErrorVO.setErrorMsg("您填写的与户主关系有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的与户主关系有误");
			mistakes.add(mistake);
			continue;
			}else{
			vo.setRelation(isOk);
			}
			}
			if(StringUtils.isEmpty(oneData.get(6))){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第7列");
			// importErrorVO.setErrorMsg("街路巷不可为空");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			int index = 2;
			try {
			ArrayList<ComMngPopulationServeExcelVO> voList = Lists.newArrayList();
			// List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>();
			ArrayList<ComMngPopulationMistakeExcelVO> mistakes = Lists.newArrayList();
			for (Map<Integer, String> oneData : list) {
			ComMngPopulationServeExcelVO vo = new ComMngPopulationServeExcelVO();
			// 姓名和身份证都为空,为空户，无需操作,否则就解析年龄，性别，出生年月日
			if (StringUtils.isNotEmpty(oneData.get(0)) \|\| StringUtils.isNotEmpty(oneData.get(1))) {
			if (StringUtils.isEmpty(oneData.get(0))) {
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第1列");
			// importErrorVO.setErrorMsg("名字不可为空，请填写姓名");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			setMistake(oneData, mistake);
			mistake.setMistake("街路巷不可为空");
			mistake.setMistake("名字不可为空，请填写姓名");
			mistakes.add(mistake);
			continue;
			}
			vo.setRoad(oneData.get(6));
			if(StringUtils.isEmpty(oneData.get(7))){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第8列");
			// importErrorVO.setErrorMsg("小区号不可为空");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			vo.setName(oneData.get(0));
			if (StringUtils.isEmpty(oneData.get(1))) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第2列"+oneData.get(1));
			// importErrorVO.setErrorMsg("身份证号不可为空，请填写身份证号");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			setMistake(oneData, mistake);
			mistake.setMistake("小区号不可为空");
			mistake.setMistake("身份证号不可为空，请填写身份证号");
			mistakes.add(mistake);
			continue;
			}
			vo.setDoorNo(oneData.get(7).trim());
			// if(StringUtils.isEmpty(oneData.get(8))){
			//// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			//// importErrorVO.setErrorPosition("第" + index + "行，第9列");
			//// importErrorVO.setErrorMsg("楼排号不可为空");
			//// populationImportErrorVOList.add(importErrorVO);
			// index++;
			// ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			// setMistake(oneData, mistake);
			// mistake.setMistake("楼排号不可为空");
			// mistakes.add(mistake);
			// continue;
			// }
			if(StringUtils.isNotEmpty(oneData.get(8))) {
			vo.setFloor(oneData.get(8).trim());
			}
			// if(StringUtils.isEmpty(oneData.get(9))){
			//// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			//// importErrorVO.setErrorPosition("第" + index + "行，第10列");
			//// importErrorVO.setErrorMsg("单元号不可为空");
			//// populationImportErrorVOList.add(importErrorVO);
			// index++;
			// ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			// setMistake(oneData, mistake);
			// mistake.setMistake("单元号不可为空");
			// mistakes.add(mistake);
			// continue;
			// }
			if(StringUtils.isNotEmpty(oneData.get(9))) {
			vo.setUnitNo(oneData.get(9).trim());
			}
			// if(StringUtils.isEmpty(oneData.get(10))){
			//// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			//// importErrorVO.setErrorPosition("第" + index + "行，第11列");
			//// importErrorVO.setErrorMsg("户室不可为空");
			//// populationImportErrorVOList.add(importErrorVO);
			// index++;
			// ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			// setMistake(oneData, mistake);
			// mistake.setMistake("户室不可为空");
			// mistakes.add(mistake);
			// continue;
			// }
			if(StringUtils.isNotEmpty(oneData.get(10))) {
			vo.setHouseNo(oneData.get(10).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(11))){
			vo.setBuildPurpose(oneData.get(11).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(12))){
			vo.setBuildArea(oneData.get(12).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(13))){
			Integer isOk = PopulHouseStatusEnum.getCodeByName(oneData.get(13).trim());
			if(isOk.equals(-1)){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第14列");
			// importErrorVO.setErrorMsg("您填写的房屋状态有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的房屋状态有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setHouseStatus(isOk);
			}
			if(StringUtils.isNotEmpty(oneData.get(14))){
			Integer isOk = PopulHousePurposeEnum.getCodeByName(oneData.get(14).trim());
			if(isOk.equals(-1)){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第15列");
			// importErrorVO.setErrorMsg("您填写的房屋用途有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的房屋用途有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setHousePurpose(PopulHousePurposeEnum.getCodeByName(oneData.get(14).trim()));
			}
			if(StringUtils.isNotEmpty(oneData.get(15))){
			Integer isOk = PopulHouseControlStatusEnum.getCodeByName(oneData.get(15));
			if(isOk.equals(-1)){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第16列");
			// importErrorVO.setErrorMsg("您填写的管控状态有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的管控状态有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setControlStatus(isOk);
			}

			if(StringUtils.isNotEmpty(oneData.get(16))){
			vo.setPhone(oneData.get(16).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(17))){
			vo.setNativePlace(oneData.get(17).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(18))){
			vo.setCultureLevel(PopulCultureLevelEnum.getCodeByName(oneData.get(18)));
			Integer isOk = PopulCultureLevelEnum.getCodeByName(oneData.get(18));
			if(isOk.equals(-1)){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第19列");
			// importErrorVO.setErrorMsg("您填写的文化程度有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的文化程度有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setCultureLevel(isOk);
			}
			if(StringUtils.isNotEmpty(oneData.get(19))){
			// String ma = convertMarriage(oneData.get(19));
			Integer isOk = PopulMarriageEnum.getCodeByName(oneData.get(19));
			if(isOk.equals(-1)){
			// vo.setMarriageStr(ma);
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第20列");
			// importErrorVO.setErrorMsg("您填写的婚姻状况有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的婚姻状况有误");
			mistakes.add(mistake);
			continue;
			}else{
			vo.setMarriage(isOk);
			}

			}
			if(StringUtils.isNotEmpty(oneData.get(20))){
			vo.setHealthy(oneData.get(20).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(21))){
			vo.setBloodType(oneData.get(21).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(22))){
			vo.setReligion(oneData.get(22).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(23))){
			vo.setProfession(oneData.get(23).trim());
			}

			if(StringUtils.isNotEmpty(oneData.get(24))){
			vo.setWorkCompany(oneData.get(24).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(25))){
			Integer isOk = PopulOutOrLocalEnum.getCodeByName(oneData.get(25));
			if(isOk.equals(-1)){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第26列");
			// importErrorVO.setErrorMsg("您填写的本地/外地有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的本地/外地有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setOutOrLocal(isOk);
			}else {
			vo.setOutOrLocal(PopulOutOrLocalEnum.getCodeByName("本地"));
			}
			if(StringUtils.isNotEmpty(oneData.get(26))){
			vo.setCensusRegister(oneData.get(26).trim());
			}
			if(StringUtils.isEmpty(oneData.get(27))){
			vo.setResidence(0);
			}else{
			vo.setResidence(PopulIsOkEnum.getCodeByName(oneData.get(27).trim()));
			}
			if(StringUtils.isNotEmpty(oneData.get(28))){
			Integer isOk = PopulPersonTypeEnum.getCodeByName(oneData.get(28));
			if(isOk.equals(-1)){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第29列");
			// importErrorVO.setErrorMsg("您填写的人员类型有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的人员类型有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setPersonType(isOk);
			}
			if(StringUtils.isNotEmpty(oneData.get(29))){
			vo.setCountry(oneData.get(29).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(30))){
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYYY-dd-MM HH:mm:ss");
			vo.setDateOfDeparture(simpleDateFormat.parse(oneData.get(30)));
			}
			if(StringUtils.isNotEmpty(oneData.get(31))){
			vo.setPersonStatus(oneData.get(31).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(32))){
			vo.setMonthlyIncome(oneData.get(32).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(33))){
			vo.setFamilyStatus(oneData.get(33).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(34))){
			vo.setGoalInChina(oneData.get(34).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(35))){
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYYY-dd-MM HH:mm:ss");
			vo.setDateOfArrival(simpleDateFormat.parse(oneData.get(35)));
			}
			if(StringUtils.isNotEmpty(oneData.get(36))){
			vo.setRemark(oneData.get(36).trim());
			}

			if(StringUtils.isNotEmpty(oneData.get(37))){
			vo.setIdCardPositive(oneData.get(37).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(38))){
			vo.setIdCardBack(oneData.get(38).trim());
			}
			if(StringUtils.isNotEmpty(oneData.get(39))){
			vo.setHouseHold(oneData.get(39).trim());
			}
			if(StringUtils.isEmpty(oneData.get(40))){
			vo.setDeath(0);
			}else{
			vo.setDeath(PopulIsOkEnum.getCodeByName(oneData.get(40).trim()));
			}
			for (int i = 41; i < headSize; i++) {
			if (oneData.get(i) != null && oneData.get(i).equals("是")) {
			vo.getUserTagStr().add(headData.get(i).substring(0,headData.get(i).indexOf("(")));
			}
			}

			//将重复的数据进行MD5加密实现去重
			String distinct = vo.getName() + vo.getCardNo() + vo.getRoad() + vo.getDoorNo() + vo.getFloor() + vo.getUnitNo() + vo.getHouseNo();
			try {
			String distinctPass = PayUtil.MD5(distinct);
			if(StringUtils.isNotEmpty(distinctPass)){
			vo.setDistinctPass(distinctPass);
			}
			}catch (Exception e){
			log.error("组装MD5加密字段失败，数据表格行数：" + index);
			// 判断身份证号码位数
			if (oneData.get(1).length() != 18) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第2列"+oneData.get(1));
			// importErrorVO.setErrorMsg("身份证号位数有误，请检查身份证号码是否正确");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("身份证号位数有误，请检查身份证号码是否正确");
			mistakes.add(mistake);
			continue;
			}
			voList.add(vo);
			vo.setCardNo(oneData.get(1).toUpperCase());
			// 根据身份证号码解析年龄以及性别
			// 获取用户生日
			String birthday = vo.getCardNo().substring(6, 14);
			if (StringUtils.isNotEmpty(birthday)) {
			String year = birthday.substring(0, 4);
			String month = birthday.substring(4, 6);
			String day = birthday.substring(6, 8);
			vo.setBirthday(year + "-" + month + "-" + day);
			}
			// //设置用户年龄
			// vo.setAge(AgeUtils.getAgeFromBirthTime(birthday));
			// 获取用户性别
			int sex = Integer.parseInt(vo.getCardNo().substring(16, 17));
			if (sex % 2 == 1) {
			vo.setSex(PopulSexEnum.nan.getCode());
			} else {
			vo.setSex(PopulSexEnum.nv.getCode());
			}
			}
			if (StringUtils.isNotEmpty(oneData.get(2))) {
			// String nation = oneData.get(2);
			// if(!nation.contains("族")){
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第3列");
			// importErrorVO.setErrorMsg("您填写的民族格式有误");
			// populationImportErrorVOList.add(importErrorVO);
			// index++;
			// continue;
			// }
			vo.setNation(oneData.get(2));
			}
			if (StringUtils.isNotEmpty(oneData.get(3))) {
			vo.setPoliticalOutlook(PopulPoliticalOutlookEnum.getCodeByName(oneData.get(3)));
			Integer isOk = PopulPoliticalOutlookEnum.getCodeByName(oneData.get(3));
			if (isOk.equals(-1)) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第4列");
			// importErrorVO.setErrorMsg("您填写的政治面貌有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的政治面貌有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setPoliticalOutlook(isOk);
			}
			if (StringUtils.isNotEmpty(oneData.get(4))) {
			Integer isOk = PopulHouseUseEnum.getCodeByName(oneData.get(4));
			if (isOk.equals(-1)) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第5列");
			// importErrorVO.setErrorMsg("您填写的是否租住有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的是否租住有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setIsRent(isOk);
			} else {
			vo.setIsRent(PopulHouseUseEnum.getCodeByName("否"));
			}
			if (StringUtils.isNotEmpty(oneData.get(5))) {

			// String ra = convertRelation(oneData.get(5));
			//
			// String relation = dictMap.get(ra);
			// if(StringUtils.isEmpty(relation)){
			//// vo.setRelationStr(ra);
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第6列");
			// importErrorVO.setErrorMsg("您填写的与户主关系有误");
			// populationImportErrorVOList.add(importErrorVO);
			// index++;
			// continue;
			// }else{
			// vo.setRelation(Integer.valueOf(relation));
			// }
			// String relation = convertMarriage(oneData.get(5));
			Integer isOk = PopulRelationEnum.getCodeByName(oneData.get(5));

			if (isOk.equals(-1)) {
			// vo.setMarriageStr(ma);
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第6列");
			// importErrorVO.setErrorMsg("您填写的与户主关系有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的与户主关系有误");
			mistakes.add(mistake);
			continue;
			} else {
			vo.setRelation(isOk);
			}
			}
			if (StringUtils.isEmpty(oneData.get(6))) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第7列");
			// importErrorVO.setErrorMsg("街路巷不可为空");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("街路巷不可为空");
			mistakes.add(mistake);
			continue;
			}
			//客户需要暂时注释，等客户处理完成需要恢复
			// if(populationImportErrorVOList.isEmpty()){
			// R r = communityService.listSavePopulationServeExcelVO(voList, communityId);
			// if (!R.isOk(r)) {
			// throw new ServiceException(r.getMsg());
			// }
			// }else{
			// throw new ServiceException("500", JSON.toJSONString(populationImportErrorVOList));
			// }
			//根据list中的IdCard城市来去重
			List<ComMngPopulationServeExcelVO> newVoList = voList.stream().filter(ListUtils.distinctByKey(ComMngPopulationServeExcelVO::getDistinctPass)).collect(Collectors.toList());
			log.info("开始进入业务层处理逻辑");
			R r = communityService.listSavePopulationServeExcelVO(newVoList, communityId);
			log.info("业务层处理逻辑完成");
			if (!R.isOk(r)) {
			log.info("业务层处理成功");
			List<ComMngPopulationMistakeExcelVO> list = JSONArray.parseArray(JSONArray.toJSONString(r.getData()), ComMngPopulationMistakeExcelVO.class);
			// String errMsg = r.getMsg();
			// List<ComMngPopulationImportErrorVO> errorList = JSON.parseArray(errMsg,ComMngPopulationImportErrorVO.class);
			// if(!errorList.isEmpty()){
			// populationImportErrorVOList.addAll(errorList);
			// }
			mistakes.addAll(list);
			log.info("将错误数据存入redis中");
			valueOperations.set(key,JSONArray.toJSONString(mistakes),1, TimeUnit.HOURS);
			log.info("将错误数据存入redis中成功");
			vo.setRoad(oneData.get(6));
			if (StringUtils.isEmpty(oneData.get(7))) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第8列");
			// importErrorVO.setErrorMsg("小区号不可为空");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("小区号不可为空");
			mistakes.add(mistake);
			continue;
			}
			vo.setDoorNo(oneData.get(7).trim());
			// if(StringUtils.isEmpty(oneData.get(8))){
			//// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			//// importErrorVO.setErrorPosition("第" + index + "行，第9列");
			//// importErrorVO.setErrorMsg("楼排号不可为空");
			//// populationImportErrorVOList.add(importErrorVO);
			// index++;
			// ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			// setMistake(oneData, mistake);
			// mistake.setMistake("楼排号不可为空");
			// mistakes.add(mistake);
			// continue;
			// }
			if (StringUtils.isNotEmpty(oneData.get(8))) {
			vo.setFloor(oneData.get(8).trim());
			}
			// if(StringUtils.isEmpty(oneData.get(9))){
			//// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			//// importErrorVO.setErrorPosition("第" + index + "行，第10列");
			//// importErrorVO.setErrorMsg("单元号不可为空");
			//// populationImportErrorVOList.add(importErrorVO);
			// index++;
			// ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			// setMistake(oneData, mistake);
			// mistake.setMistake("单元号不可为空");
			// mistakes.add(mistake);
			// continue;
			// }
			if (StringUtils.isNotEmpty(oneData.get(9))) {
			vo.setUnitNo(oneData.get(9).trim());
			}
			// if(StringUtils.isEmpty(oneData.get(10))){
			//// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			//// importErrorVO.setErrorPosition("第" + index + "行，第11列");
			//// importErrorVO.setErrorMsg("户室不可为空");
			//// populationImportErrorVOList.add(importErrorVO);
			// index++;
			// ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			// setMistake(oneData, mistake);
			// mistake.setMistake("户室不可为空");
			// mistakes.add(mistake);
			// continue;
			// }
			if (StringUtils.isNotEmpty(oneData.get(10))) {
			vo.setHouseNo(oneData.get(10).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(11))) {
			vo.setBuildPurpose(oneData.get(11).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(12))) {
			vo.setBuildArea(oneData.get(12).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(13))) {
			Integer isOk = PopulHouseStatusEnum.getCodeByName(oneData.get(13).trim());
			if (isOk.equals(-1)) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第14列");
			// importErrorVO.setErrorMsg("您填写的房屋状态有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的房屋状态有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setHouseStatus(isOk);
			}
			if (StringUtils.isNotEmpty(oneData.get(14))) {
			Integer isOk = PopulHousePurposeEnum.getCodeByName(oneData.get(14).trim());
			if (isOk.equals(-1)) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第15列");
			// importErrorVO.setErrorMsg("您填写的房屋用途有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的房屋用途有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setHousePurpose(PopulHousePurposeEnum.getCodeByName(oneData.get(14).trim()));
			}
			if (StringUtils.isNotEmpty(oneData.get(15))) {
			Integer isOk = PopulHouseControlStatusEnum.getCodeByName(oneData.get(15));
			if (isOk.equals(-1)) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第16列");
			// importErrorVO.setErrorMsg("您填写的管控状态有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的管控状态有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setControlStatus(isOk);
			}

			if (StringUtils.isNotEmpty(oneData.get(16))) {
			vo.setPhone(oneData.get(16).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(17))) {
			vo.setNativePlace(oneData.get(17).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(18))) {
			vo.setCultureLevel(PopulCultureLevelEnum.getCodeByName(oneData.get(18)));
			Integer isOk = PopulCultureLevelEnum.getCodeByName(oneData.get(18));
			if (isOk.equals(-1)) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第19列");
			// importErrorVO.setErrorMsg("您填写的文化程度有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的文化程度有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setCultureLevel(isOk);
			}
			if (StringUtils.isNotEmpty(oneData.get(19))) {
			// String ma = convertMarriage(oneData.get(19));
			Integer isOk = PopulMarriageEnum.getCodeByName(oneData.get(19));
			if (isOk.equals(-1)) {
			// vo.setMarriageStr(ma);
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第20列");
			// importErrorVO.setErrorMsg("您填写的婚姻状况有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的婚姻状况有误");
			mistakes.add(mistake);
			continue;
			} else {
			vo.setMarriage(isOk);
			}

			}
			if (StringUtils.isNotEmpty(oneData.get(20))) {
			vo.setHealthy(oneData.get(20).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(21))) {
			vo.setBloodType(oneData.get(21).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(22))) {
			vo.setReligion(oneData.get(22).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(23))) {
			vo.setProfession(oneData.get(23).trim());
			}

			if (StringUtils.isNotEmpty(oneData.get(24))) {
			vo.setWorkCompany(oneData.get(24).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(25))) {
			Integer isOk = PopulOutOrLocalEnum.getCodeByName(oneData.get(25));
			if (isOk.equals(-1)) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第26列");
			// importErrorVO.setErrorMsg("您填写的本地/外地有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的本地/外地有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setOutOrLocal(isOk);
			} else {
			vo.setOutOrLocal(PopulOutOrLocalEnum.getCodeByName("本地"));
			}
			if (StringUtils.isNotEmpty(oneData.get(26))) {
			vo.setCensusRegister(oneData.get(26).trim());
			}
			if (StringUtils.isEmpty(oneData.get(27))) {
			vo.setResidence(0);
			} else {
			vo.setResidence(PopulIsOkEnum.getCodeByName(oneData.get(27).trim()));
			}
			if (StringUtils.isNotEmpty(oneData.get(28))) {
			Integer isOk = PopulPersonTypeEnum.getCodeByName(oneData.get(28));
			if (isOk.equals(-1)) {
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行，第29列");
			// importErrorVO.setErrorMsg("您填写的人员类型有误");
			// populationImportErrorVOList.add(importErrorVO);
			index++;
			ComMngPopulationMistakeExcelVO mistake = new ComMngPopulationMistakeExcelVO();
			setMistake(oneData, mistake);
			mistake.setMistake("您填写的人员类型有误");
			mistakes.add(mistake);
			continue;
			}
			vo.setPersonType(isOk);
			}
			if (StringUtils.isNotEmpty(oneData.get(29))) {
			vo.setCountry(oneData.get(29).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(30))) {
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYYY-dd-MM HH:mm:ss");
			vo.setDateOfDeparture(simpleDateFormat.parse(oneData.get(30)));
			}
			if (StringUtils.isNotEmpty(oneData.get(31))) {
			vo.setPersonStatus(oneData.get(31).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(32))) {
			vo.setMonthlyIncome(oneData.get(32).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(33))) {
			vo.setFamilyStatus(oneData.get(33).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(34))) {
			vo.setGoalInChina(oneData.get(34).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(35))) {
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYYY-dd-MM HH:mm:ss");
			vo.setDateOfArrival(simpleDateFormat.parse(oneData.get(35)));
			}
			if (StringUtils.isNotEmpty(oneData.get(36))) {
			vo.setRemark(oneData.get(36).trim());
			}

			if (StringUtils.isNotEmpty(oneData.get(37))) {
			vo.setIdCardPositive(oneData.get(37).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(38))) {
			vo.setIdCardBack(oneData.get(38).trim());
			}
			if (StringUtils.isNotEmpty(oneData.get(39))) {
			vo.setHouseHold(oneData.get(39).trim());
			}
			if (StringUtils.isEmpty(oneData.get(40))) {
			vo.setDeath(0);
			} else {
			vo.setDeath(PopulIsOkEnum.getCodeByName(oneData.get(40).trim()));
			}
			for (int i = 41; i < headSize; i++) {
			if (oneData.get(i) != null && oneData.get(i).equals("是")) {
			vo.getUserTagStr().add(headData.get(i).substring(0, headData.get(i).indexOf("(")));
			}
			}

			// 将重复的数据进行MD5加密实现去重
			String distinct = vo.getName() + vo.getCardNo() + vo.getRoad() + vo.getDoorNo() + vo.getFloor()
			+ vo.getUnitNo() + vo.getHouseNo();
			try {
			String distinctPass = PayUtil.MD5(distinct);
			if (StringUtils.isNotEmpty(distinctPass)) {
			vo.setDistinctPass(distinctPass);
			}
			} catch (Exception e) {
			log.error("组装MD5加密字段失败，数据表格行数：" + index);
			continue;
			}
			voList.add(vo);
			index++;
			}
			// 客户需要暂时注释，等客户处理完成需要恢复
			// if(populationImportErrorVOList.isEmpty()){
			// R r = communityService.listSavePopulationServeExcelVO(voList, communityId);
			// if (!R.isOk(r)) {
			// throw new ServiceException(r.getMsg());
			// }
			// }else{
			// throw new ServiceException("500", JSON.toJSONString(populationImportErrorVOList));
			// }
			// 根据list中的IdCard城市来去重
			List<ComMngPopulationServeExcelVO> newVoList =
			voList.stream().filter(ListUtils.distinctByKey(ComMngPopulationServeExcelVO::getDistinctPass))
			.collect(Collectors.toList());
			log.info("开始进入业务层处理逻辑");
			R r = communityService.listSavePopulationServeExcelVO(newVoList, communityId, userId);
			log.info("业务层处理逻辑完成");
			if (!R.isOk(r)) {
			log.info("业务层处理成功");
			List<ComMngPopulationMistakeExcelVO> list =
			JSONArray.parseArray(JSONArray.toJSONString(r.getData()), ComMngPopulationMistakeExcelVO.class);
			// String errMsg = r.getMsg();
			// List<ComMngPopulationImportErrorVO> errorList =
			// JSON.parseArray(errMsg,ComMngPopulationImportErrorVO.class);
			// if(!errorList.isEmpty()){
			// populationImportErrorVOList.addAll(errorList);
			// }
			mistakes.addAll(list);
			log.info("将错误数据存入redis中");
			valueOperations.set(key, JSONArray.toJSONString(mistakes), 1, TimeUnit.HOURS);
			log.info("将错误数据存入redis中成功");
			throw new ServiceException("500", key);
			} else {
			log.info("业务层处理逻辑失败");
			if (!mistakes.isEmpty()) {
			log.info("业务层处理逻辑失败，将错误数据缓存到redis中");
			valueOperations.set(key, JSONArray.toJSONString(mistakes), 1, TimeUnit.HOURS);
			log.info("业务层处理逻辑失败，将错误数据缓存到redis中成功");
			throw new ServiceException("500", key);
			}else{
			log.info("业务层处理逻辑失败");
			if(!mistakes.isEmpty()){
			log.info("业务层处理逻辑失败，将错误数据缓存到redis中");
			valueOperations.set(key,JSONArray.toJSONString(mistakes),1, TimeUnit.HOURS);
			log.info("业务层处理逻辑失败，将错误数据缓存到redis中成功");
			throw new ServiceException("500", key);
			}
			}
			} catch (NumberFormatException e) {
			log.info("处理数据时失败");
			e.printStackTrace();
			// List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>();
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行");
			// importErrorVO.setErrorMsg("数据格式有误，请检查文档内数据");
			// populationImportErrorVOList.add(importErrorVO);
			log.error("数据格式有误，第" + index + "行");
			throw new ServiceException("500", "导入失败111");
			} catch (ParseException e1) {
			log.info("处理数据时失败2");
			e1.printStackTrace();
			// List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>();
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行");
			// importErrorVO.setErrorMsg("数据格式有误，请检查文档内数据");
			// populationImportErrorVOList.add(importErrorVO);
			log.error("数据格式有误，第" + index + "行");
			throw new ServiceException("500", "导入失败222");
			}
			} catch (NumberFormatException e) {
			log.info("处理数据时失败");
			e.printStackTrace();
			// List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>();
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行");
			// importErrorVO.setErrorMsg("数据格式有误，请检查文档内数据");
			// populationImportErrorVOList.add(importErrorVO);
			log.error("数据格式有误，第" + index + "行");
			throw new ServiceException("500", "导入失败111");
			} catch (ParseException e1) {
			log.info("处理数据时失败2");
			e1.printStackTrace();
			// List<ComMngPopulationImportErrorVO> populationImportErrorVOList = new ArrayList<>();
			// ComMngPopulationImportErrorVO importErrorVO = new ComMngPopulationImportErrorVO();
			// importErrorVO.setErrorPosition("第" + index + "行");
			// importErrorVO.setErrorMsg("数据格式有误，请检查文档内数据");
			// populationImportErrorVOList.add(importErrorVO);
			log.error("数据格式有误，第" + index + "行");
			throw new ServiceException("500", "导入失败222");
			}
			}

			private void setMistake(Map<Integer, String> map, ComMngPopulationMistakeExcelVO vo) {
			vo.setName(map.get(0));
			vo.setCardNo(map.get(1));
			vo.setNation(map.get(2));
			vo.setPoliticalOutlook(map.get(3));
			vo.setIsRent(map.get(4));
			vo.setRelation(map.get(5));
			vo.setRoad(map.get(6));
			vo.setDoorNo(map.get(7));
			vo.setFloor(map.get(8));
			vo.setUnitNo(map.get(9));
			vo.setHouseNo(map.get(10));
			vo.setBuildPurpose(map.get(11));
			vo.setBuildArea(map.get(12));
			vo.setHouseStatus(map.get(13));
			vo.setHousePurpose(map.get(14));
			vo.setControlStatus(map.get(15));
			vo.setPhone(map.get(16));
			vo.setNativePlace(map.get(17));
			vo.setCultureLevel(map.get(18));
			vo.setMarriage(map.get(19));
			vo.setHealthy(map.get(20));
			vo.setBloodType(map.get(21));
			vo.setReligion(map.get(22));
			vo.setProfession(map.get(23));
			vo.setWorkCompany(map.get(24));
			vo.setOutOrLocal(map.get(25));
			vo.setCensusRegister(map.get(26));
			vo.setResidence(map.get(27));
			// vo.setAddress(map.get(28));
			vo.setPersonType(map.get(28));
			vo.setCountry(map.get(29));
			vo.setStringOfDeparture(map.get(30));
			vo.setPersonStatus(map.get(31));
			vo.setMonthlyIncome(map.get(32));
			vo.setFamilyStatus(map.get(33));
			vo.setGoalInChina(map.get(34));
			vo.setStringOfArrival(map.get(35));
			vo.setRemark(map.get(36));
			vo.setIdCardPositive(map.get(37));
			vo.setIdCardBack(map.get(38));
			vo.setHouseHold(map.get(39));
			vo.setDeath(map.get(40));
			}

			private String convertMarriage(String ma) {

			if (Objects.equals("已", ma) \|\| Objects.equals("一", ma)) {
			ma = "已婚";
			} else if (Objects.equals("未", ma) \|\| Objects.equals("未婚婚", ma)) {
			ma = "未婚";
			} else if (Objects.equals("初", ma)) {
			ma = "初婚";
			} else if (Objects.equals("再", ma)) {
			ma = "再婚";
			} else if (Objects.equals("复", ma)) {
			ma = "复婚";
			} else if (Objects.equals("丧", ma) \|\| Objects.equals("丧偶偶", ma)) {
			ma = "丧偶";
			} else if (Objects.equals("离", ma) \|\| Objects.equals("离异", ma) \|\| Objects.equals("离异婚", ma)) {
			ma = "离婚";
			} else if (Objects.equals("分", ma)) {
			ma = "分居";
			}

			private void setMistake(Map<Integer, String> map, ComMngPopulationMistakeExcelVO vo){
			vo.setName(map.get(0));
			vo.setCardNo(map.get(1));
			vo.setNation(map.get(2));
			vo.setPoliticalOutlook(map.get(3));
			vo.setIsRent(map.get(4));
			vo.setRelation(map.get(5));
			vo.setRoad(map.get(6));
			vo.setDoorNo(map.get(7));
			vo.setFloor(map.get(8));
			vo.setUnitNo(map.get(9));
			vo.setHouseNo(map.get(10));
			vo.setBuildPurpose(map.get(11));
			vo.setBuildArea(map.get(12));
			vo.setHouseStatus(map.get(13));
			vo.setHousePurpose(map.get(14));
			vo.setControlStatus(map.get(15));
			vo.setPhone(map.get(16));
			vo.setNativePlace(map.get(17));
			vo.setCultureLevel(map.get(18));
			vo.setMarriage(map.get(19));
			vo.setHealthy(map.get(20));
			vo.setBloodType(map.get(21));
			vo.setReligion(map.get(22));
			vo.setProfession(map.get(23));
			vo.setWorkCompany(map.get(24));
			vo.setOutOrLocal(map.get(25));
			vo.setCensusRegister(map.get(26));
			vo.setResidence(map.get(27));
			// vo.setAddress(map.get(28));
			vo.setPersonType(map.get(28));
			vo.setCountry(map.get(29));
			vo.setStringOfDeparture(map.get(30));
			vo.setPersonStatus(map.get(31));
			vo.setMonthlyIncome(map.get(32));
			vo.setFamilyStatus(map.get(33));
			vo.setGoalInChina(map.get(34));
			vo.setStringOfArrival(map.get(35));
			vo.setRemark(map.get(36));
			vo.setIdCardPositive(map.get(37));
			vo.setIdCardBack(map.get(38));
			vo.setHouseHold(map.get(39));
			vo.setDeath(map.get(40));
			return ma;
			}

			private String convertRelation(String ra) {
			if (ra.contains("户主")) {
			ra = "户主";
			} else if (Objects.equals(ra, "女儿")) {
			ra = "女";
			} else if (Objects.equals(ra, "儿子") \|\| Objects.equals(ra, "儿")) {
			ra = "子";
			} else if (Objects.equals(ra, "大女")) {
			ra = "长女";
			} else if (Objects.equals(ra, "二女") \|\| Objects.equals(ra, "此女")) {
			ra = "次女";
			} else if (Objects.equals(ra, "二子")) {
			ra = "次子";
			} else if (Objects.equals(ra, "长男") \|\| ra.contains("长子")) {
			ra = "长子";
			} else if (Objects.equals(ra, "非亲属") \|\| ra.contains("女友") \|\| ra.contains("女朋友") \|\| ra.contains("男友")
			\|\| ra.contains("男朋友")) {
			ra = "其他";
			} else if (Objects.equals(ra, "姐")) {
			ra = "姐姐";
			} else if (Objects.equals(ra, "户主")) {
			ra = "本人";
			} else if (Objects.equals(ra, "妻子") \|\| Objects.equals(ra, "媳妇")) {
			ra = "妻";
			} else if (Objects.equals(ra, "哥") \|\| Objects.equals(ra, "哥哥")) {
			ra = "兄";
			} else if (Objects.equals(ra, "丈夫")) {
			ra = "夫";
			} else if (Objects.equals(ra, "丈母娘")) {
			ra = "岳母";
			} else if (Objects.equals(ra, "继子") \|\| Objects.equals(ra, "养子")) {
			ra = "养子或继子";
			} else if (Objects.equals(ra, "继女") \|\| Objects.equals(ra, "养女")) {
			ra = "养女或继女";
			} else if (ra.contains("儿媳") \|\| ra.contains("长媳")) {
			ra = "儿媳";
			} else if (Objects.equals(ra, "三姨") \|\| Objects.equals(ra, "姨姨")) {
			ra = "姨母";
			} else if (Objects.equals(ra, "二外孙女")) {
			ra = "外孙女";
			} else if (Objects.equals(ra, "外孙")) {
			ra = "外孙子";
			} else if (Objects.equals(ra, "侄儿")) {
			ra = "侄子";
			} else if (ra.contains("表")) {
			ra = "表兄弟、表姐妹";
			} else if (Objects.equals(ra, "继父") \|\| Objects.equals(ra, "养父")) {
			ra = "继父或养父";
			} else if (Objects.equals(ra, "父")) {
			ra = "父亲";
			} else if (Objects.equals(ra, "弟弟")) {
			ra = "弟";
			} else if (Objects.equals(ra, "孙")) {
			ra = "孙子";
			} else if (Objects.equals(ra, "孙媳妇") \|\| Objects.equals(ra, "外孙媳妇")) {
			ra = "孙媳妇或外孙媳妇";
			} else if (Objects.equals(ra, "妹")) {
			ra = "妹妹";
			} else if (Objects.equals(ra, "祖父母")) {
			ra = "祖父母或外祖父母";
			} else if (Objects.equals(ra, "姐妹") \|\| Objects.equals(ra, "兄弟")) {
			ra = "兄弟姐妹";
			} else if (Objects.equals(ra, "其他姐妹") \|\| Objects.equals(ra, "其他兄弟")) {
			ra = "其他兄弟姐妹";
			} else if (Objects.equals(ra, "奶奶")) {
			ra = "祖母";
			} else if (Objects.equals(ra, "爷爷")) {
			ra = "祖父";
			} else if (Objects.equals(ra, "爷爷")) {
			ra = "祖父";
			} else if (ra.contains("堂")) {
			ra = "堂兄弟、堂姐妹";
			}

			private R mistakeExportPopulation(List<ComMngPopulationMistakeExcelVO> vo) {
			//生成动态模板excel通过ftp工具上传到主节点，然后返回模板下载地址
			String ftpUrl = "/mnt/data/web/excel/";
			String name = "实有人口错误数据.xlsx";
			try {
			SFTPUtil sftp = new SFTPUtil(userName, password, host, port);
			sftp.login();
			boolean existDir = sftp.isExistDir(ftpUrl + name);
			if (!existDir) {
			String property = System.getProperty("user.dir");
			String fileName = property + File.separator + name;
			// 这里需要指定写用哪个class去写
			ExcelWriter excelWriter = null;
			InputStream inputStream = null;
			try {
			excelWriter = EasyExcel.write(fileName, ComMngPopulationMistakeExcelVO.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomSheetWriteHandler()).build();
			WriteSheet writeSheet = EasyExcel.writerSheet("实有人口错误数据").build();
			excelWriter.write(vo, writeSheet);
			excelWriter.finish();
			File file = new File(fileName);
			inputStream = new FileInputStream(file);
			sftp.uploadMore(ftpUrl, name, inputStream);
			sftp.logout();
			inputStream.close();
			String absolutePath = file.getAbsolutePath();
			boolean delete = file.delete();
			log.info("删除excel【{}】结果【{}】", absolutePath, delete);
			} finally {
			// 千万别忘记finish 会帮忙关闭流
			if (inputStream != null) {
			inputStream.close();
			}
			if (excelWriter != null) {
			excelWriter.finish();
			}
			}
			}
			return R.ok(excelUrl + name);
			} catch (Exception e) {
			e.printStackTrace();
			log.error("文件传输失败【{}】", e.getMessage());
			return R.fail();
			}
			}

			private String convertMarriage(String ma) {

			if(Objects.equals("已",ma) \|\| Objects.equals("一",ma)){
			ma = "已婚";
			}else if(Objects.equals("未",ma) \|\| Objects.equals("未婚婚",ma)){
			ma = "未婚";
			}else if(Objects.equals("初",ma)){
			ma = "初婚";
			}else if(Objects.equals("再",ma)){
			ma = "再婚";
			}else if(Objects.equals("复",ma)){
			ma = "复婚";
			}else if(Objects.equals("丧",ma) \|\| Objects.equals("丧偶偶",ma)){
			ma = "丧偶";
			}else if(Objects.equals("离",ma) \|\| Objects.equals("离异",ma) \|\| Objects.equals("离异婚",ma)){
			ma = "离婚";
			}else if(Objects.equals("分",ma)){
			ma = "分居";
			}

			return ma;
			}

			private String convertRelation(String ra) {
			if(ra.contains("户主")){
			ra = "户主";
			}else if(Objects.equals(ra,"女儿")){
			ra = "女";
			}else if(Objects.equals(ra,"儿子") \|\| Objects.equals(ra,"儿")){
			ra = "子";
			}else if(Objects.equals(ra,"大女")){
			ra = "长女";
			}else if(Objects.equals(ra,"二女") \|\| Objects.equals(ra,"此女")){
			ra = "次女";
			}else if(Objects.equals(ra,"二子")){
			ra = "次子";
			}else if(Objects.equals(ra,"长男") \|\| ra.contains("长子")){
			ra = "长子";
			}else if(Objects.equals(ra,"非亲属") \|\| ra.contains("女友") \|\| ra.contains("女朋友")
			\|\| ra.contains("男友") \|\| ra.contains("男朋友")){
			ra = "其他";
			}else if(Objects.equals(ra,"姐")){
			ra = "姐姐";
			}else if(Objects.equals(ra,"户主")){
			ra = "本人";
			}else if(Objects.equals(ra,"妻子") \|\| Objects.equals(ra,"媳妇")){
			ra = "妻";
			}else if(Objects.equals(ra,"哥") \|\| Objects.equals(ra,"哥哥")){
			ra = "兄";
			}else if(Objects.equals(ra,"丈夫")){
			ra = "夫";
			}else if(Objects.equals(ra,"丈母娘")){
			ra = "岳母";
			}else if(Objects.equals(ra,"继子") \|\| Objects.equals(ra,"养子")){
			ra = "养子或继子";
			}else if(Objects.equals(ra,"继女") \|\| Objects.equals(ra,"养女")){
			ra = "养女或继女";
			}else if(ra.contains("儿媳")\|\|ra.contains("长媳")){
			ra = "儿媳";
			}else if(Objects.equals(ra,"三姨") \|\| Objects.equals(ra,"姨姨")){
			ra = "姨母";
			}else if(Objects.equals(ra,"二外孙女")){
			ra = "外孙女";
			}else if(Objects.equals(ra,"外孙")){
			ra = "外孙子";
			}else if(Objects.equals(ra,"侄儿")){
			ra = "侄子";
			}else if(ra.contains("表")){
			ra = "表兄弟、表姐妹";
			}else if(Objects.equals(ra,"继父") \|\| Objects.equals(ra,"养父")){
			ra = "继父或养父";
			}else if(Objects.equals(ra,"父")){
			ra = "父亲";
			}else if(Objects.equals(ra,"弟弟")){
			ra = "弟";
			}else if(Objects.equals(ra,"孙")){
			ra = "孙子";
			}else if(Objects.equals(ra,"孙媳妇") \|\| Objects.equals(ra,"外孙媳妇")){
			ra = "孙媳妇或外孙媳妇";
			}else if(Objects.equals(ra,"妹")){
			ra = "妹妹";
			}else if(Objects.equals(ra,"祖父母")){
			ra = "祖父母或外祖父母";
			}else if(Objects.equals(ra,"姐妹") \|\| Objects.equals(ra,"兄弟")){
			ra = "兄弟姐妹";
			}else if(Objects.equals(ra,"其他姐妹") \|\| Objects.equals(ra,"其他兄弟")){
			ra = "其他兄弟姐妹";
			}else if(Objects.equals(ra,"奶奶")){
			ra = "祖母";
			}else if(Objects.equals(ra,"爷爷")){
			ra = "祖父";
			}else if(Objects.equals(ra,"爷爷")){
			ra = "祖父";
			}else if(ra.contains("堂")){
			ra = "堂兄弟、堂姐妹";
			}

			return ra;
			}
			return ra;
			}
			}