“Java+POI+模板”二：基于对象列表输出到excel(利用反射)

请先阅读 “Java+POI+模板”一：打造复杂Excel 报表  

 

1. 首先创建ExcelUtil类，该类将要实现将一组对象转换为Excel表格，并且可以从Excel表格中读取到一组List对象中。现在我们先创建类并实现单例。

 

public class ExcelUtil {
	private static ExcelUtil eu = new ExcelUtil();
	private ExcelUtil(){}
	
	public static ExcelUtil getInstance() {
		return eu;
	}
}

 2. 创建一个Annotation类，用来在对象的get方法上加入的annotation，通过该annotation说明某个属性所对应的标题。

 

@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelResources {
	/**
	 * 属性的标题名称
	 * @return
	 */
	String title();
	/**
	 * 在excel的顺序
	 * @return
	 */
	int order() default 9999;
}

 3. 创建ExcelHeader类，用来存储Excel标题的对象，通过该对象可以获取标题和方法的对应关系

 

public class ExcelHeader implements Comparable<ExcelHeader>{
	/**
	 * excel的标题名称
	 */
	private String title;
	/**
	 * 每一个标题的顺序
	 */
	private int order;
	/**
	 * 说对应方法名称
	 */
	private String methodName;
	
	
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public int getOrder() {
		return order;
	}
	public void setOrder(int order) {
		this.order = order;
	}
	public String getMethodName() {
		return methodName;
	}
	public void setMethodName(String methodName) {
		this.methodName = methodName;
	}
	
	public int compareTo(ExcelHeader o) {
		return order>o.order?1:(order<o.order?-1:0);
	}
	public ExcelHeader(String title, int order, String methodName) {
		super();
		this.title = title;
		this.order = order;
		this.methodName = methodName;
	}
	@Override
	public String toString() {
		return "ExcelHeader [title=" + title + ", order=" + order
				+ ", methodName=" + methodName + "]";
	}
	
	
}

 4. 创建Model类，并在属性的get方法上面加上我们的Annotation

 

public class User {
	
	private int id;
	private String username;
	private String nickname;
	private int age;
	
	@ExcelResources(title="用户标识",order=1)
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	@ExcelResources(title="用户名",order=2)
	public String getUsername() {
		return username;
	}
	
	public void setUsername(String username) {
		this.username = username;
	}
	
	@ExcelResources(title="用户昵称",order=3)
	public String getNickname() {
		return nickname;
	}
	public void setNickname(String nickname) {
		this.nickname = nickname;
	}
	
	@ExcelResources(title="用户年龄",order=4)
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public User(int id, String username, String nickname, int age) {
		super();
		this.id = id;
		this.username = username;
		this.nickname = nickname;
		this.age = age;
	}
	public User() {
		super();
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", nickname="
				+ nickname + ", age=" + age + "]";
	}
	
	
}

 5. 在ExcelUtil中实现exportObj2ExcelByTemplate方法，用于处理将对象转换为Excel。

 

public void exportObj2ExcelByTemplate(Map<String,String> datas,String template,String outPath, List objs, Class clz, boolean isClasspath){
		ExcelTemplate et=ExcelTemplate.getInstance();
		if(isClasspath){
			et.readTemplateByClasspath(template);
		}else{
			et.readTemplateByPath(template);
		}
		List<ExcelHeader> headers=getHeaderList(clz);
		Collections.sort(headers);
		//输出标题
		et.createNewRow();
		for (ExcelHeader eh : headers) {
			et.createCell(eh.getTitle());
		}
		//输出值
		try {
			for(Object obj:objs){
				et.createNewRow();
				for(ExcelHeader eh:headers){
					String mn=eh.getMethodName();
					Method m=clz.getDeclaredMethod(mn);
					Object rel=m.invoke(obj);
					et.createCell(rel.toString());
				}
			}
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		et.writeToFile(outPath);
	}

这里输出值的过程也可以通过导入BeanUtils来处理

//输出值
		try {
			for(Object obj:objs){
				et.createNewRow();
				for(ExcelHeader eh:headers){
//					String mn=eh.getMethodName();
//					Method m=clz.getDeclaredMethod(mn);
//					Object rel=m.invoke(obj);
//					et.createCell(rel.toString());
					et.createCell(BeanUtils.getProperty(obj,getMethodName(eh) ));
				}
			}
		} 

 

 exportObj2ExcelByTemplate方法方法会调用getHeaderList方法，来获取Annotation的title以及order

 

private List<ExcelHeader> getHeaderList(Class clz){
		List<ExcelHeader> headers=new ArrayList<ExcelHeader>();
		Method[] ms = clz.getDeclaredMethods();
		for (Method m : ms) {
			String mn =m.getName();
			if(mn.startsWith("get")&& m.isAnnotationPresent(ExcelResource.class)){
				ExcelResource er=m.getAnnotation(ExcelResource.class);
				headers.add(new ExcelHeader(er.title(), er.order(), mn));
			}
		}
		return headers;
		
	}

 6. 实现此功能

 

@Test
	public void testObj2Xls() {
		List<User> users = new ArrayList<User>();
		users.add(new User(1,"aaa","水水水",11));
		users.add(new User(2,"sdf","水水水",11));
		users.add(new User(3,"sdfde","水水水",11));
		users.add(new User(4,"aaa","水水水",11));
		users.add(new User(54,"aaa","水水水",11));
		users.add(new User(16,"aaa","水水水",11));
		ExcelUtil.getInstance().exportObj2ExcelByTemplate(null,"/excel/user.xls","d:/test/poi/tus.xls", users, User.class, true);
	}

 原先的模板如下图：

 插入完成后的模板如下图：