设计思路:平常我们导入的话,获取到表格单元格值 之后,采用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/util
String 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 block
e.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 block
e.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、2007
if(!(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());
//创建workbook
workbook = 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);//规则组ip
obj.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();//1
short lastCellNum = row.getLastCellNum();//10
String[] contexts = new String[lastCellNum];
// 遍历取值
for(short index = firstCellNum; index < lastCellNum ;index++){
// 为了 方便对应,1-1 ,2--2
Cell cell = row.getCell(index);
if(cell != null){
contexts[index] = cell.getStringCellValue();
}
}
return contexts;
}
}
这样就避免 每导入一次,死板new 对象的问题了!