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

偏执的太偏执、 2022-05-10 08:52 429阅读 0赞

*  一、环境说明：win10专业版、jdk1.8.0\_171、eclipse4.7.3a、Junit5![70][]
 *  二、创建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>
      	
    	<dependency>
    	    <groupId>org.apache.poi</groupId>
    	    <artifactId>poi</artifactId>
    	    <version>4.0.0</version>
    	</dependency>
    	
    	<dependency>
    	    <groupId>org.apache.poi</groupId>
    	    <artifactId>poi-ooxml</artifactId>
    	    <version>4.0.0</version>
    	</dependency>
    	
    	<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>
    	
    	<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=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]]

*  ## 补充说明： ##

### 项目结构 ###

![70 1][]

### Excel表格内容 ###

![70 2][]

[70]: /images/20220510/d456f2d614044807ac43c1be19911180.png
[70 1]: /images/20220510/cff3a3bc2e344316ae58ccbca8f59352.png
[70 2]: /images/20220510/23d3a92e88504f3dadc2015cdd06e2ce.png