java反射机制-excel导入-表头名字和顺序允许互换
如下excel表格,按照表头名字读取数据,允许表头增加无意义空格,允许表头顺序交换。
创新互联建站主要从事成都做网站、网站设计、外贸营销网站建设、网页设计、企业做网站、公司建网站等业务。立足成都服务西市,十多年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:028-86922220
序号 | 隧道左公里标 | 隧道右公里标 | 是否隧道 |
1 | DK2291.416 | DK0 | 是 |
2 | DK7389.65 | DK2291.416 | 否 |
3 | ZK2277 | ZK0 | 是 |
4 | ZK5235.68 | ZK2277 | 否 |
/** * * 读取指定sheet 页指定行数据 * * @param sheetIx 指定 sheet 页,从 0 开始 * @param start 指定开始行,从 0 开始 * @param end 指定结束行,从 0 开始 * @return * @throws Exception */ @SuppressWarnings({ "rawtypes", "unchecked" }) public List > readGeneric(int sheetIx, int start, int end, T t) throws Exception { Sheet sheet = workbook.getSheetAt(sheetIx); List
> list = new ArrayList
>(); if (end > getRowCount(sheetIx)) { end = getRowCount(sheetIx); } List
colNums = new ArrayList (); for (int i = start; i <= end; i++) { List rowList = new ArrayList (); Row row = sheet.getRow(i); List rowdata = getRowdata(row); Class clazz = t.getClass(); Object fa= clazz.newInstance(); Method method = t.getClass().getMethod("getLdcode", new Class[]{}); String[] ldcode = (String[])method.invoke(fa, new Object[]{}); //处理从0行开始取得请求 if(0 == start) { //处理请求的第0行,即表头 if(i == 0) { //通过反射获取模板类的实例 try { String simpleName = t.getClass().getSimpleName(); String StrEnumClass = simpleName.substring(0, simpleName.length()-5) + "Enum"; Class EnumClass = Class.forName("com.crscd.config.service.cbtc2.atp.excel.enums."+StrEnumClass); //Object enumInstance = EnumClass.newInstance(); for(int j = 0; j < rowdata.size(); j++) { //去除表头上空格、回车、换行符、制表符 if(null != rowdata.get(j) && !"".equals(rowdata.get(j))) { Pattern p = Pattern.compile("\\s*|\t|\r|\n"); String str = (String)rowdata.get(j); Matcher m = p.matcher(str); String excelData = m.replaceAll(""); //获取表头对应的工具类中ldcode位置 Method met = EnumClass.getMethod("getStatus", String.class); int col = (int) met.invoke(Object.class, excelData); colNums.add(col); }else { colNums.add(-1); } } }catch(ClassNotFoundException e) { //e.printStackTrace(); } } } for(int j = 0; j < colNums.size(); j++) { int k = j; //如果映射表头列和数据列不匹配,当数据列小于映射表头列时,说明excel有无效列,则不足的列用空串补足 if (colNums.size() > rowdata.size() ) { rowdata.add(""); } if(0 == start && !colNums.isEmpty()) { k = colNums.get(j); } if(k == -1) { continue; }else { try { method = t.getClass().getMethod("set"+ldcode[k], String.class); method.invoke(fa, rowdata.get(j)); //如果映射表头列和数据列不匹配,是excel多余无效行列,则越界,仍保留未越界之前的全部数据。 }catch(IndexOutOfBoundsException e) { continue; } } } rowList.add((T) fa); list.add(rowList); } return list; }
package com.crscd.config.service.cbtc2.atp.excel; public class TunnelUtilsimplements Comparable >,Cloneable{ private String[] ldcode = { "Id", "TunnelLeftKilo", "TunnelRightKilo", "IsTunnel" }; private String id; private String tunnelLeftKilo; private String tunnelRightKilo; private String isTunnel; public String[] getLdcode() { return ldcode; } public void setLdcode(String[] ldcode) { this.ldcode = ldcode; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getTunnelLeftKilo() { return tunnelLeftKilo; } public void setTunnelLeftKilo(String tunnelLeftKilo) { this.tunnelLeftKilo = tunnelLeftKilo; } public String getTunnelRightKilo() { return tunnelRightKilo; } public void setTunnelRightKilo(String tunnelRightKilo) { this.tunnelRightKilo = tunnelRightKilo; } public String getIsTunnel() { return isTunnel; } public void setIsTunnel(String isTunnel) { this.isTunnel = isTunnel; } @Override public int compareTo(TunnelUtils o) { if(Double.parseDouble(stringGetNum(tunnelLeftKilo)) Double.parseDouble(stringGetNum(o.getTunnelLeftKilo()))) { return 1; } return 0; } //字符串取数 public String stringGetNum(String str) { String str2 = ""; if(str != null && !"".equals(str)){ for(int i=0;i =48 && str.charAt(i)<=57) || str.charAt(i)==46){ str2+=str.charAt(i); } } return str2; } return str; } @Override public Object clone() { TunnelUtils stu = null; try{ stu = (TunnelUtils )super.clone(); }catch(CloneNotSupportedException e) { e.printStackTrace(); } return stu; } }
package com.crscd.config.service.cbtc2.atp.excel.enums; import java.util.regex.Pattern; public enum TunnelEnum { id("序号",0), tunnelLeftKilo("隧道左公里标",1), tunnelRightKilo("隧道右公里标",2), isTunnel("是否隧道",3); private final String msg; private final int status; TunnelEnum(String msg,int status) { this.msg = msg; this.status = status; } //动态匹配表头名字,允许加空,加无意义字符 public static int getStatus(String str) { str = str.replaceAll("\\(", "\\\\("); str = str.replaceAll("\\)", "\\\\)"); String pattern = "^"+str+".*"; for(TunnelEnum e : TunnelEnum.values()) { if(Pattern.matches(pattern, e.msg)) { return e.status; } } return -1; } }
excel的数据如下:
序号 | 隧道左公里标 | 隧道右公里标 | 是否隧道 |
1 | DK2291.416 | DK0 | 是 |
2 | DK7389.65 | DK2291.416 | 否 |
3 | ZK2277 | ZK0 | 是 |
4 | ZK5235.68 | ZK2277 | 否 |
网页题目:java反射机制-excel导入-表头名字和顺序允许互换
标题链接:http://pwwzsj.com/article/gipsgh.html