Java 使用poi导入excel，结合xml文件进行数据验证的例子(增加了jar包)

ava 使用poi导入excel，结合xml文件进行数据验证的例子(增加了jar包)

假设现在要做一个通用的导入方法：

要求：

1.xml的只定义数据库表中的column字段，字段类型，是否非空等条件。

2.excel定义成模板，里面只填写了所需要的数据，有可能数据有问题。

3.在导入的时候就需要对每个excel单元格的数据进行验证。

4.验证完之后，若所有数据正确，那么批量保存。若有一点点错误，就不执行保存操作，并提示错误原因。

思路：

1.完美使用了Map的功能，先将xml中的数据存入map中，怎么存呢？

下面我根据xml文件来具体分析：（为图方便，我只做了字段的非空验证）

user.xml

 

<?xml version="1.0" encoding="UTF-8"?>
<excel>
   <entity name="用户表" code="user" >	
		<column name="状态" code="status" type="String"></column>	
		<column name="端口号" code="port" type="int">
			<rules> 
				<rule name="nullable" message="端口号不允许为空"></rule>
			</rules>				
		</column>	
	   <column name="IP地址" code="ip" type="String">
	   	   <rules> 
				<rule name="nullable" message="IP地址不允许为空"></rule>
			</rules>	
	   </column>	 
	   <column name="密码" code="password" type="String">
	   	    <rules> 
				<rule name="nullable" message="密码不允许为空"></rule>
			</rules>	
	   </column>   
	   <column name="用户名" code="username" type="String"></column>   
	   <column name="员工号" code="no" type="String">	   	
			<rules>
				<rule name="nullable" message="员工号不允许为空"></rule>
				<rule name="checkUnique" message="员工号已经存在"></rule>
			</rules>	
	   </column>      
	   <column name="头像" code="userImage" type="BLOB"></column>  
   </entity>
</excel>

根据xml所做的准备：

 

准备4个Map：

（1），已知 <entity> 中的name="用户表" ,定义entityMap 来存放实体类的map对象

（2），已知 “用户表”和 某个字段名“员工号”，那么就可以存放每一列的map对象

（3），已知 “用户表”和 某个字段名“员工号”，可以找到该列下的所有验证规则存放到map中

（4），已知 “用户表” 和 “ 员工号”和验证规则name "nullable",那么可以找到每一列的某一个验证规则

 

2.读取excel数据时，需要一一对应xml map中的字段与验证规则。

下面是excel数据：标注红色 * 号的表示必填项。

 

接下来就要看具体的实现代码了：

东西很多，我只贴两个比较重要的java 类

1.ParseExcelUtil.java  ,要试验代码，可以直接在工程里面单击右键--run as 运行这个类，不过前提是要导入这个测试项目，最后面我会上传。

 

package com.karen.test2;

import java.beans.IntrospectionException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.karen.database.Dao;
/**
 * 解析excel 工具类
 * @author PCCW
 *
 */
@SuppressWarnings("rawtypes")
public class ParseExcelUtil {
	
	public  FileInputStream fis ;
	public HSSFWorkbook workBook;
	public HSSFSheet sheet;
	public ParseXMLUtil parseXmlUtil;
	public StringBuffer errorString;
	
	/**当前实体类的code**/
	public String curEntityCode;
	/**表头map对象：key:entityCode, value:headMap(index,headTitle)**/
	public Map curEntityHeadMap ;
	
	/**字段的必填：key:entityCode+headTitle, value:true(必填),false(不必填)**/
	public Map curEntityColRequired;
	
	/**存放每一行的数据**/
	public  List listDatas ;
	
	
	public ParseExcelUtil(File excelFile,File xmlFile){
		try {
			if(excelFile == null){
				throw new FileNotFoundException();
			}
		   fis = new FileInputStream(excelFile);		   
		   workBook = new HSSFWorkbook(fis);
		   parseXmlUtil = new ParseXMLUtil(xmlFile);
		   errorString = new StringBuffer();
		   readExcelData();
		   	   				
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}catch (IOException e) {
			e.printStackTrace();
		}	
	}
	
	
	/**开始从excel读取数据**/	
	public void readExcelData(){
		int sheetSize = workBook.getNumberOfSheets();
		for(int i=0;i<sheetSize;i++){
			sheet = workBook.getSheetAt(i);
			String entityName = workBook.getSheetName(i);
			readSheetData(sheet,entityName);
		}
		
	}
	
