我们今天就给大家介绍一个用途非常广泛的功能：批量导入，在很多系统中，这也是必须实现的功能。而且当Excel表结构越复杂时，实现的难度就越高。
不管项目如何复杂，原理却基本相同，一般是前台页面选择Excel文件，后台获取后进行数据转化，然后循环执行Sql语句即可，了解这些原理后，一切也就变得简单。
下面为了重点演示，项目进行简化，只有批量导入功能，将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，属性根据自己的业务设置。
以上就是具体实现过程，如果有任何问题，私信，我们共同交流讨论。
