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