我是靠谱客的博主 健忘酒窝,这篇文章主要介绍导入Excel时,根据注解生成实体类,现在分享给大家,希望可以做个参考。

  • 一、环境说明: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. 注解接口
    复制代码
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    package 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 ""; }

     

  2. 实体类
    复制代码
    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
    package 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 + "]"; } }

     

  3. 工具类
    复制代码
    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
    323
    package 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)$"); } }

     

  4. l测试类

    复制代码
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    package 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); } }

     

  5. 运行结果:

    复制代码
    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时内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
点赞(119)

评论列表共有 0 条评论

立即
投稿
返回
顶部