一个上传Excel(poi)的套路

思路是这样，上传excel一般我们会定一个模板。模板第一行是表头，固定N列。

那么，我们配置一个数组来定义第个列的数据类型，生成对象是属性名，是否可以为空，那么就有了这么一个实体类:

@Data
class ExcelColConfig{
	//对象属性名
	private String codeName;
	private Class type;
	private boolean notNull;
	public ExcelColConfig(String codeName,Class type, boolean notNull) {
		super();
		this.codeName=codeName;
		this.type = type;
		this.notNull = notNull;
	}
}

当上传时，比如我有4列，那么就这么配置:

ExcelColConfig[] rowConfig=new ExcelColConfig[] {
		new ExcelColConfig("templateSign",String.class,true),
		new ExcelColConfig("paperUrl",String.class,true),
		new ExcelColConfig("paperNo",Integer.class,true),
		new ExcelColConfig("useTime",Integer.class,true),
};

抽象出一个通用方法，以上面的“元数据”和文件为输入，输出格式化的List<map>对象:

/**
 * 
 * @param excelFile springMVC转入的File对象
 * @param rowConfig 自定义配置对象
 * @return
 * @throws IOException
 */
public static List<Map<String,String>> convertEntity(MultipartFile excelFile, ExcelUtils.ExcelColConfig[] rowConfig) throws IOException{
    
}

它还用了另两个工具方法:

public static String[] getTitle(XSSFRow row,int colNum) {
    String title[]=new String[colNum];
    for(int i=0;i<colNum;i++) {
        title[i]=row.getCell(i).getStringCellValue();
    }
    return title;
}

public static Boolean isEmptyRow(XSSFRow row,int colNum) {
	if(row==null) {
		return true;
	}
	int n=0;
	for(int i=0;i<colNum;i++) {
		XSSFCell cell=row.getCell(i);
		if(cell==null||StringUtils.isBlank(cell.toString())) {
			n++;
		}
	}
	if(n==colNum) {
		return true;
	}else {
		return false;
	}
	
}

最后调用这样，一句就行了：

List<Map<String,String>> sheetList =ExcelUtils.convertEntity(excelFile, rowConfig);

本以为完事了，结果又来个需求，数据要验证...... 我想那就在一行转换完后验证吧，上面的convertEntity方法加个参数就变成了这样:

/**
 * 
 * @param excelFile springMVC转入的File对象
 * @param rowConfig 自定义配置对象
 * @param checkRow 为空不验证，不为空，调用其check方法
 * @return
 * @throws IOException
 */
public static List<Map<String,Object>> convertEntity(MultipartFile excelFile, ExcelUtils.ExcelColConfig[] rowConfig,CheckRow checkRow) throws IOException{
	List<Map<String,Object>> sheetList = new ArrayList<>();
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(excelFile.getInputStream());
	try {
    	if(xssfWorkbook.getNumberOfSheets()!=1) {
    	     throw new RuntimeException("excel文件格式不正确,sheet数需=1");
    	}



    	XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    	XSSFRow titleRow = xssfSheet.getRow(0);

    	int templateLen=rowConfig.length;
    	if(titleRow.getPhysicalNumberOfCells()<templateLen) {
    		throw new RuntimeException( "列数小于"+templateLen+",请检查文件模板是否正确");
    	}
    	String title[] = ExcelUtils.getTitle(titleRow,rowConfig.length);
    	for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
    	    XSSFRow row = xssfSheet.getRow(rowNum);
    	    
    	    int userRow=rowNum+1;
    	    if(ExcelUtils.isEmptyRow(row, rowConfig.length)) {
    	    	log.info(userRow+"行是空行，跳过");
    	    	continue;
    	    }
    	    Map<String,Object> rowObjMap=new HashMap<String,Object>();
    	    
    	    for(int i=0;i<rowConfig.length;i++) {
    	    	ExcelColConfig ec=rowConfig[i];
    	    	Object val=null;
    	    	String errorMsg=null;
    	    	try {
	    	    	if(ec.getType().equals(String.class)) {
	    	    		//row.getCell(i).toString() 可得到大部分情况
	    	    		//row.getCell(i).getRichStringCellValue() 可得到公式计算后的值
	    	    		String sval=row.getCell(i)==null?null:String.valueOf(row.getCell(i).getRichStringCellValue()).trim();
	    	    		if(!StringUtils.isBlank(sval)) {
	    	    			val=sval;
	    	    		}
	    	    	}else if(ec.getType().equals(Integer.class)) {
	    	    		Integer ival=null;
	    	            try {
	    	            	ival=row.getCell(i)==null?null:Double.valueOf(row.getCell(i).toString().trim()).intValue();
	    	            }catch(Exception e) {
	    	            	log.error(e.getMessage(),e);
	    	            	errorMsg= String.format("%s行,%s错误:"+e.getMessage(), userRow,title[i]);
	    	            }
	    	            val=ival;
	    	    	}
    	    	}catch(Exception e) {
    	    		log.error(e.getMessage(),e);
    	    		errorMsg=(i+1)+"列"+userRow+"行("+row.getCell(i).toString()+")错误,"+e.getMessage();
    	    	}
    	    	if(errorMsg!=null) {
    	    		throw new RuntimeException(errorMsg);
    	    	}
    	    	
    	    	if(ec.isNotNull()&&val==null) {
    	    		throw new RuntimeException(String.format("%s行,%s不能为空", userRow,title[i]));
    			}
    	    	rowObjMap.put(ec.getCodeName(),val);
    	    }
    	    //如果每行有验证逻辑，在这里判断
    	    if(checkRow!=null) {
    	    	checkRow.check(userRow,row, rowObjMap, title, rowConfig);
    	    }
    	    
    	    
    		sheetList.add(rowObjMap);
    	}
	}finally {
		if(xssfWorkbook!=null) {
			xssfWorkbook.close();
		}
	}
	return sheetList;
}

