java POI Execel表格的统一校验与导入

一、前言

最近公司项目有很多地方需要导入execel文件，之前用的那个没有对execel导入做统一的处理，是针对每一个execel表格分别处理的，这样处理起来很麻烦，每次都要写很多的逻辑代码，而且还没有对execel表格的内容做约束，这样很可能出现问题。所以，有必要对execel表格文件做统一的处理，而不是针对某一个execel文件。

二、设计概述

对execel表格数据的校验采用Hibernate Validator这个组件，这个校验器很强大，具体有什么功能请参考官方文档；由于数据的内容、格式每次都是变化的，故针对每一行数据需要动态地把它封装成一个对象，这时候就需要用到动态语言，我用的是Spring EL语言，这个特性是Spring3.0 采用的，很强大，具体的可参考官方文档。

三、实现步骤

1、找数据范围

execel表格的数据范围很灵活，不用限定在某行、某列，我们假设execel表格的数据是集中的，也即集中在某一个矩形区域，我们只需要扫描每一行每一列，找到数据开始的单元格，它左边、下面就都是数据内容了。

2、封装成字符串

execel的单元格格式很不统一，我们需要把每一个单元格的数据内容都解析成字符串，然后以一行为单位，封装成字符串数字，再把数据送到需要装载的Bean，让它自己去封装。

四、实现考虑

如果表格数据太多，一次性就把数据都解析完了，这样可能会使系统崩溃了，所有，我的做法是每解析一行数据，就把它插入数据库，这样，再多的数据也不会有问题。

校验的时候，需要写个配置文件，这个配置文件说明了需要导入的表格的内容以及各式，以免非法导入。

五、代码

1、需要引入的包（我只是大概说一下，可能会有遗漏）   

     解析xml用到的包 dom4j

     解析execel用到的包  poi相关包

     动态解析用到的包  Spring EL

代码结构

一个异常类、一个校验数据转换类、一个主类、一个配置文件、以及相关测试类。

最主要是一个用来解析Execel的类，另外一个需要校验的类

package test.bean;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.validation.constraints.DecimalMin;
import javax.validation.constraints.Null;
import javax.validation.constraints.Pattern;
import javax.validation.constraints.Size;

import org.hibernate.validator.constraints.Length;
import org.hibernate.validator.constraints.NotEmpty;

import com.dcrx.execelimport.ValidationExeception;

public class Warehouse {
	@NotEmpty
	private String warehouseId;
	@Length(min = 2, max = 16)
	private String warehouseName;
	private int id;
	//@Range(min=2,max=2)
	@NotEmpty
	@Pattern(regexp = "[12]")
	private String type = "1"; // 1-自有，2-代储
	private int status = 0; // 0-正常，1-删除
	private String companyId = "";
	private Date date;
	@DecimalMin("0.1")
	private double dou;
	private float flo;
	private boolean boo;
	
	DateFormat format = new SimpleDateFormat("yyyy-MM-dd"); // 针对日期的默认转换形式

	public void assertEquals(String[] values,int size) throws ValidationExeception{
		if(values.length != size)
			throw new ValidationExeception("配置文件提供"+values.length+"个字段"+",而导入数据库需要提供"+size+"个字段");
	}
	public String getIntString(String val){
		if(val.equals(""))
			return "";
		else{
			double d = Double.valueOf(val);
			Integer i = (int)d;
			return  i.toString();
		}
	}
	public int getInt(String val){
		return val.equals("")?0:Integer.valueOf(getIntString(val));
	}
	public float getFloat(String val){
		return val.equals("")?0.0f:Float.valueOf(val);
	}
	public double getDouble(String val){
		return val.equals("")?0.0:Double.valueOf(val);
	}
	public Date getDate(String val) throws ParseException{
		return  val.equals("") ? new Date() : format.parse(val);
	}
	public void setValues(String[] values) throws Exception{
            assertEquals(values,7);
			this.warehouseId = values[0];
			this.warehouseName = values[1];
			this.type = getIntString(values[2]);		
			this.date = values[3].equals("") ? new Date() : format.parse(values[3]);
			this.dou = values[4].equals("") ? 0.0 : Double.valueOf(values[4]);
			this.flo = values[5].equals("") ? 0.0f : Float.valueOf(values[5]);
			this.boo = values[6].equals("") ? false : Boolean.valueOf(values[6]);
	}

	public boolean isBoo() {
		return boo;
	}

	public void setBoo(boolean boo) {
		this.boo = boo;
	}

	public String getWarehouseId() {
		return warehouseId;
	}

	public void setWarehouseId(String warehouseId) {
		this.warehouseId = warehouseId;
	}

	public String getWarehouseName() {
		return warehouseName;
	}

