项目案例，将Excel数据批量导入到数据库

你在工作中是否遇到这样的问题？数据一般存放在Excel表中，逐条迁移到数据库中太麻烦，而且很多时候企业的数据量都是以万起步，单条导入显然不现实。那么该如何解决呢？

我们今天就给大家介绍一个用途非常广泛的功能：批量导入，在很多系统中，这也是必须实现的功能。而且当Excel表结构越复杂时，实现的难度就越高。

不管项目如何复杂，原理却基本相同，一般是前台页面选择Excel文件，后台获取后进行数据转化，然后循环执行Sql语句即可，了解这些原理后，一切也就变得简单。

下面为了重点演示，项目进行简化，只有批量导入功能，采用Struts2框架，将Excel数据导入到Mysql数据库中。

项目结构如下图：

Mysql数据库user表结构

Excel中数据

如果Excel中必填项数据为空，提示导入失败，报告错误信息及位置，项目演示图：

如果数据正确，提示导入数据库成功：

具体实现过程

首先是JSP页面，name的名称是重点：

<form action="${pageContext.request.contextPath }/excelsave.action" method="post" enctype="multipart/form-data">
    <input type="file" name="fileinput" multiple="multiple"/>
    <button type="submit" name="subimit">上传</button>
</form>

前台点击上传后跳转到action处理，action中首先定义：

//这里特别注意获取fileinput要和页面的导入时name属性一致。
private File fileinput;
//文件名用得到就获取，一般用不到。
private String fileinputFileName;
//下面是get和set方法，省略

然后在方法体中直接调用：

//参数为获取到的文件
ExcelUtil(fileinput);

ExcelUtil是处理Excel工具类，直接使用，代码如下：

/**解析excel 工具类*/
@SuppressWarnings("rawtypes")
public class ExcelUtil {
	
	public FileInputStream fis ;
	public HSSFWorkbook workBook;
	public HSSFSheet sheet;
	public XMLUtil 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 ExcelUtil(File excelFile){
		try {
			if(excelFile == null){
				throw new FileNotFoundException();
			}
		   fis = new FileInputStream(excelFile);		   
		   workBook = new HSSFWorkbook(fis);
		   parseXmlUtil = new XMLUtil();
		   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(Constans.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(Constans.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");
				   int intVal = 0;
				   if(xmlColType.equals("int")){
					   if(value != null) {
						   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);
			   }
			   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 listDatas){
	   
	   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 = "";
			   if(this.getStringCellValue(colCell)==null) {
				   //System.out.println("第"+curRow+"行,第"+curCol+"列:"+rulMsg);
			   }else {
				   cellValue = this.getStringCellValue(colCell).trim();
			   }
			   if(rulName.equals(Constans.RULE_NAME_NULLABLE)){		   
				   if(cellValue.equals("")||cellValue == null){
					   errorString.append("导入失败，错误信息：第"+curRow+"行,第"+curCol+"列:"+rulMsg+"<br>");
				   }
			   }
			//////这里写其他的验证规则。。。
			 
			}
		   
	   }
   }
   
   /**保存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 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 XMLUtil getParseXmlUtil() {
		return parseXmlUtil;
	}
	public void setParseXmlUtil(XMLUtil 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;
	}

}

项目中定义一个XML文件，主要做一些条件限制，比如用户名不能为空，email不能重复等，所以就有一个XML解析类：

/**解析xml工具类*/
@SuppressWarnings("rawtypes")
public class XMLUtil {

	 /**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 XMLUtil(){
		SAXReader reader = new SAXReader();
		InputStream in = getClass().getClassLoader().getResourceAsStream("user.xml");//读取文件流，Url为controller.xml文件
		try {
			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();
			}
		}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中
		}
	}
	


	/**所有的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;
	}
}

XML解析类定义完成后，ExcelUtil会调用处理，处理完成后返回，导入结束。

至于数据库连接，任何方式都行，这里不做要求，另外需要定义一个实体User，属性根据自己的业务设置。

以上就是具体实现过程，如果你有任何问题，欢迎留言，我们共同交流讨论。

还可以微信关注和置顶我的公众号“SL社区”(slshequ)，获取源码。