Java 使用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();  //获得ruleswhile(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里引入他们。