导入表格,依据某一列特定的值 解析是否需要的内容,不是则返回错误信息
如果数据正确返回 一个 List<Map<String, String>>
public static List<?> parsePersonExecl(File execlFile,String streetname){
InputStream in = null;Workbook book = null;
List<Map<String, String>> returnList = new ArrayList<>();
List<String> errorList = new ArrayList<>();
try {
in = new FileInputStream(execlFile);
book = WorkbookFactory.create(in); -------------------------可以解析xls ,xlsx
// book = createWorkBook(in,execlFile.getName());
Sheet sheet = book.getSheetAt(0);
if(0==sheet.getLastRowNum()){
return null;
}
Map<String, String> personMap = null;
Row row = null;
String cellValue ;
for(int i=2;i<=sheet.getLastRowNum();i++){
personMap = new HashMap<>();
row = sheet.getRow(i);
if(null==row){
errorList.add("表格第"+(i+1)+"行第2列值不可为空!");
}else{
cellValue = getCellValue(row.getCell(1)).trim();
if(cellValue.lastIndexOf("街道")!=-1&&cellValue.length()>0){
if(cellValue.equals(streetname)||cellValue==streetname){
cellValue = getCellValue(row.getCell(4)).trim();
if(StringUtils.isBlank(cellValue)){
errorList.add("表格第"+(i+1)+"行第5列值不可为空!");
}else{
boolean flag = false;
String newIdCard = "";
if(cellValue.length()>=15){
if(cellValue.length()==15){
flag = IdcardValidatorUtils.isValidatedAllIdcard(cellValue);
if(flag){
newIdCard = IdcardValidatorUtils.convertIdcarBy15bit(cellValue);
cellValue = newIdCard;
}else{
errorList.add("表格第"+(i+1)+"行第5列值居民身份证号信息有误!");
}
}else if(cellValue.length()==18){
flag = IdcardValidatorUtils.isValidatedAllIdcard(cellValue);
if(!flag){
errorList.add("表格第"+(i+1)+"行第5列值居民身份证号信息有误!");
}
}else{
errorList.add("表格第"+(i+1)+"行第5列值居民身份证号信息有误!");
}
personMap.put("idCard", cellValue);
returnList.add(personMap);
cellValue = null;
row = null;
personMap = null;
}else{
errorList.add("表格第"+(i+1)+"行第5列值居民身份证号信息有误!");
}
}
}else{
errorList.add("表格第"+(i+1)+"行非本街道数据,请检查数据是否正确或为空");
};
}else{
errorList.add("表格第"+(i+1)+"行非本街道数据,请检查数据是否正确或为空");
continue;
}
}
}
} catch (Exception e) {
throw new ServiceProcessException(e);
} finally{
try {
if(null != book){
book.close();
}
if(null != in){
in.close();
}
} catch (IOException e) {
throw new ServiceProcessException(e);
}
}
if(errorList.size() > 0 ){
return errorList;
}
return returnList;
}