Excel 导入工具类

设计思路：平常我们导入的话，获取到表格单元格值 之后，采用new 对象出来，然后把 值赋予到 对象上，但是 此方式有个局限性就是若是 下次导入的对象与上次的不同，那么还得 再写一遍赋值代码，所以 在 重复利用率不高，因此 打算用 java 反射来实现 动态赋值，跳脱出 类型的限制；

那么 反射的使用要知道 对象名与属性名的，因此 我打算  写一个 配置文件 用来  连接（过度）Excel 与 对象 之间的 映射关系；然后通过此作为桥梁 实现反射生成对象

PS:使用 refect 的目的 是为了调高 利用性，但是 对象采用 反射机制生成 效率要低于 直接new,效率要低于一倍左右;

所以此方法 适用于 数据量不太的情况下

java 类目录结构：

Xml 文件：

<?xml version="1.0" encoding="UTF-8"?><root><!-- 规则信息：BusinessRule  id：实体类路径 --><improtExcel id="com.test.data.BusinessRule" name="ruleImport"><mapping type="String" excelTitle="规则名称" property="name" required="true"></mapping><mapping type="String" excelTitle="Bps范围" property="bps_range" required="false"></mapping><mapping type="String" excelTitle="Pps范围" property="pps_range" required="false"></mapping><mapping type="String" excelTitle="协议范围" property="prot" required="true"></mapping><mapping type="String" excelTitle="源端口" property="src_port" required="false"></mapping><mapping type="String" excelTitle="目的端口" property="dest_port" required="false"></mapping><mapping type="String" excelTitle="时间资源" property="time_res_id" required="true"></mapping><mapping type="String" excelTitle="是否启用" property="is_on" required="true"></mapping><mapping type="String" excelTitle="动作是否允许" property="rosterFlag" required="true"></mapping><mapping type="String" excelTitle="源ip范围" property="srcIpRange" required="false"></mapping><mapping type="String" excelTitle="目的ip范围" property="dstIpRange" required="false"></mapping></improtExcel><improtExcel id="com.test.data.BusinessModel" name="ipAreaImport"><mapping type="String" excelTitle="区域名称" property="name" required="true"></mapping><mapping type="String" excelTitle="资产分析是否启用" property="isAnaly" required="true"></mapping><mapping type="String" excelTitle="描述" property="description" required="false"></mapping><!--地址内容 用一些字段 先临时存<mapping type="String" excelTitle="地址内容" property="content" required="true"></mapping> --><mapping type="String" excelTitle="IP地址" property="creator" required="false"></mapping><mapping type="String" excelTitle="地址范围" property="createtime" required="false"></mapping><mapping type="String" excelTitle="子网地址" property="updatetime" required="false"></mapping></improtExcel></root>

存 xml信息的实体类：

package com.test.util.excelutil;/** *  * @author zhangqingzhou * 规则导入Excel 时 , 导入 信息 的 条件 储存类（即要求） */public class MappingBean {/** * excel 表 列名 */private String excelTitle;/** * 对应到 实体类 上的 属性值 */private String property;/** * 字段值类型 */private String type;/** * 是否 为 必须字段 */private boolean required;/** * 最小长度 */private int minLength;/** * 最大长度 */private int maxLength;private String mapName;private String mapDefaultValue;/** * 注解名 */private String annoName;/** * 反射到的 类 */private String className;/** * 方法名 */private String methodName;public String getExcelTitle() {return excelTitle;}public void setExcelTitle(String excelTitle) {this.excelTitle = excelTitle;}public String getProperty() {return property;}public void setProperty(String property) {this.property = property;}public String getType() {return type;}public void setType(String type) {this.type = type;}public boolean isRequired() {return required;}public void setRequired(boolean required) {this.required = required;}public int getMinLength() {return minLength;}public void setMinLength(int minLength) {this.minLength = minLength;}public int getMaxLength() {return maxLength;}public void setMaxLength(int maxLength) {this.maxLength = maxLength;}public String getMapName() {return mapName;}public void setMapName(String mapName) {this.mapName = mapName;}public String getMapDefaultValue() {return mapDefaultValue;}public void setMapDefaultValue(String mapDefaultValue) {this.mapDefaultValue = mapDefaultValue;}public String getAnnoName() {return annoName;}public void setAnnoName(String annoName) {this.annoName = annoName;}public String getClassName() {return className;}public void setClassName(String className) {this.className = className;}public String getMethodName() {return methodName;}public void setMethodName(String methodName) {this.methodName = methodName;}}