	public void setWarehouseName(String warehouseName) {
		this.warehouseName = warehouseName;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public int getStatus() {
		return status;
	}

	public void setStatus(int status) {
		this.status = status;
	}

	public String getCompanyId() {
		return companyId;
	}

	public void setCompanyId(String companyId) {
		this.companyId = companyId;
	}

	public Date getDate() {
		return date;
	}

	public void setDate(Date date) {
		this.date = date;
	}

	public double getDou() {
		return dou;
	}

	public void setDou(double dou) {
		this.dou = dou;
	}

	public float getFlo() {
		return flo;
	}

	public void setFlo(float flo) {
		this.flo = flo;
	}

}

以上是一个实体类，具体的校验规则是用到了Hibernate Validator 的语法

下面是主类

package com.dcrx.execelimport;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.ValidatorFactory;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.expression.EvaluationException;
import org.springframework.expression.Expression;
import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;

/**
 * @Desc 对execel文件的校验并且解析
 * @author jackshen
 * 
 */
@SuppressWarnings("rawtypes")
public class ExecelUtil {
	Logger logger = Logger.getLogger(this.getClass());

	private String description = "";// 如果校验失败，将会给出详细提示信息
	private Sheet sheet;// execel 对象
    private List<String> fieldList;//从xml读取到的execel表格信息
    private int rowIndex = 0;//当前操作行
    private Object objectBean;//每一行数据封装 
    private Cell cellStart;// 数据的开始单元格
    private Class clazz; //需要封装的类
    private Validator validator; //hibernate 的校验器
    private String[] fieldVals ; //从execel读到的某一行的数据
    private int fieldSize = 0;  //有效数据的列数
    DateFormat format = new SimpleDateFormat("yyyy-MM-dd"); // 针对日期的默认转换形式
    private Expression exp ;//EL 解析器
    private ExpressionParser parser;
	public String getDescription() {
		return description;
	}

