假设现在要做一个通用的导入方法:
要求:
1.xml的只定义数据库表中的column字段,字段类型,是否非空等条件。
2.excel定义成模板,里面只填写了所需要的数据,有可能数据有问题。
3.在导入的时候就需要对每个excel单元格的数据进行验证。
4.验证完之后,若所有数据正确,那么批量保存。若有一点点错误,就不执行保存操作,并提示错误原因。
思路:
1.完美使用了Map的功能,先将xml中的数据存入map中,怎么存呢?
下面我根据xml文件来具体分析:(为图方便,我只做了字段的非空验证)
user.xml
- <?xmlversion="1.0"encoding="UTF-8"?>
- <excel>
- <entityname="用户表"code="user">
- <columnname="状态"code="status"type="String"></column>
- <columnname="端口号"code="port"type="int">
- <rules>
- <rulename="nullable"message="端口号不允许为空"></rule>
- </rules>
- </column>
- <columnname="IP地址"code="ip"type="String">
- <rules>
- <rulename="nullable"message="IP地址不允许为空"></rule>
- </rules>
- </column>
- <columnname="密码"code="password"type="String">
- <rules>
- <rulename="nullable"message="密码不允许为空"></rule>
- </rules>
- </column>
- <columnname="用户名"code="username"type="String"></column>
- <columnname="员工号"code="no"type="String">
- <rules>
- <rulename="nullable"message="员工号不允许为空"></rule>
- <rulename="checkUnique"message="员工号已经存在"></rule>
- </rules>
- </column>
- <columnname="头像"code="userImage"type="BLOB"></column>
- </entity>
- </excel>
准备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 运行这个类,不过前提是要导入这个测试项目,最后面我会上传。
- packagecom.karen.test2;
- importjava.beans.IntrospectionException;
- importjava.io.File;
- importjava.io.FileInputStream;
- importjava.io.FileNotFoundException;
- importjava.io.IOException;
- importjava.lang.reflect.InvocationTargetException;
- importjava.util.ArrayList;
- importjava.util.HashMap;
- importjava.util.List;
- importjava.util.Map;
- importorg.apache.poi.hssf.usermodel.HSSFCell;
- importorg.apache.poi.hssf.usermodel.HSSFDateUtil;
- importorg.apache.poi.hssf.usermodel.HSSFRow;
- importorg.apache.poi.hssf.usermodel.HSSFSheet;
- importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
- importcom.karen.database.Dao;
- /**
- *解析excel工具类
- *@authorPCCW
- *
- */
- @SuppressWarnings("rawtypes")
- publicclassParseExcelUtil{
- publicFileInputStreamfis;
- publicHSSFWorkbookworkBook;
- publicHSSFSheetsheet;
- publicParseXMLUtilparseXmlUtil;
- publicStringBuffererrorString;
- /**当前实体类的code**/
- publicStringcurEntityCode;
- /**表头map对象:key:entityCode,value:headMap(index,headTitle)**/
- publicMapcurEntityHeadMap;
- /**字段的必填:key:entityCode+headTitle,value:true(必填),false(不必填)**/
- publicMapcurEntityColRequired;
- /**存放每一行的数据**/
- publicListlistDatas;
- publicParseExcelUtil(FileexcelFile,FilexmlFile){
- try{
- if(excelFile==null){
- thrownewFileNotFoundException();
- }
- fis=newFileInputStream(excelFile);
- workBook=newHSSFWorkbook(fis);
- parseXmlUtil=newParseXMLUtil(xmlFile);
- errorString=newStringBuffer();
- readExcelData();
- }catch(FileNotFoundExceptione){
- e.printStackTrace();
- }catch(IOExceptione){
- e.printStackTrace();
- }
- }
- /**开始从excel读取数据**/
- publicvoidreadExcelData(){
- intsheetSize=workBook.getNumberOfSheets();
- for(inti=0;i<sheetSize;i++){
- sheet=workBook.getSheetAt(i);
- StringentityName=workBook.getSheetName(i);
- readSheetData(sheet,entityName);
- }
- }
- /**读每个sheet页的数据**/
- publicvoidreadSheetData(HSSFSheetsheet,StringentityName){
- introwNumbers=sheet.getPhysicalNumberOfRows();
- Mapent=(Map)parseXmlUtil.getEntityMap().get(entityName);
- this.setCurEntityCode((String)ent.get("code"));
- if(rowNumbers==0){
- System.out.println("================excel中数据为空!");
- errorString.append(ParseConstans.ERROR_EXCEL_NULL);
- }
- ListcolList=(List)parseXmlUtil.getColumnListMap().get(entityName);
- intxmlRowNum=colList.size();
- HSSFRowexcelRow=sheet.getRow(0);
- intexcelFirstRow=excelRow.getFirstCellNum();
- intexcelLastRow=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"})
- publicvoidreadSheetHeadData(HSSFSheetsheet){
- MapheadMap=newHashMap();
- curEntityHeadMap=newHashMap();
- curEntityColRequired=newHashMap();
- HSSFRowexcelheadRow=sheet.getRow(0);
- intexcelLastRow=excelheadRow.getLastCellNum();
- StringheadTitle="";
- for(inti=0;i<excelLastRow;i++){
- HSSFCellcell=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"})
- publicvoidreadSheetColumnData(HSSFSheetsheet,StringentityName){
- HSSFRowexcelheadRow=sheet.getRow(0);
- intexcelLastcell=excelheadRow.getLastCellNum();//excel总列数
- intexcelRowNum=sheet.getLastRowNum();//excel总行数
- MapheadMap=(Map)this.getCurEntityHeadMap().get(this.getCurEntityCode());
- MapcolMap=parseXmlUtil.getColumnMap();
- listDatas=newArrayList();
- for(inti=1;i<excelRowNum+1;i++){//行循环
- HSSFRowcolumnRow=sheet.getRow(i);
- if(columnRow!=null){
- MapcurRowCellMap=newHashMap();
- for(intj=0;j<excelLastcell;j++){//列循环
- intcout=headMap.get(j).toString().indexOf("*");
- StringheadTitle="";
- if(cout==-1){
- headTitle=headMap.get(j).toString();
- }else{
- headTitle=headMap.get(j).toString().substring(0,cout);
- }
- MapcurColMap=(Map)colMap.get(entityName+"_"+headTitle);
- StringcurColCode=(String)curColMap.get("code");
- StringcurColType=(String)curColMap.get("type");
- HSSFCellcolCell=columnRow.getCell(j);
- Stringvalue=this.getStringCellValue(colCell);
- if(value!=null){
- value=value.trim();
- }
- StringxmlColType=(String)curColMap.get("type");
- if(xmlColType.equals("int")){
- intintVal=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(Strings:strArr){
- System.out.println(s);
- }
- }
- }
- /**验证单元格数据**/
- @SuppressWarnings("static-access")
- publicvoidvalidateCellData(intcurRow,intcurCol,HSSFCellcolCell,StringentityName,StringheadName,StringcurColType){
- ListrulList=(List)parseXmlUtil.getColumnRulesMap().get(entityName+"_"+headName);
- if(rulList!=null&&rulList.size()>0){
- for(inti=0;i<rulList.size();i++){
- MaprulM=(Map)rulList.get(i);
- StringrulName=(String)rulM.get("name");
- StringrulMsg=(String)rulM.get("message");
- StringcellValue=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")
- publicvoidsaveExcelData(StringentityName){
- List<User>users=newArrayList();
- for(inti=0;i<this.getListDatas().size();i++){
- MapexcelCol=(Map)this.getListDatas().get(i);//得到第i行的数据
- Useruser=newUser();
- try{
- Userobj=(User)BeanToMapUtil.convertMap(user.getClass(),excelCol);
- users.add(obj);
- }catch(IntrospectionExceptione){
- e.printStackTrace();
- }catch(IllegalAccessExceptione){
- e.printStackTrace();
- }catch(InstantiationExceptione){
- e.printStackTrace();
- }catch(InvocationTargetExceptione){
- e.printStackTrace();
- }
- }
- /**批量保存数据**/
- Daodao=newDao();
- for(inti=0;i<users.size();i++){
- try{
- dao.saveUser(users.get(i));
- }catch(Exceptione){
- e.printStackTrace();
- }
- }
- }
- /**
- *获得单元格字符串
- *@throwsUnSupportedCellTypeException
- */
- publicstaticStringgetStringCellValue(HSSFCellcell){
- if(cell==null){
- returnnull;
- }
- Stringresult="";
- switch(cell.getCellType()){
- caseHSSFCell.CELL_TYPE_BOOLEAN:
- result=String.valueOf(cell.getBooleanCellValue());
- break;
- caseHSSFCell.CELL_TYPE_NUMERIC:
- if(HSSFDateUtil.isCellDateFormatted(cell)){
- java.text.SimpleDateFormatTIME_FORMATTER=newjava.text.SimpleDateFormat(
- "yyyy-MM-dd");
- result=TIME_FORMATTER.format(cell.getDateCellValue());
- }
- else{
- doubledoubleValue=cell.getNumericCellValue();
- result=""+doubleValue;
- }
- break;
- caseHSSFCell.CELL_TYPE_STRING:
- if(cell.getRichStringCellValue()==null){
- result=null;
- }
- else{
- result=cell.getRichStringCellValue().getString();
- }
- break;
- caseHSSFCell.CELL_TYPE_BLANK:
- result=null;
- break;
- caseHSSFCell.CELL_TYPE_FORMULA:
- try{
- result=String.valueOf(cell.getNumericCellValue());
- }catch(Exceptione){
- result=cell.getRichStringCellValue().getString();
- }
- break;
- default:
- result="";
- }
- returnresult;
- }
- /**主方法**/
- publicstaticvoidmain(String[]args){
- FileexcelFile=newFile("src/user.xls");
- FilexmlFile=newFile("src/user.xml");
- newParseExcelUtil(excelFile,xmlFile);
- }
- publicStringgetCurEntityCode(){
- returncurEntityCode;
- }
- publicvoidsetCurEntityCode(StringcurEntityCode){
- this.curEntityCode=curEntityCode;
- }
- publicMapgetCurEntityHeadMap(){
- returncurEntityHeadMap;
- }
- publicvoidsetCurEntityHeadMap(MapcurEntityHeadMap){
- this.curEntityHeadMap=curEntityHeadMap;
- }
- publicParseXMLUtilgetParseXmlUtil(){
- returnparseXmlUtil;
- }
- publicvoidsetParseXmlUtil(ParseXMLUtilparseXmlUtil){
- this.parseXmlUtil=parseXmlUtil;
- }
- publicMapgetCurEntityColRequired(){
- returncurEntityColRequired;
- }
- publicvoidsetCurEntityColRequired(MapcurEntityColRequired){
- this.curEntityColRequired=curEntityColRequired;
- }
- publicListgetListDatas(){
- returnlistDatas;
- }
- publicvoidsetListDatas(ListlistDatas){
- this.listDatas=listDatas;
- }
- publicStringBuffergetErrorString(){
- returnerrorString;
- }
- publicvoidsetErrorString(StringBuffererrorString){
- this.errorString=errorString;
- }
- }
2.ParseXMLUtil.java
这个类是用来解析xml的,测试方法同样可以右键 run as 运行。可以把下面的一段注释放开,查看打印结果。
- packagecom.karen.test2;
- importjava.io.File;
- importjava.io.FileInputStream;
- importjava.io.FileNotFoundException;
- importjava.util.ArrayList;
- importjava.util.HashMap;
- importjava.util.Iterator;
- importjava.util.List;
- importjava.util.Map;
- importjava.util.Set;
- importorg.dom4j.Document;
- importorg.dom4j.Element;
- importorg.dom4j.io.SAXReader;
- /**
- *解析xml工具类
- *@authorPCCW-80352891
- *
- */
- @SuppressWarnings("rawtypes")
- publicclassParseXMLUtil{
- /**entitymap对象,key:name,value:entity的属性map集**/
- publicMapentityMap;
- /**columnmap对象,key:entityName_colName,value:column的属性map集**/
- publicMapcolumnMap;
- /**rulemap对象,key:entityName_colName_ruleName,value:rule的map集:找到一行rule**/
- publicMapruleMap;
- /**rulesmap对象,key:entityName_colName,value:rules的map集:找到该column下所有的rule**/
- publicMapcolumnRulesMap;
- /**entity--columnmap:key:entityName,value:columnlist:根据实体类名得到所有的列**/
- publicMapcolumnListMap;
- /**columnlist**/
- publicListcolumnList;
- /**开始解析xml文件**/
- publicParseXMLUtil(FilexmlFilePath){
- FileInputStreamin=null;
- try{
- if(xmlFilePath==null){
- thrownewFileNotFoundException();
- }
- SAXReaderreader=newSAXReader();
- in=newFileInputStream(xmlFilePath);
- Documentdoc=reader.read(in);
- Elementroot=doc.getRootElement();
- IteratoritEntity=root.elements("entity").iterator();
- while(itEntity.hasNext()){
- Elemententity=(Element)itEntity.next();
- parseEntity(entity);
- }
- /**测试entityMap是否正确**/
- MapenMap=(Map)this.getEntityMap().get("用户表");
- Set<?>set=enMap.keySet();
- Iteratorit=set.iterator();
- while(it.hasNext()){
- Stringuu=(String)it.next();
- System.out.println("entityproperties:"+uu+"="+enMap.get(uu));
- }
- /**//**测试columnlist是否正确**//*
- ListcolList=(List)this.getColumnListMap().get("用户表");
- System.out.println("columnsize:"+colList.size());
- *//**测试columnMap是否正确**//*
- MapcolMap=(Map)this.getColumnMap().get("用户表_员工号");
- Set<?>coListSet=colMap.keySet();
- IteratorcoListIt=coListSet.iterator();
- while(coListIt.hasNext()){
- StringcoListKey=(String)coListIt.next();
- System.out.println("columnproperties:"+coListKey+"="+colMap.get(coListKey));
- }
- *//**测试ruleMap是否正确**//*
- if(this.getColumnRulesMap()!=null){
- ListrulesValidList=(List)this.getColumnRulesMap().get("用户表_员工号");
- for(inti=0;i<rulesValidList.size();i++){
- MapcolRuleMap=(Map)rulesValidList.get(i);
- StringruleName=(String)colRuleMap.get("name");
- MapruleMa=(Map)this.getRuleMap().get("用户表_员工号_"+ruleName);//eg:用户表_用户名_nullable
- Stringmess=(String)ruleMa.get("message");
- System.out.println("ValidateRules"+i+":"+mess);
- }
- }*/
- }catch(Exceptione){
- e.printStackTrace();
- }
- }
- /**开始解析entity**/
- @SuppressWarnings("unchecked")
- publicvoidparseEntity(Elemententity){
- if(entity!=null){
- /**对数据进行初始化设置**/
- columnListMap=newHashMap();
- columnMap=newHashMap();
- entityMap=newHashMap();
- ruleMap=newHashMap();
- columnRulesMap=newHashMap();
- columnList=newArrayList();
- setEntityMap(entity);
- StringentityName=entity.attributeValue("name");
- IteratoritColumn=entity.elements("column").iterator();
- while(itColumn.hasNext()){
- Elementcolumn=(Element)itColumn.next();
- setColumnMap(entityName,column);
- }
- columnListMap.put(entityName,columnList);
- }
- }
- /**将entity放入entityMap中**/
- @SuppressWarnings("unchecked")
- publicvoidsetEntityMap(Elemententity){
- Mapent=newHashMap();
- Stringname=entity.attributeValue("name");
- Stringcode=entity.attributeValue("code");
- ent.put("name",name);
- ent.put("code",code);
- entityMap.put(name,ent);
- }
- /**将column放入columnMap中**/
- @SuppressWarnings("unchecked")
- publicvoidsetColumnMap(StringentityName,Elementcolumn){
- if(column!=null){
- Mapcol=newHashMap();
- Stringname=column.attributeValue("name");
- Stringcode=column.attributeValue("code");
- Stringtype=column.attributeValue("type");
- col.put("name",name);
- col.put("code",code);
- col.put("type",type);
- StringcolumnMapKey=entityName+"_"+name;//eg:用户表_用户名
- columnMap.put(columnMapKey,col);
- columnList.add(col);
- IteratorruleIt=column.elements("rules").iterator();//获得rules
- while(ruleIt.hasNext()){
- Elementrules=(Element)ruleIt.next();
- Iteratorrule=rules.elements("rule").iterator();//获得rule
- while(rule.hasNext()){
- ElementruleValid=(Element)rule.next();//获得每一行rule
- setRuleMap(entityName,name,ruleValid);
- }
- }
- }
- }
- /**将rule验证规则放入ruleMap中**/
- @SuppressWarnings("unchecked")
- publicvoidsetRuleMap(StringentityName,StringcolumnName,ElementruleValid){
- if(ruleValid!=null){
- StringruleName=ruleValid.attributeValue("name");
- StringruleMsg=ruleValid.attributeValue("message");
- MapruleValidMap=newHashMap();
- ruleValidMap.put("name",ruleName);
- ruleValidMap.put("message",ruleMsg);
- StringruleStrKey=entityName+"_"+columnName+"_"+ruleName;
- StringcolStrKey=entityName+"_"+columnName;
- if(this.getColumnRulesMap().containsKey(colStrKey)){
- Listvalids=(List)this.getColumnRulesMap().get(colStrKey);
- valids.add(ruleValidMap);
- }else{
- Listvalids=newArrayList();
- valids.add(ruleValidMap);
- this.columnRulesMap.put(colStrKey,valids);//将每个column下的所有rules存入该map中
- }
- ruleMap.put(ruleStrKey,ruleValidMap);//将每个column下的一条rule存入该map中
- }
- }
- /**主方法**/
- publicstaticvoidmain(String[]args){
- Filefile=newFile("src/user.xml");
- newParseXMLUtil(file);
- }
- /**所有的getset方法**/
- publicMapgetEntityMap(){
- returnentityMap;
- }
- publicvoidsetEntityMap(MapentityMap){
- this.entityMap=entityMap;
- }
- publicMapgetColumnMap(){
- returncolumnMap;
- }
- publicvoidsetColumnMap(MapcolumnMap){
- this.columnMap=columnMap;
- }
- publicMapgetRuleMap(){
- returnruleMap;
- }
- publicvoidsetRuleMap(MapruleMap){
- this.ruleMap=ruleMap;
- }
- publicMapgetColumnRulesMap(){
- returncolumnRulesMap;
- }
- publicvoidsetColumnRulesMap(MapcolumnRulesMap){
- this.columnRulesMap=columnRulesMap;
- }
- publicMapgetColumnListMap(){
- returncolumnListMap;
- }
- publicvoidsetColumnListMap(MapcolumnListMap){
- this.columnListMap=columnListMap;
- }
- }
3.既然做导入,当然需要连接数据库啦。只需要在mysql数据库中,建立一个 名为 chat 的数据库,然后导入下面的sql.来创建一张user表
- CREATETABLE`user`(
- `status`varchar(20)defaultNULL,
- `port`int(10)NOTNULL,
- `ip`varchar(40)NOTNULL,
- `password`varchar(10)NOTNULL,
- `username`varchar(100)NOTNULL,
- `no`varchar(10)defaultNULL,
- `userImage`blob,
- PRIMARYKEY(`username`)
- )ENGINE=InnoDBDEFAULTCHARSET=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
mysql-connector-java-5.0.8-bin.jar
必须要在eclipse里引入他们。