Java 运用poi导入excel，结合xml文件进行数据验证的例子(增加了jar包)
Java 使用poi导入excel，结合xml文件进行数据验证的例子(增加了jar包)
2.ParseXMLUtil.java
3.既然做导入，当然需要连接数据库啦。只需要在mysql数据库中，建立一个 名为 chat 的数据库,然后导入下面的sql.来创建一张user表
假设现在要做一个通用的导入方法：
要求：
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里引入他们。