	/**读每个sheet页的数据**/
	public void readSheetData(HSSFSheet sheet,String entityName){
		
		   int rowNumbers = sheet.getPhysicalNumberOfRows();
		   Map ent = (Map) parseXmlUtil.getEntityMap().get(entityName);
		   this.setCurEntityCode((String) ent.get("code"));
		   if(rowNumbers == 0){
			   System.out.println("================excel中数据为空！");
			   errorString.append(ParseConstans.ERROR_EXCEL_NULL);
		   }		  
			List colList = (List) parseXmlUtil.getColumnListMap().get(entityName);
			int xmlRowNum = colList.size();
		   HSSFRow excelRow = sheet.getRow(0);
		   int excelFirstRow = excelRow.getFirstCellNum();
		   int excelLastRow = excelRow.getLastCellNum();
		   if(xmlRowNum  != (excelLastRow-excelFirstRow)){
			     System.out.println("==================xml列数与excel列数不相符，请检查");
			     errorString.append(ParseConstans.ERROR_EXCEL_COLUMN_NOT_EQUAL);
		   }
		   readSheetHeadData(sheet);
			   
		   readSheetColumnData(sheet,entityName);
		   
		 
		 	   
	   }
	
   /**读取sheet页中的表头信息**/
   @SuppressWarnings({ "unchecked", "static-access"})
	public void readSheetHeadData(HSSFSheet sheet){
	   
		   Map headMap = new HashMap();
		   curEntityHeadMap = new HashMap();
		   curEntityColRequired = new HashMap();
		   HSSFRow excelheadRow = sheet.getRow(0);
		   int excelLastRow = excelheadRow.getLastCellNum();
		   String headTitle = "";
		   for(int i=0;i<excelLastRow;i++){
			   HSSFCell cell = excelheadRow.getCell(i);
			   headTitle = this.getStringCellValue(cell).trim();
			   if(headTitle.endsWith("*")){
				   curEntityColRequired.put(this.getCurEntityCode()+"_"+headTitle,true);
			   }else{
				   curEntityColRequired.put(this.getCurEntityCode()+"_"+headTitle,false);
			   }
			   headMap.put(i, headTitle);
		   }
		   curEntityHeadMap.put(this.getCurEntityCode(), headMap);
	   }
   
   /**读取sheet页里面的数据**/
   @SuppressWarnings({ "unchecked", "static-access" })
public void readSheetColumnData(HSSFSheet sheet,String entityName){
	   
	   HSSFRow excelheadRow = sheet.getRow(0);
	   int excelLastcell = excelheadRow.getLastCellNum();   //excel总列数
	   int excelRowNum = sheet.getLastRowNum();  //excel总行数
	   Map headMap = (Map) this.getCurEntityHeadMap().get(this.getCurEntityCode());	   
	   Map colMap = parseXmlUtil.getColumnMap();
	   listDatas =new ArrayList();
	   
	   for(int i=1;i<excelRowNum+1;i++){//行循环		  		   
		   HSSFRow columnRow = sheet.getRow(i);	
		   if(columnRow != null){
			   Map curRowCellMap = new HashMap();
			   for(int j =0; j<excelLastcell;j++){ //列循环
				   int cout =  headMap.get(j).toString().indexOf("*");
				   String headTitle ="";
				   if(cout == -1){
					  headTitle = headMap.get(j).toString();
				   }else{
					   headTitle =  headMap.get(j).toString().substring(0, cout);
				   }			   		    
				   Map curColMap =  (Map) colMap.get(entityName+"_"+headTitle);
				   String curColCode = (String) curColMap.get("code");
				   String curColType = (String) curColMap.get("type");
				   HSSFCell colCell = columnRow.getCell(j);
				   String value =this.getStringCellValue(colCell);
				   if(value != null){
					   value = value.trim();
				   }			  
				   String xmlColType = (String) curColMap.get("type");
				   if(xmlColType.equals("int")){
					   int   intVal = Integer.valueOf(value);
					   curRowCellMap.put(curColCode, intVal);  //将这一行的数据以code-value的形式存入map
				   }else{
				      curRowCellMap.put(curColCode, value); 
				   }
				   /**验证cell数据**/
				   validateCellData(i+1,j+1,colCell,entityName,headTitle,curColType);
			   }
			   listDatas.add(curRowCellMap);
		   } 
	   }
	   
	   if(this.getErrorString().length() ==0){//如果没有任何错误，就保存
		   saveExcelData(entityName);
		   System.out.println("导入数据成功！");
	   }else{
		   //清理所有的缓存clearMap();现在暂时未清理
		   String[] strArr = errorString.toString().split("<br>");
		   for(String s: strArr){
			   System.out.println(s);
		   }
		   
	   }
	   
	   
   }
   /**验证单元格数据**/
   @SuppressWarnings("static-access")
public void validateCellData(int curRow,int curCol,HSSFCell colCell,String entityName,String headName,String curColType){
	   
	   List rulList = (List) parseXmlUtil.getColumnRulesMap().get(entityName+"_"+headName);
	   if(rulList != null && rulList.size()>0){
		   for(int i=0 ; i<rulList.size() ; i++){
			   Map rulM = (Map) rulList.get(i);
			   String rulName = (String) rulM.get("name");
			   String rulMsg = (String) rulM.get("message");
			   String cellValue = this.getStringCellValue(colCell).trim();		   		   
			   if(rulName.equals(ParseConstans.RULE_NAME_NULLABLE)){		   
				   
				   if(cellValue.equals("")||cellValue == null){
					   errorString.append("第"+curRow+"行,第"+curCol+"列:"+rulMsg+"<br>");
				   }
			   }else {
				   //////这里写其他的验证规则。。。
			   }
		   }
	   }
   }
   