读取xml 信息放入 容器中：

package com.test.util.excelutil;import java.io.File;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.dom4j.Document;import org.dom4j.DocumentException;import org.dom4j.Element;import org.dom4j.io.SAXReader;import com.test.excelImportUtil.ExcelMappingBean;import com.test.util.excelImportUtil.StringUtil;/** * 初始化映射 工具类 * @author Administrator * */public class RuleImportMappingUtil {private static RuleImportMappingUtil singleton = null;private Map<String, MappingBean> mappingBeanMap = new HashMap<String, MappingBean>();private Map<String, List<String>> titlesMap = new HashMap<String, List<String>>();public static RuleImportMappingUtil getInstance(){if(singleton == null){singleton = getSyncInstance();}return singleton;}/** * 线程安全 获取实例 * @return */private static synchronized RuleImportMappingUtil getSyncInstance(){return new RuleImportMappingUtil();}private RuleImportMappingUtil(){initExcelInfo();};public static void main(String[] args) {new RuleImportMappingUtil().initExcelInfo();}/** * 初始化 参数 读取 xml 文件，初始化 信息 */private void initExcelInfo(){

// xml 文件路径//E:/gyy/cupid_tomcat_7.0.53/webapps/nta/WEB-INF/_conf/nta/utilString webinfo = "E:/gyy/cupid_tomcat_7.0.53/webapps/nta/WEB-INF/_conf";String fileName = webinfo + "/nta/util/importExcelMappingRule.xml";SAXReader reader = new SAXReader();Document document;try {document = reader.read(new File(fileName));Element root = document.getRootElement();List<Element> excelEles = root.elements();for(Element excelEle : excelEles){String id = excelEle.attributeValue("id");String alias = excelEle.attributeValue("name");List<Element> mappingEles = excelEle.elements();List<String> titles = new ArrayList<String>();for(Element mappingEle : mappingEles){MappingBean excelMapping = new MappingBean();excelMapping.setExcelTitle(mappingEle.attributeValue("excelTitle"));excelMapping.setProperty(mappingEle.attributeValue("property"));excelMapping.setRequired(Boolean.valueOf(mappingEle.attributeValue("required")));titles.add(excelMapping.getExcelTitle());excelMapping.setType(mappingEle.attributeValue("type"));if(StringUtil.strIsNull(alias)){mappingBeanMap.put(id + "_" + excelMapping.getExcelTitle(), excelMapping);}else{mappingBeanMap.put(id + "$" + alias + "_" + excelMapping.getExcelTitle(), excelMapping);}}if(!titles.isEmpty()){if(StringUtil.strIsNull(alias)){titlesMap.put(id, titles);}else{titlesMap.put(id + "$" + alias + "_", titles);}}}} catch (DocumentException e) {e.printStackTrace();} }/** *  * @param packagePath  包路径 * @param name    总文件名  约定 * @param excelTitle  列的标题 * @return */public MappingBean  getMappingBean(String packagePath , String name , String excelTitle){if(name == null || name.equals("")){return mappingBeanMap.get(packagePath + "_" + excelTitle);}else{return mappingBeanMap.get(packagePath  + "$" + name + "_" + excelTitle);}}/** * 获取 所有的 字段值-中文含义 * @param packagePath * @param name * @return */public List<String>  getAllTitles(String packagePath , String name ){if(name == null || name.equals("")){return titlesMap.get(packagePath);}else{return titlesMap.get(packagePath + "$" + name +"_");}}/** * 检验 Excel 格式是否正确 * @param packagePath * @param name * @param titles * @return   * true:列名合法   * false：列名不合法 */public boolean checkTitleIsright(String packagePath , String name ,String[] titles){boolean flag = true;List<String> allTitles = getAllTitles(packagePath, name);if(allTitles != null){for (String string : titles) {boolean flag2 = false;for (String str : allTitles) {if(string.equals(str)){flag2 = true;break;}}// 遍历之后 还是没有发现if(flag2 == false){return false;}}}return flag;}}

反射工具类：

package com.tst.util.excelutil;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.util.Map;/* * zhangqingzhou * 动态 导入过程，表值与对象 的映射 */public class ReflectUtil {public static Object reflectBean(Class<?> fclass , Map<String , Object> map){// 得到 此类的所有属性Field[] fileds = fclass.getDeclaredFields();Object bean = null;try {bean = fclass.newInstance();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}// 遍历类的属性 进行赋值for (Field field : fileds) {String filedName = null;// 获取方法名filedName = field.getName();if(filedName != null){// 过滤 序列化 字段if(filedName.equals("serialVersionUID")){continue;}// 进行 赋值操作try {if(map.containsKey(filedName)){ // 1.1 先 获取 此字段的 类型Class<?> type = field.getType();Method setMethod = fclass.getMethod(getSetMethod(filedName), type);setMethod.invoke(bean, map.get(filedName));}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}}return bean;}/** * 获取 set方法名 * @param fieldName * @return */public static String getSetMethod(String fieldName){String method = null;if(fieldName != null && fieldName.length() > 0){byte[] bytes = fieldName.getBytes();// + 32 变大写bytes[0] = (byte)( bytes[0] - 'a' + 'A');method = "set" + new String(bytes);}return method;}/** * 获取 Get方法名 * @param fieldName * @return */public static String getGetMethod(String fieldName){String method = null;if(fieldName != null && fieldName.length() > 0){byte[] bytes = fieldName.getBytes();// + 32 变大写bytes[0] = (byte)( bytes[0] - 'a' + 'A');method = "get" + new String(bytes);}return method;}}

读取excel的 controller:

@SuppressWarnings({ "unchecked" })@Log(isEnabled=false)@At("/import")@AdaptBy(type = UploadAdaptor.class, args = {})@Ok("jsp:jsp.nta.rule.business_rule")@Fail("jsp:jsp.nta.rule.lead_bug")public void importResourse(Ioc ioc,HttpServletRequest request,HttpServletResponse response,@Param("filename") TempFile tempFile,@Param("groupIdTemp") String groupId) throws Exception{boolean bug=false;// 存储消息Map<Integer, String> returnMap = new HashMap<Integer, String>();FieldMeta meta=tempFile.getMeta();String fileName = meta.getFileLocalName();//判断文件类型是否合法，支持excel2003、2007if(!(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))){request.setAttribute("fileError", "导入失败，文件格式不正确，只支持.xls和.xlsx后缀文件！");request.setAttribute("returnMapErr",returnMap);throw ExceptionWrapper.wrapError(new EC("导入失败，文件格式不正确，只支持.xls和.xlsx后缀文件！", false));}System.out.println("groupId:"+groupId);FileInputStream inputStream = null;Workbook workbook = null;Sheet sheet = null;try {inputStream = new FileInputStream(tempFile.getFile());//创建workbookworkbook = WorkbookFactory.create(inputStream);sheet = workbook.getSheet("Rule");if(sheet != null){List<BusinessRule> dataList = (List<BusinessRule>)ExcelUtil.parseExcelData(BusinessRule.class, sheet, "ruleImport", returnMap);for (Map.Entry<Integer, String> entry : returnMap.entrySet()) {if(entry.getValue().indexOf("<BR/>")==-1){request.setAttribute("returnMapErr",returnMap);bug=true;throw ExceptionWrapper.wrapError(new EC("第"+entry.getKey()+"行未成功导入，原因是：" + entry.getValue(), false));}}if(!bug){System.out.println("dataSize:"+dataList.size());if(dataList != null){// 对数据 进行判断 过滤  正确性验证List<BusinessRule> beanList = ExcelUtil.wrapDataRule(dataList, returnMap, getConnectInfo(), unitMap);for (Map.Entry<Integer, String> entry : returnMap.entrySet()) {if(entry.getValue().contains("重新导入")){request.setAttribute("returnMapErr",returnMap);throw ExceptionWrapper.wrapError(new EC("第"+entry.getKey()+"行未成功导入，原因是：" + entry.getValue(), false));}}// 添加数据for (BusinessRule obj : beanList) {obj.setAlert_level(5);obj.setStandby1(groupId);//规则组ipobj.setTotal_doctets_end_unit("");obj.setPkg_len_range("");obj = filterPortByprotocol(obj);businessRuleService.insert(obj, "");}}}}else {throw ExceptionWrapper.wrapError(new EC("导入失败，请使用正确的模板文件填写数据！", false));}}catch (Exception e) {throw ExceptionWrapper.wrapError(new EC("导入失败，请使用正确的模板文件填写数据！", false));}}

读取Excel 的工具类：

package com.venustech.tsoc.cupid.apm.rule.util.excelutil;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.regex.Matcher;import java.util.regex.Pattern;import javax.servlet.http.HttpServletRequest;import org.apache.cxf.binding.corba.wsdl.Array;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;/** * Excel 工具箱 * @author zqz * */public class ExcelUtil {// 组合方式 获取 映射工具private static RuleImportMappingUtil  mappingUtil = RuleImportMappingUtil.getInstance();private static final DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");/** *  * @param fclass  被反射的实体类 * @param sheet  Excel 表单 * @param name  名字 * @param returnMap  异常Map * @return List<?> * @throws Exception */public static List<?>  parseExcelData(Class<?>  fclass , Sheet  sheet ,String name ,Map<Integer,String>  returnMap)throws Exception{// 泛型的使用List<Object> dataList =new ArrayList<Object>();int rowIndex = 0;try {// 开始 行数int startRow = sheet.getFirstRowNum();// 结尾行数int lastRow = sheet.getLastRowNum();// 验证是否为 空内容if(startRow >= lastRow){// 添加 提示消息returnMap("0","文件为空！");return null;}// 获取 第一行 的列标题String[] titles = getExcelContext(sheet.getRow(startRow));if(titles == null || titles.length == 0){return dataList;}// 验证 标题是否存在if(!mappingUtil.checkTitleIsright(fclass.getName(), name, titles)){returnMap.put(0, "表格格式不对(表格列名不合法),请使用模板重新导入！");return dataList;}// 开始遍历取值Row row = null;Cell cell = null;String cellValue = null;// 设置 容器 存值MappingBean  mappingBean = null;// 映射类Map<String ,Object> rowMap = new HashMap<String, Object>();//每一行的数据//1.1 遍历所有数据 行for(int rowNum = startRow+1;rowNum <= lastRow; rowNum++ ){  row =sheet.getRow(rowNum);  if(row == null){  continue;  }  // 1.2 遍历列  short lastCellNum = row.getLastCellNum();  for(short cellNum = 0; cellNum <=lastCellNum; cellNum++ ){  // 1.3 取单元格的值 cell = row.getCell(cellNum);  if(cell != null){ // 统一值的 类型，方便存储 cell.setCellType(Cell.CELL_TYPE_STRING); cellValue = cell.getStringCellValue(); if(cellValue != null){ // 去除两边空格 cellValue = cellValue.trim(); }else{ cellValue = ""; }  // 与xml 文件进行匹配，找到 此字段的信息 String title = titles[cellNum]; mappingBean = mappingUtil.getMappingBean(fclass.getName(), name, title); if(mappingBean != null){ if(title.equals("是否启用") || title.equals("动作是否允许") || title.equals("资产分析是否启用")){ cellValue = (cellValue.equals("是")?"1":(title.contains("是否启用")?"0":"2")); rowMap.put(mappingBean.getProperty(), Integer.parseInt(cellValue)); }else{  rowMap.put(mappingBean.getProperty(), cellValue); } }  }  // 清空 数据 cell = null; cellValue = null; mappingBean = null;  }  // 把一行的数据 反射生成队形  dataList.add(ReflectUtil.reflectBean(fclass, rowMap));   //清空行数据  rowMap.clear();    rowIndex++;  row = null;  }} catch (Exception e) {returnMap.put("0","文件导入失败");}return dataList;}/** * 获取 此行所有列的内容 * @param row * @return */private static String[]  getExcelContext(Row row){short firstCellNum = row.getFirstCellNum();//1short lastCellNum = row.getLastCellNum();//10String[] contexts = new String[lastCellNum];// 遍历取值for(short index = firstCellNum; index < lastCellNum ;index++){// 为了 方便对应，1-1  ，2--2Cell cell = row.getCell(index);if(cell != null){contexts[index] = cell.getStringCellValue();}}return contexts;}}

这样就避免 每导入一次，死板new 对象的问题了！