	public Object getObjectBean() {
		return objectBean;
	}
	/**
	 * 
	 * @param execelFilename
	 *            execel文件名
	 * @param xmlFilename
	 *            execel文件所对应的校验文件
	 * @param calzz 需要封装的类
	 */
	public ExecelUtil(InputStream execelIS,String xmlFilename,Class clazz) throws ValidationExeception{
		// 打开execel工作簿
		Workbook wb = null;
		try {
			wb = new HSSFWorkbook(execelIS);
		} catch (IOException e) {
			logger.error(e);
			throw new ValidationExeception("","加载文件失败,请确保是否是Execel表格");
		}		
		sheet = wb.getSheetAt(0);// 默认取第一个工作簿
		//读配置文件，获取所有的属性列描述
        fieldList = this.readFieldsFromXML(getAbsolutePath(xmlFilename));
        //个数
        fieldSize = fieldList.size();
        
		//找到有效数据的开始单元格
		cellStart = this.findStartCell();
		if(cellStart == null){			
			throw new ValidationExeception("",this.description);
		}
		
		//每次读取一行execel数据，rowIndex每次增1
		rowIndex = cellStart.getRowIndex()+1;
		
		//需要封装的对象类
		this.clazz = clazz;
		//初始化校验器
		ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
		validator = factory.getValidator();
		//初始化EL解析器
		parser = new SpelExpressionParser();
		exp = parser.parseExpression("values");
	}
	//是否还有数据
    public boolean hasNext(){
    	Row row = sheet.getRow(rowIndex++);
    	if(row == null)
    		return false;
    	fieldVals = this.getRowValues(row, cellStart.getColumnIndex());
        if(Arrays.asList(fieldVals).indexOf("") != -1){
        	for(String s :fieldVals)//如果每个字段都是空的，则返回false 否则true
        		if(!s.equals(""))
        			return true;
        	return false;
        }
    	return true;
    }
    //校验
    public boolean validate(){
        try {
        	objectBean = Class.forName(clazz.getName()).newInstance();
		} catch (Exception e) {
			logger.error(e);
		}
		
		try{
		    exp.setValue(objectBean, fieldVals);// 给objectBean的属性赋值
		}catch(EvaluationException e){//由于所有的数据类型转换都有objectBean里面来处理，故可能有异常，需要进行相应的处理
			List exList = Arrays.asList("ParseException","NumberFormatException");//一般可能发生的异常
			Throwable t = e.getCause();
			while(t!=null){
				String causeClazz = t.getClass().getSimpleName();
				if(exList.contains(causeClazz)){
                       this.description = "第" +rowIndex+"行，类型转换失败："+t.getMessage();
                       return false;
				}else if(causeClazz.equals("ValidationExeception")){//自定义异常
					this.description = "第" +rowIndex+"行,"+t.getMessage();
					return false;
				}else
					t = t.getCause();
			}			 
			this.description = parser.parseExpression("message").getValue(objectBean,String.class);
			return false;
		}
		//校验，校验规则是配置在objectBean对象里面
		Set<ConstraintViolation<Object>> constraintViolations = validator.validate(objectBean);
		if(constraintViolations.size() >0){//校验失败时，提示相应信息
			for (ConstraintViolation<Object> vl : constraintViolations) {
				this.description = "第" +rowIndex+"行，校验出错："+ vl.getPropertyPath() + "=" + vl.getInvalidValue() + ":" + vl.getMessage();			
				return false;
			}			
		}
		return true;
    }
    private String[] getRowValues(Row row,int columnStartIndex){
        String[] values = new String[fieldSize];
    	for(int j = columnStartIndex,t=0;t<fieldSize;j++,t++){
    		Cell c = row.getCell(j);
    		if(c==null){
    			values[t] = "";
    			continue;
    		}
    		switch(c.getCellType()){
    		case Cell.CELL_TYPE_BLANK:
    			values[t] = "";
    			break;
    		case Cell.CELL_TYPE_BOOLEAN:
    			values[t] = String.valueOf(c.getBooleanCellValue());
    			break;
    		case Cell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(c))
					values[t] = format.format(c.getDateCellValue());
				else 
					values[t] = String.valueOf(c.getNumericCellValue());
    			break;
    		case Cell.CELL_TYPE_STRING:
    			values[t] = String.valueOf(c.getStringCellValue());
    			break;
    		default:
    			values[t] = "";
    			break;
    		}
    	}
    	return values;
    }
	// 根据某一个单元格，得到更人性化的显示,例如“A4”
	private String getCellRef(Cell cell) {
		return CellReference.convertNumToColString(cell.getColumnIndex()) + (cell.getRowIndex() + 1);
	}
	private  String getAbsolutePath(String file) throws ValidationExeception {
		try {
			file = this.getClass().getClassLoader().getResource(file).getFile();

		} catch (NullPointerException e) {
			throw new ValidationExeception(file, "文件不存在");
		}
		try {
			// 解决当出现中文路径时不能解析的bug
			file = URLDecoder.decode(file, "UTF-8");
		} catch (UnsupportedEncodingException e) {
			throw new ValidationExeception(file, "解码失败");
		}
		return file;
	}
	private List<String> readFieldsFromXML(String xmlFilename)throws ValidationExeception{
		SAXReader reader = new SAXReader();
		Document document = null;
		
		try {
			document = reader.read(new File(xmlFilename));// 加载配置文件
		} catch (DocumentException e) {
			e.printStackTrace();
			this.description = "IO 异常，读取配置文件失败";
			throw new ValidationExeception(xmlFilename,"IO 异常，读取配置文件失败");
		}
		
		Element root = document.getRootElement();
		List<String> fields = new ArrayList<String>();
		for (Iterator iter = root.elementIterator("field"); iter.hasNext();) {
			Element field = (Element) iter.next();
            fields.add(field.getTextTrim());
		}
		
		return fields;
	}
	/**
	 * 从execel表中找到数据开始的单元格
	 * @return
	 */
	private  Cell findStartCell(){
		String firstFieldDesc = this.fieldList.get(0);
		int endRow = sheet.getLastRowNum()>100?100:sheet.getLastRowNum();
		for(int i = 0;i<=endRow;i++){
			Row r = sheet.getRow(i);
			if (r == null)
				continue;
			for(int j = 0;j < r.getLastCellNum();j++){
				Cell c = r.getCell(j);
				if( c == null)
					continue;
				if(c.getCellType() == Cell.CELL_TYPE_STRING){
					if(c.getStringCellValue().trim().equals(firstFieldDesc)){//找到第一个符合要求的字段，接下来判断它相邻的字段是否都符合要求
                        if(fieldList.size()>r.getLastCellNum()-j){
                        	this.description = "execel表格与所给配置描述不符，请下载模板文件";
                        	return null;
                        }
						for(int k=j+1,t=1;k<=j+fieldList.size()-1;k++,t++){
							Cell c2 = r.getCell(k);
							if(c2 == null){
								this.description = "请确保单元格"+this.getCellRef(c2)+"内容是\""+fieldList.get(t)+"\"";
								return null;
							}
							if(c2.getCellType() == Cell.CELL_TYPE_STRING){
								if(c2.getStringCellValue().contains(fieldList.get(t)))
									continue;
								else{
										this.description = "请确保单元格"+this.getCellRef(c2)+"内容是\""+fieldList.get(t)+"\"";
										return null;	
								}
							}
							 
						}
						return c;
					}else
						continue;
				}else
					continue;
			}
		}
		this.description = "找不到\""+fieldList.get(0)+"\"这一列";
		return null;
	}
}

附件有一个完整的Demo，只需要引入相应的包就能跑。

http://download.youkuaiyun.com/detail/jackshen310/4472852