- 一、环境说明:win10专业版、jdk1.8.0_171、eclipse4.7.3a、Junit5
- 二、创建maven工程,引入pom依赖
复制代码
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<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.szcatic</groupId> <artifactId>import</artifactId> <version>0.0.1-SNAPSHOT</version> <dependencies> <!-- HSSFWorkbook所需jar包 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.0</version> </dependency> <!-- XSSFWorkbook所需jar包 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.0</version> </dependency> <!-- log4j2所需jar包 --> <dependency> <groupId>org.apache.logging.log4j</groupId> <artifactId>log4j-api</artifactId> <version>2.11.0</version> </dependency> <dependency> <groupId>org.apache.logging.log4j</groupId> <artifactId>log4j-core</artifactId> <version>2.11.0</version> </dependency> <dependency> <groupId>org.apache.logging.log4j</groupId> <artifactId>log4j-slf4j-impl</artifactId> <version>2.11.0</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.7.25</version> </dependency> <!-- junit5运行所需jar包 --> <dependency> <groupId>org.junit.jupiter</groupId> <artifactId>junit-jupiter-engine</artifactId> <version>5.2.0</version> <scope>test</scope> </dependency> <dependency> <groupId>org.junit.platform</groupId> <artifactId>junit-platform-runner</artifactId> <version>1.2.0</version> <scope>test</scope> </dependency> </dependencies> </project>
- 三、详细代码:
- 注解接口 复制代码1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16package com.szcatic.service; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * Excel注解定义 * @author zsx * @version 2018-09-27 */ @Target({ ElementType.METHOD, ElementType.FIELD }) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelField { // 列名 String name() default ""; }
- 实体类 复制代码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
75package com.szcatic.entity; import java.io.Serializable; import java.math.BigDecimal; import java.util.Date; import com.szcatic.service.ExcelField; /** * 用户实体类 * @author zsx * @version 2018-09-27 */ public class User implements Serializable { private static final long serialVersionUID = 1L; @ExcelField(name="用户名") private String userName; // 用户名 @ExcelField(name="密码") private String password; // 密码 @ExcelField(name="年龄") private Integer age; // 年龄 @ExcelField(name="性别") private String gender; // 性别 @ExcelField(name="邮箱") private String email; // 邮箱 @ExcelField(name="时间") private Date date; // 时间 @ExcelField(name="花费") private BigDecimal cost; // 花费 public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Date getDate() { return date; } public void setDate(Date date) { this.date = date; } public BigDecimal getCost() { return cost; } public void setCost(BigDecimal cost) { this.cost = cost; } @Override public String toString() { return "User [userName=" + userName + ", password=" + password + ", age=" + age + ", gender=" + gender + ", email=" + email + ", date=" + date + ", cost=" + cost + "]"; } }
- 工具类 复制代码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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323package com.szcatic.util; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.lang.annotation.Annotation; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.math.BigDecimal; import java.text.ParseException; 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 org.apache.commons.compress.utils.Lists; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.szcatic.service.ExcelField; /** * 导入工具类 * @author zsx * @version 2018-09-27 */ public class ExcelImportUtils { private static Logger logger = LoggerFactory.getLogger(ExcelImportUtils.class); private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); public static <E> List<E> readFile(File file, Class<E> clzz) { return readFile(file, clzz, ExcelField.class, "name"); } /** * 从文件读取数据 * @param file:Excel文件,第一行为列标题 * @param clzz:映射生成的实体类 * @param annotationClass:注解类 * @param methodName:注解类中对列应名的方法 * @return List */ public static <E, T extends Annotation> List<E> readFile(File file, Class<E> clzz, Class<T> annotationClass, String methodName) { Workbook wb = null; try { if (file == null || !validateExcel(file.getName())) { logger.error("文件为空或者不是Excel类型的文件"); return Lists.newArrayList(); } InputStream is; is = new FileInputStream(file); //创建工作表 if (isExcel2003(file.getName())) { wb = new HSSFWorkbook(is); }else { wb = new XSSFWorkbook(is); } return readExcel(clzz, wb, annotationClass, methodName); } catch (FileNotFoundException e) { e.printStackTrace(); return Lists.newArrayList(); } catch (IOException e) { e.printStackTrace(); return Lists.newArrayList(); }finally { if (wb != null) { try { wb.close(); } catch (IOException e) { e.printStackTrace(); } } } } /** * 读取Excel内容,默认第一行为标题行 * @param wb * @param file * @param map * @return */ private static <E, T extends Annotation> List<E> readExcel(Class<E> clzz, Workbook wb, Class<T> annotationClass, String methodName) { //获取实体类的所有属性 Field[] fields = clzz.getDeclaredFields(); Map<String, String> map = getFieldMap(fields, annotationClass, methodName); Sheet sheet = wb.getSheetAt(0); Row title = sheet.getRow(0); int totalColumns = title.getPhysicalNumberOfCells(); int totalRows = sheet.getPhysicalNumberOfRows(); //获取Excel所有的列标题 String[] titles = getColumnTitle(title, totalColumns); List<E> list = new ArrayList<>(); Cell cell; Row row; E e; //从第二行开始读取数据 for (int i = 1; i < totalRows; i++) { row = sheet.getRow(i); e = getNewInstance(clzz); for (int j = 0; j < totalColumns; j++) { cell = row.getCell(j); readCellContent(map.get(titles[j]), fields, cell, e); } list.add(e); } return list; } /** * 读取单元格内容,并将内容添加到实体类E中 * @param fieldName 当前单元格对应的Bean字段 * @param fields 属性数组 * @param cell 单元格 * @param e 实体类 */ private static <E> void readCellContent(String fieldName, Field[] fields, Cell cell, E e) { Object obj = getCellValue(cell); if (obj == null) { return; } mappingValueToBean(fieldName, fields, obj, e); } /** * 映射值到实体类 * @param fieldName * @param fields * @param obj * @param e */ private static <E> void mappingValueToBean(String fieldName, Field[] fields, Object obj, E e) { try { for (Field field : fields) { if(!fieldName.equals(field.getName())) { continue; } //设置私有属性可以访问 field.setAccessible(true); field.set(e, getValue(field, obj)); break; } }catch (IllegalArgumentException e1) { e1.printStackTrace(); }catch (IllegalAccessException e1) { e1.printStackTrace(); } } /** * 将obj的值转化为该属性类型的值 * @param field * @param obj * @return */ private static Object getValue(Field field, Object obj) { if(field.getType().equals(obj.getClass())) { return obj; } Object obj2 = null; try { if(Date.class.equals(field.getType())) { obj2 = sdf.parse(obj.toString()); }else if (String.class.equals(field.getType())) { obj2 = String.valueOf(obj); }else if (Long.class.equals(field.getType())) { obj2 = Long.valueOf(obj.toString()); }else if (Integer.class.equals(field.getType())) { obj2 = Integer.valueOf(obj.toString()); }else if (BigDecimal.class.equals(field.getType())) { obj2 = new BigDecimal(obj.toString()); }else if (Boolean.class.equals(field.getType())) { obj2 = Boolean.valueOf(obj.toString()); }else if (Float.class.equals(field.getType())) { obj2 = Float.valueOf(obj.toString()); }else if (Double.class.equals(field.getType())) { obj2 = Double.valueOf(obj.toString()); } } catch (ParseException e) { e.printStackTrace(); } return obj2; } /** * 获取单元格的值 * @param cell * @return Object */ private static Object getCellValue(Cell cell) { Object obj; // 以下是判断数据的类型 switch (cell.getCellType()) { case NUMERIC : // 数字 obj = cell.getNumericCellValue(); if (HSSFDateUtil.isCellDateFormatted(cell)) { obj = HSSFDateUtil.getJavaDate((double) obj); } break; case STRING : // 字符串 obj = cell.getStringCellValue(); break; case BOOLEAN : // Boolean obj = cell.getBooleanCellValue(); break; case FORMULA : // 公式 obj = cell.getCellFormula(); break; case BLANK : // 空值 obj = null; break; case ERROR : // 故障 obj = "非法字符"; break; default: obj = "未知类型"; break; } return obj; } /** * 通过反射获取T类的新实例 * @param clzz * @return T */ private static <T> T getNewInstance(Class<T> clzz) { T t = null; try { t = clzz.newInstance(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } return t; } /** * 获取列标题 * @param title 列标题所在行 * @param totalColumns 总列数 * @return String[] */ private static String[] getColumnTitle(Row title, int totalColumns) { String[] titles = new String[totalColumns]; for (int j = 0; j < totalColumns; j++) { titles[j] = title.getCell(j).getStringCellValue(); } return titles; } /** * 获取属性和注解对应的集合 * @param clzz * @param annotationClass * @param methodName * @return Map : key为属性上的注解值,value为属性名 */ private static <T extends Annotation> Map<String, String> getFieldMap(Field[] fields, Class<T> annotationClass, String methodName) { Map<String, String> map = new HashMap<>(); T t; for (Field field : fields) { //获取属性上T类型的注解 if(field.isAnnotationPresent(annotationClass)) { t = field.getAnnotation(annotationClass); map.put(String.valueOf(getMethodReturnValue(t, methodName)), field.getName()); } } return map; } /** * 获取方法的返回值 * @param T 实体类 * @param methodName 方法名 * @return Object */ private static <T> Object getMethodReturnValue(T t, String methodName) { Object obj = null; try { obj = t.getClass().getMethod(methodName).invoke(t); }catch (NoSuchMethodException e) { e.printStackTrace(); }catch (SecurityException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } return obj; } /** * 验证是否是Excel格式的文件 * @param fileName:文件名 * @return boolean : true表示是Excel格式的文件,false表示不是 */ private static boolean validateExcel(String fileName) { if (fileName == null || !(isExcel2003(fileName) || isExcel2007(fileName))) { return false; } return true; } /** * 判断是不是2003格式的Excel * @param fileName * @return boolean : true表示是2003格式的Excel,false表示不是 */ private static boolean isExcel2003(String fileName) { return fileName.matches("^.+\.(?i)(xls)$"); } /** * 判断是不是2007格式的Excel * @param fileName * @return boolean : true表示是2007格式的Excel,false表示不是 */ private static boolean isExcel2007(String fileName) { return fileName.matches("^.+\.(?i)(xlsx)$"); } }
-
l测试类
复制代码1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22package com.szcatic.test; import java.io.File; import java.util.List; import org.junit.jupiter.api.Test; import com.szcatic.entity.User; import com.szcatic.util.ExcelImportUtils; /** * 导入Excel工具类测试类 * @author zsx * @version 2018-09-27 */ public class ExcelImportUtilsTest { @Test void testReadFile() { File file = new File("D:/newProject/export.xls"); List<User> list = ExcelImportUtils.readFile(file, User.class); System.out.println(list); File file2 = new File("D:/newProject/export.xls"); List<User> list2 = ExcelImportUtils.readFile(file2, User.class); System.out.println(list2); } }
-
运行结果:
复制代码1
2
3[User [userName=zhangsan, password=1234, age=20, gender=男, email=zhangsan@163.com, date=Thu Sep 27 00:00:00 CST 2018, cost=8555.55], User [userName=lisi, password=1234, age=25, gender=男, email=lisi@163.com, date=Thu Sep 27 00:00:00 CST 2018, cost=6666.66]] [User [userName=zhangsan, password=1234, age=20, gender=男, email=zhangsan@163.com, date=Thu Sep 27 00:00:00 CST 2018, cost=8555.55], User [userName=lisi, password=1234, age=25, gender=男, email=lisi@163.com, date=Thu Sep 27 00:00:00 CST 2018, cost=6666.66]]
-
补充说明:
项目结构
Excel表格内容
最后
以上就是健忘酒窝最近收集整理的关于导入Excel时,根据注解生成实体类的全部内容,更多相关导入Excel时内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
发表评论 取消回复