导入Excel时，根据注解生成实体类

96 阅读 0 评论 64 点赞

我是靠谱客的博主健忘酒窝，最近开发中收集的这篇文章主要介绍导入Excel时，根据注解生成实体类，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、环境说明：win10专业版、jdk1.8.0_171、eclipse4.7.3a、Junit5
二、创建maven工程，引入pom依赖

<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>

三、详细代码：

注解接口

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 "";
}

实体类

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 + "]";
}
}

工具类

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)$");
}
}

l测试类

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);
}
}

运行结果：

[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]]