这里的CheckRow是个接口，定义如下:

public  interface CheckRow {
    public void check(int rowNum,XSSFRow row,Map<String,String> rowObjMap,String title[],ExcelUtils.ExcelColConfig[] rowConfig);
}

比如我，调用convertEntity之前，初始化了一下这个方法:

//回调行验证逻辑
CheckRow cr=new CheckRow() {

    @Override
    public void check(int rowNum,XSSFRow row, Map<String, Object> rowObjMap, String[] title,
            ExcelColConfig[] rowConfig) {
        //前面已判空，这里不判断
        String spaceType=String.valueOf(rowObjMap.get("spaceType"));
        String answer=String.valueOf(rowObjMap.get("answer"));
        if(SpaceType.getCode(spaceType)==null) {
            throw new RuntimeException(String.format("%s行空类型\"%s\"不支持,仅支持%s", rowNum,spaceType,SpaceType.getNames()));
        }else if(SpaceType.getCode(spaceType)==SpaceType.JUDGE.getValue()) {
            if(!answer.equals("对")&&!answer.equals("错")) {
                throw new RuntimeException(String.format("%s行是%s题，答案只能是\"对\"或\"错\"", rowNum,SpaceType.JUDGE.getName()));
            }
        }else if(SpaceType.getCode(spaceType)==SpaceType.CHOOSE_SINGLE.getValue()) {
            boolean error=false;
            if(answer.length()!=1) {
                error=true;
            }
            if(answer.charAt(0)<'A'||answer.charAt(0)>'E') {
                error=true;
            }
            if(error) {
                throw new RuntimeException(String.format("%s行是%s题，答案只能是A-E", rowNum,SpaceType.CHOOSE_SINGLE.getName()));
            }
        }
        
    }

};

以上用到了poi,apache log库,apache.commons库。

上传的页数(Sheet)往往少于我们建的表的数量，就是说一个Sheet要分拆到各个表。这是因为我们建表一般是按对象来建的，比如我们要上传一些题(试卷里的)，而这个题是有层次结构的。一套试卷有很多大题，每道大题下又有很多小题。但是上传时，为了方便，只上传一个excel。因为整理题内容的人认为都放在同一页是方便的，其实也确实是这样。试想，如果我们分开，每个小题一行，再用一个字段来表示大题。这个小题列表与试卷不是一一对应的，这就会增加核对的工作量。如果是一一对应的，从上到下一一核对即可，不是的话，核对速度就慢了。

就是说一个Sheet会有层次关系，父层信息会有冗余。那么需求就来了，冗余信息是不是一致的？比如，有两列，一个是题的序号，一个是题的题干。如果出现了这样的数据:序号都为1，但一个题干是aaa,而另一个题干是bbb。那个题干是对的？出现矛盾了吧。

解决这个问题的办法有两，简单的就是以第一行为准。复杂一点的就是把这样的数据列出来，列的方法可以是如sql里 select id,count(distinct name) from tab group by id 这样的算法，把count数大于1的(就是矛盾)反馈给用户。复杂的方法不推荐，一是这个算法复杂，增加复杂度，与业务员沟通也有难度。二是这个算法也不严谨，就算把每组都做好了，还有可能把主键给弄错了。还是需要人工检查。

有了人工检查，以"第一行为准"就可以了，沟通也简单。比如,导入了如下格式:

大题号,大题题干，小题号，小题题干

一个sheet一旦导进去应该就能从页面查看到导入多少个大题，每个大题下有多少个小题。即可以以层次的方式展示，也可以"试卷"的方式显示。这么一对比是否正确立即出来了。

文/程忠浏览次数：0次 2020-03-04 23:25:01

一个上传Excel(poi)的套路

相关阅读