基于 Java 泛型和反射 导入 Excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.3.RELEASE</version>
</parent>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-autoconfigure</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
private Workbook workbook;
public List<T> importExcel(String sheetName, File f, Class<T> item) {
List<T> list = null;
try {
if(f.getName().endsWith("xlsx")){
workbook = new XSSFWorkbook(new FileInputStream(f));
}else{
workbook = new HSSFWorkbook(new FileInputStream(f));
}
Sheet sheet = workbook.getSheet(sheetName);
if (!"".equals(sheetName.trim())) {
sheet = workbook.getSheet(sheetName);// 如果指定sheet名,则取指定sheet中的内容.
}
if (sheet == null) {
sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet.
}
// 获取数据
list = dispatch(sheet, item);
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
/** 生成实体类的 List<T>
*
由于采取反射技术，所以要传入 实体类的类型 ,例如Entity.class
*
*/
public List<T> dispatch(Sheet sheet, Class<T> clazz) {
List<T> instances = new ArrayList<>();
List<Map<String, String>> sheetValue = parseExcelSheet(sheet);
for (int i = 0; i < sheetValue.size(); i++) {
Map<String, String> map = sheetValue.get(i);
Field[] fields = clazz.getDeclaredFields();
try {
T t2 = clazz.newInstance();
for (Field field : fields) {
field.setAccessible(true);
Type type = field.getType();
field.set(t2, getTypeValue(map.get(field.getName()), type.getTypeName()));
}
instances.add(t2);
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
}
}
return instances;
}
//赋值判断类型
public Object getTypeValue(String value, String type) {
Object obj = new Object();
if ("int".equals(type) || type.indexOf("Integer") > -1) {
obj = Integer.valueOf(value);
} else if ("short".equals(type) || type.indexOf("Short") > -1) {
obj = Short.valueOf(value);
} else if ("long".equals(type) || type.indexOf("Long") > -1) {
obj = Long.valueOf(value);
} else if ("float".equals(type) || type.indexOf("Float") > -1) {
obj = Float.valueOf(value);
} else if ("double".equals(type) || type.indexOf("Double") > -1) {
obj = Double.valueOf(value);
} else if ("boolean".equals(type) || type.indexOf("Boolean") > -1) {
obj = Boolean.valueOf(value);
} else {
obj = value;
}
return obj;
}
/**
* 解析Excel第一行，并生成以第一行表头为key，每一行的值为value的map
* 例如: excel内容如下
* id
alarm
1
abcd1
2
abcd2
3
abcd3
那么List<Map>就是的内容就是
List: map1( id: 1, alarm: abcd1 ),map2(( id: 2, alarm: abcd2 ).....
*/
public List<Map<String, String>> parseExcelSheet(Sheet sheet) {
List<Map<String, String>> result = new ArrayList<>();
Map<String, String> rowValue = null;
int rows = sheet.getPhysicalNumberOfRows();
String[] headers = getHeaderValue(sheet.getRow(0));
for (int i = 1; i < rows; i++) {
rowValue = new HashMap<>();
Row row = sheet.getRow(i);
for (int kk = 0; kk < headers.length; kk++) {
rowValue.put(headers[kk], String.valueOf(getCellValue(row.getCell(kk))));
}
result.add(rowValue);
}
return result;
}
/** 获取第一行，表头，也就是实体类的字段，支持中英文，及下划线，忽略大小写，但是绝笔不能重复，表头有重复字段则不能解析
**/
private String[] getHeaderValue(Row rowHeader) {
int colNum = rowHeader.getPhysicalNumberOfCells();
String[] headValue = new String[rowHeader.getPhysicalNumberOfCells()];
for (int i = 0; i < colNum; i++) {
headValue[i] = rowHeader.getCell(i).getStringCellValue();
}
return headValue;
}
@SuppressWarnings("deprecation")
public Object getCellValue(Cell cell) {
Object value = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
// 如果为时间格式的内容
if (DateUtil.isCellDateFormatted(cell)) {
// 注：format格式 yyyy-MM-dd hh:mm:ss
// 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss");
value = sdf.format( DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
break;
} else {
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
value = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
value = cell.getCellFormula() + "";
break;
case Cell.CELL_TYPE_BLANK: // 空值
value = "";
break;
case Cell.CELL_TYPE_ERROR: // 故障
value = "非法字符";
break;
default:
value = "未知类型";
break;
}
return value;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
/**
*/
private int id;
/**
*/
private String alarm;
/**
* 获取 主键
*/
public int getId() {
return id;
}
/**
* 设置 主键
*/
public void setId(int id) {
this.id = id;
}
/**
* 获取
*/
public String getAlarm() {
return alarm;
}
/**
* 设置
*/
public void setAlarm(String alarm) {
this.alarm = alarm;
}
@Override
public String toString() {
return "Alarm [id=" + id + ", alarm=" + alarm + "]";
}

1
2
3
4
5
6
7
8
9
10
11
12
@Test
public void testImport() {
ExcelUtil2<Alarm> excelUtil = new ExcelUtil2<>() ;
//"alarm"为excel 的 sheet名字，也就是表名，暂时未做批量功能
List<Alarm> acfForms = excelUtil.importExcel("alarm",
new File(path)
, Alarm.class);
for (Alarm a : acfForms) {
System.out.println(a.toString());
}
}