   /**保存excel里面的数据**/
   @SuppressWarnings("unchecked")
public void saveExcelData(String entityName){
	   
       List<User> users= new ArrayList();
	   for(int i = 0 ; i<this.getListDatas().size();i++){
		   Map excelCol = (Map) this.getListDatas().get(i);  //得到第 i 行的数据	   
		   User user = new User();
		   try {
			User obj = (User) BeanToMapUtil.convertMap(user.getClass(), excelCol);
			users.add(obj);	 
		} catch (IntrospectionException e) {			
			e.printStackTrace();
		} catch (IllegalAccessException e) {		
			e.printStackTrace();
		} catch (InstantiationException e) {		
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
			  
	   }
	   /**批量保存数据**/
	   Dao dao = new Dao();
	   for(int i = 0;i<users.size();i++){
		   try{
		   dao.saveUser(users.get(i));
		   
		   }catch(Exception e){
			   e.printStackTrace();
		   }
	   }
	   
   }
   
   /**
	 * 获得单元格字符串
	 * @throws UnSupportedCellTypeException 
	 */
	public static String getStringCellValue(HSSFCell cell) {
		if (cell == null){
			return null;
		}

		String result = "";
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_BOOLEAN:
				result = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					java.text.SimpleDateFormat TIME_FORMATTER = new java.text.SimpleDateFormat(
							"yyyy-MM-dd");
					result = TIME_FORMATTER.format(cell.getDateCellValue());
				}
				else{
					double doubleValue = cell.getNumericCellValue();
					result = "" + doubleValue;
				}
				break;
			case HSSFCell.CELL_TYPE_STRING:
				if (cell.getRichStringCellValue() == null){
					result = null;
				}
				else{
					result = cell.getRichStringCellValue().getString();
				}
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				result = null;
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				try{
					result = String.valueOf(cell.getNumericCellValue());   
		        }catch(Exception e){
		        	result = cell.getRichStringCellValue().getString();
		        }
				break;
			default:
				result = "";
		}
		
		return result;
	}
	/**主方法**/
	public static void main(String[] args) {
		
		File excelFile = new File("src/user.xls");
		File xmlFile = new File("src/user.xml");
        new ParseExcelUtil(excelFile,xmlFile);		
        
	}

	
	
	public String getCurEntityCode() {
		return curEntityCode;
	}
	public void setCurEntityCode(String curEntityCode) {
		this.curEntityCode = curEntityCode;
	}
	public Map getCurEntityHeadMap() {
		return curEntityHeadMap;
	}
	public void setCurEntityHeadMap(Map curEntityHeadMap) {
		this.curEntityHeadMap = curEntityHeadMap;
	}
	public ParseXMLUtil getParseXmlUtil() {
		return parseXmlUtil;
	}
	public void setParseXmlUtil(ParseXMLUtil parseXmlUtil) {
		this.parseXmlUtil = parseXmlUtil;
	}
	public Map getCurEntityColRequired() {
		return curEntityColRequired;
	}
	public void setCurEntityColRequired(Map curEntityColRequired) {
		this.curEntityColRequired = curEntityColRequired;
	}
	public List getListDatas() {
		return listDatas;
	}
	public void setListDatas(List listDatas) {
		this.listDatas = listDatas;
	}
	public StringBuffer getErrorString() {
		return errorString;
	}
	public void setErrorString(StringBuffer errorString) {
		this.errorString = errorString;
	}


}

