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

• 三、详细代码：

1. 注解接口

   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. 实体类

   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. 工具类

   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测试类

   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. 运行结果：

   [User [userName=zhangsan, password=1234, age=20, gender=男, [email protected], date=Thu Sep 27 00:00:00 CST 2018, cost=8555.55], User [userName=lisi, password=1234, age=25, gender=男, [email protected], date=Thu Sep 27 00:00:00 CST 2018, cost=6666.66]]
   [User [userName=zhangsan, password=1234, age=20, gender=男, [email protected], date=Thu Sep 27 00:00:00 CST 2018, cost=8555.55], User [userName=lisi, password=1234, age=25, gender=男, [email protected], date=Thu Sep 27 00:00:00 CST 2018, cost=6666.66]]
   

    

• 补充说明：

项目结构

Excel表格内容