如下excel表格，按照表头名字读取数据，允许表头增加无意义空格，允许表头顺序交换。
创新互联建站主要从事成都做网站、网站设计、外贸营销网站建设、网页设计、企业做网站、公司建网站等业务。立足成都服务西市,十多年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:028-86922220
| 序号 | 隧道左公里标 | 隧道右公里标 | 是否隧道 |
| 1 | DK2291.416 | DK0 | 是 |
| 2 | DK7389.65 | DK2291.416 | 否 |
| 3 | ZK2277 | ZK0 | 是 |
| 4 | ZK5235.68 | ZK2277 | 否 |
实现方法如下，充分利用java反射机制，巧妙运用正则表达式进行表头名字匹配，提取配置表格文件顺序，模糊动态匹配表头名字和顺序。
/**
*
* 读取指定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 TunnelUtils implements 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://jkwzsj.com/article/gipsgh.html