2.ParseXMLUtil.java

 

这个类是用来解析xml的，测试方法同样可以右键 run as 运行。可以把下面的一段注释放开，查看打印结果。

 

package com.karen.test2;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

/**
 * 解析xml工具类
 * @author PCCW-80352891
 *
 */
@SuppressWarnings("rawtypes")
public class ParseXMLUtil {

	 /**entity map对象，key:name ,value:entity的属性map集**/
	public Map entityMap ;
	
	/**column map 对象，key:entityName_colName , value:column的属性map集 **/
	public Map columnMap;
	
	/**rule map 对象，key:entityName_colName_ruleName, value: rule 的map集：找到一行rule**/
	public Map ruleMap ;
	
	/**rules map 对象, key:entityName_colName, value: rules 的map集:找到该column下所有的rule**/
	public Map  columnRulesMap ;
	
	/**entity--column map: key:entityName, value: column list:根据实体类名得到所有的列**/
	public Map columnListMap ;
	
   /**column list**/
	public List columnList ;
	
	 
    /**开始解析xml文件**/
	public ParseXMLUtil(File xmlFilePath){
		FileInputStream in = null;
		try {
			if(xmlFilePath == null){
				 throw new FileNotFoundException();
			}
			SAXReader reader = new SAXReader();		           
           	in = new FileInputStream(xmlFilePath);	           	
			Document doc = reader.read(in);
			Element root = doc.getRootElement();	
			Iterator itEntity = root.elements("entity").iterator();
			while(itEntity.hasNext()){
				Element entity = (Element) itEntity.next();
				parseEntity(entity);
			}
			
			/**测试entityMap 是否正确**/
			Map enMap = (Map) this.getEntityMap().get("用户表");
			Set<?> set = enMap.keySet();
			Iterator it = set.iterator();
			while(it.hasNext()){
				String uu = (String) it.next();
				System.out.println("entity properties:"+uu+" = "+enMap.get(uu));
			}
			
/*			*//**测试column list是否正确**//*
			List colList = (List) this.getColumnListMap().get("用户表");
			System.out.println("column size:"+colList.size());
			
			*//**测试columnMap是否正确**//*
			Map colMap = (Map) this.getColumnMap().get("用户表_员工号");						
				Set<?> coListSet = colMap.keySet();
				Iterator coListIt = coListSet.iterator();
				while(coListIt.hasNext()){
					String coListKey = (String) coListIt.next();
					System.out.println("column  properties: "+coListKey+" = "+colMap.get(coListKey));
				}		
			*//**测试ruleMap是否正确**//*	
			if(this.getColumnRulesMap() != null){
			    List rulesValidList = (List) this.getColumnRulesMap().get("用户表_员工号");				
				for(int i=0;i<rulesValidList.size(); i++){
					Map colRuleMap = (Map) rulesValidList.get(i);
				    String ruleName = (String) colRuleMap.get("name");
				    Map ruleMa = (Map) this.getRuleMap().get("用户表_员工号_"+ruleName); //eg: 用户表_用户名_nullable
				    String mess = (String) ruleMa.get("message");
				    System.out.println("Validate Rules"+i+" : "+mess);							
		  	    } 
			}*/
		}catch(Exception e){
			e.printStackTrace();
		}
		
	}
	
	 /**开始解析entity**/
	@SuppressWarnings("unchecked")
	public void parseEntity(Element entity){
		if(entity != null){
			
			/**对数据进行初始化设置**/
			columnListMap = new HashMap();
			columnMap = new HashMap();
			entityMap = new HashMap();
			ruleMap = new HashMap();
			columnRulesMap = new HashMap();
			columnList = new ArrayList();
			
			setEntityMap(entity);			
			String entityName = entity.attributeValue("name");
			Iterator itColumn = entity.elements("column").iterator();
			while(itColumn.hasNext()){
				Element column = (Element) itColumn.next();
				setColumnMap(entityName,column);
			}
			columnListMap.put(entityName, columnList);
		}
	}
	 
	
	
	/**将entity放入entityMap中**/
	@SuppressWarnings("unchecked")
	public void setEntityMap(Element entity){		
		Map ent = new HashMap();
		String name = entity.attributeValue("name");
		String code = entity.attributeValue("code");
		ent.put("name", name);
		ent.put("code", code);
		entityMap.put(name, ent);			
	}
	
