The following excel table reads the data according to the header name, allows the header to add meaningless spaces, and allows the header to exchange in order.
| Serial number | Left kilometer mark of tunnel | Right kilometer mark of tunnel | Is the tunnel? |
| 1 | DK2291.416 | DK0 | yes |
| 2 | DK7389.65 | DK2291.416 | no |
| 3 | ZK2277 | ZK0 | yes |
| 4 | ZK5235.68 | ZK2277 | no |
/**
*
* Read specified sheet page specified row < T > Data
*
* @param sheetIx Specify sheet page, starting from 0
* @param start Specify start line, starting at 0
* @param end Specify end line, starting at 0
* @return
* @throws Exception
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public List<List<T>> readGeneric(int sheetIx, int start, int end, T t) throws Exception {
Sheet sheet = workbook.getSheetAt(sheetIx);
List<List<T>> list = new ArrayList<List<T>>();
if (end > getRowCount(sheetIx)) {
end = getRowCount(sheetIx);
}
List<Integer> colNums = new ArrayList<Integer>();
for (int i = start; i <= end; i++) {
List<T> rowList = new ArrayList<T>();
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[]{});
//Process request from row 0
if(0 == start)
{
//Processing request row 0, which is the header
if(i == 0)
{
//Get instance of template class by reflection
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++)
{
//Remove spaces, carriage returns, line breaks, and tabs on the header
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("");
//Get the ldcode position in the tool class corresponding to the header
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;
//If the mapping header column and data column do not match, when the data column is smaller than the mapping header column, it means that excel has invalid columns, and the insufficient columns are filled with empty strings
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));
//If the mapping header column does not match the data column, it is an extra invalid column in excel. If it is out of bounds, all the data before it is still retained.
}catch(IndexOutOfBoundsException e) {
continue;
}
}
}
rowList.add((T) fa);
list.add(rowList);
}
return list;
}package com.crscd.config.service.cbtc2.atp.excel;
public class TunnelUtils<T> implements Comparable<TunnelUtils<T>>,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<T> o) {
if(Double.parseDouble(stringGetNum(tunnelLeftKilo))<Double.parseDouble(stringGetNum(o.getTunnelLeftKilo()))) {
return -1;
}
if(Double.parseDouble(stringGetNum(tunnelLeftKilo))>Double.parseDouble(stringGetNum(o.getTunnelLeftKilo()))) {
return 1;
}
return 0;
}
//String access
public String stringGetNum(String str) {
String str2 = "";
if(str != null && !"".equals(str)){
for(int i=0;i<str.length();i++){
if((str.charAt(i)>=48 && str.charAt(i)<=57) || str.charAt(i)==46){
str2+=str.charAt(i);
}
}
return str2;
}
return str;
}
@Override
public Object clone() {
TunnelUtils<T> stu = null;
try{
stu = (TunnelUtils<T>)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("Serial number",0),
tunnelLeftKilo("Left kilometer mark of tunnel",1),
tunnelRightKilo("Right kilometer mark of tunnel",2),
isTunnel("Is the tunnel?",3);
private final String msg;
private final int status;
TunnelEnum(String msg,int status)
{
this.msg = msg;
this.status = status;
}
//Dynamically match the header name. It is allowed to add blank and meaningless characters
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;
}
}The data of excel is as follows:
| Serial number | Left kilometer mark of tunnel | Right kilometer mark of tunnel | Is the tunnel? |
| 1 | DK2291.416 | DK0 | yes |
| 2 | DK7389.65 | DK2291.416 | no |
| 3 | ZK2277 | ZK0 | yes |
| 4 | ZK5235.68 | ZK2277 | no |