	/**将column放入columnMap中**/
	@SuppressWarnings("unchecked")
	public void setColumnMap(String entityName,Element column){
		if(column != null){		
			Map col = new HashMap();
			String name = column.attributeValue("name");
			String code = column.attributeValue("code");
			String type = column.attributeValue("type");
			col.put("name", name);
			col.put("code", code);
			col.put("type", type);
			String columnMapKey = entityName+"_"+name;    //eg:  用户表_用户名
			columnMap.put(columnMapKey, col);		
			columnList.add(col);
			Iterator ruleIt = column.elements("rules").iterator();  //获得rules
			while(ruleIt.hasNext()){
				Element rules = (Element)ruleIt.next(); 
    			Iterator rule  = rules.elements("rule").iterator();   //获得 rule
    			while(rule.hasNext()){
    				Element ruleValid = (Element) rule.next();     //获得每一行rule
    				setRuleMap(entityName,name,ruleValid);    				
    			}
			}
		}
	}
		
    /**将 rule 验证规则放入ruleMap中**/
	@SuppressWarnings("unchecked")
	public void setRuleMap(String entityName,String columnName,Element ruleValid){
		if(ruleValid != null){			
			String ruleName = ruleValid.attributeValue("name");
			String ruleMsg = ruleValid.attributeValue("message");
			Map ruleValidMap = new HashMap();
			ruleValidMap.put("name", ruleName);
			ruleValidMap.put("message", ruleMsg);
			String ruleStrKey = entityName+"_"+columnName+"_"+ruleName;
			String colStrKey = entityName+"_"+columnName;
			if(this.getColumnRulesMap().containsKey(colStrKey)){
    			List valids = (List) this.getColumnRulesMap().get(colStrKey);
    			valids.add(ruleValidMap);
    		}else{
    			List valids = new ArrayList();
    			valids.add(ruleValidMap);
    			this.columnRulesMap.put(colStrKey, valids);  //将每个column下的所有rules存入该map中
    		}
			ruleMap.put(ruleStrKey, ruleValidMap); //将每个column下的一条rule存入该map中
		}
	}
	
	/**主方法**/
	public static void main(String[] args) {
		File file = new File("src/user.xml");
       new ParseXMLUtil(file);		
	}

	/**所有的get set 方法**/
	public Map getEntityMap() {
		return entityMap;
	}

	public void setEntityMap(Map entityMap) {
		this.entityMap = entityMap;
	}

	public Map getColumnMap() {
		return columnMap;
	}

	public void setColumnMap(Map columnMap) {
		this.columnMap = columnMap;
	}

	public Map getRuleMap() {
		return ruleMap;
	}

	public void setRuleMap(Map ruleMap) {
		this.ruleMap = ruleMap;
	}

	public Map getColumnRulesMap() {
		return columnRulesMap;
	}

	public void setColumnRulesMap(Map columnRulesMap) {
		this.columnRulesMap = columnRulesMap;
	}

	public Map getColumnListMap() {
		return columnListMap;
	}

	public void setColumnListMap(Map columnListMap) {
		this.columnListMap = columnListMap;
	}



}

3.既然做导入，当然需要连接数据库啦。只需要在mysql数据库中，建立一个 名为 chat 的数据库,然后导入下面的sql.来创建一张user表

 

 

CREATE TABLE `user` (
  `status` varchar(20) default NULL,
  `port` int(10) NOT NULL,
  `ip` varchar(40) NOT NULL,
  `password` varchar(10) NOT NULL,
  `username` varchar(100) NOT NULL,
  `no` varchar(10) default NULL,
  `userImage` blob,
  PRIMARY KEY  (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

4.例子肯定需要很多jar包，比如poi啊，各种包。我就不在这里写出来了。

 需要例子源码 请到这里下载：

http://download.csdn.net/detail/chenxuejiakaren/4439307

5.运行方法： 将例子导入到eclipse之中，然后可能会因为jdk版本不一样会有红色感叹号，没关系，改一下。单击项目右键--properties--java build path--libraries--找jdk啊。这个搞java的都会吧。

然后，单击右键 run as 运行ParseExcelUtil.java 就可以啦。

 

关于例子导入后会提示缺少包的问题：

我引入的相关jar包是在eclipse里面直接引入的，没有相对于的lib目录。主要是缺少了2个jar

poi-3.8-20120326.jar    下载地址：   http://download.csdn.net/detail/chenxuejiakaren/4440128

mysql-connector-java-5.0.8-bin.jar      下载地址：  http://download.csdn.net/detail/chenxuejiakaren/4440132

必须要在eclipse里引入他们。