java 使用POI操作Excel(3)----基于对象操作Excel_JAVA_编程开发_程序员俱乐部

??????? 前两章介绍了POI对Excel的使用,对于java程序员来说,使用对象来操作Excel更符合要求.

??????? 下面是一个用于测试的实体对象:

class="java">package gd.hz.poi.model;

import gd.hz.poi.annotation.ExcelSign;

import java.io.Serializable;
import java.util.Date;

/**
 * 用户Model
 * @author lfd 
 * 2013-12-09
 */
public class User implements Serializable {
	private static final long serialVersionUID = 1L;
	private int id ;
	private String name ;
	private int age ;
	private String sex ;
	private Date createDate ;
	private boolean hasVisible ;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	
	public Date getCreateDate() {
		return createDate;
	}
	public void setCreateDate(Date createDate) {
		this.createDate = createDate;
	}
	
	public boolean isHasVisible() {
		return hasVisible;
	}
	public void setHasVisible(boolean hasVisible) {
		this.hasVisible = hasVisible;
	}
	
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + id;
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		User other = (User) obj;
		if (id != other.id)
			return false;
		return true;
	}
	
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", age=" + age + ", sex="
				+ sex + ", createDate=" + createDate + ", hasVisible="
				+ hasVisible + "]";
	}
}

?

?一个对象就是Excel表中的一行,为了确定实体属性与Excel每一列之间的对应关系,写一个注解来标识:ExcelSign

package gd.hz.poi.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface ExcelSign {
	String title() ;  //记录每个字段在Excel中的标题
	int order() default 0 ;  //记录每个字段在Excel的排序
}

?将实体加上注解:

package gd.hz.poi.model;

import gd.hz.poi.annotation.ExcelSign;

import java.io.Serializable;
import java.util.Date;

/**
 * 用户Model
 * @author lfd 
 * 2013-12-09
 */
public class User implements Serializable {
	private static final long serialVersionUID = 1L;
	private int id ;
	private String name ;
	private int age ;
	private String sex ;
	private Date createDate ;
	private boolean hasVisible ;
	
	@ExcelSign(title="用户标记", order=10)
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	@ExcelSign(title="用户名称", order=9)
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	@ExcelSign(title="年龄", order=7)
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	
	@ExcelSign(title="性别", order=8)
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	
	@ExcelSign(title="日期")
	public Date getCreateDate() {
		return createDate;
	}
	public void setCreateDate(Date createDate) {
		this.createDate = createDate;
	}
	
	@ExcelSign(title="是否在职")
	public boolean isHasVisible() {
		return hasVisible;
	}
	public void setHasVisible(boolean hasVisible) {
		this.hasVisible = hasVisible;
	}
	
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + id;
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		User other = (User) obj;
		if (id != other.id)
			return false;
		return true;
	}
	
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", age=" + age + ", sex="
				+ sex + ", createDate=" + createDate + ", hasVisible="
				+ hasVisible + "]";
	}
}

?

以字符串的形式保存每个字段的名称,类型,在Excel中先后顺序和对应的标题.

package gd.hz.poi.util;

import java.lang.reflect.Type;

/**
 * Excel用户标题
 * @author lfd
 * 2013-12-10
 */
public class ExcelHeader implements Comparable<ExcelHeader> {
	private String methodName ;  //字段的方法名称(字符串形式,保存字段的getXXX方法)
	private String title ;      //字段在Excel中对应的标题
	private int order ;         //记录每个字段的先后顺序(越大越靠前)
	private Type type ;         //字段的类型
	
	public ExcelHeader() {}
	
	public ExcelHeader(String methodName, String title, int order, Type type) {
		this.methodName = methodName;
		this.title = title;
		this.order = order;
		this.type = type;
	}
	
	public String getMethodName() {
		return methodName;
	}
	public void setMethodName(String methodName) {
		this.methodName = 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 Type getType() {
		return type;
	}
	public void setType(Type type) {
		this.type = type;
	}

	/**
	 * 根据order进行排序
	 */
	@Override
	public int compareTo(ExcelHeader o) {
		return this.order > o.order ? -1 : (this.order < o.order ? 1 : 0) ;
	}
}

?

保存实体每个字段的基本信息:

/**
	 * 输出对象标题
	 * @param clazz Class
	 * @return List<ExcelHeader>
	 */
	private List<ExcelHeader> getHeader(Class<?> clazz) {
		Method[] methods = clazz.getDeclaredMethods() ;  //反射获取实体上的所有方法
		String name = null ;
		List<ExcelHeader> headers = new ArrayList<ExcelHeader>() ;
		for(Method method : methods) {
			name = method.getName() ; //实体对象的方法名称
			//过滤只剩下getXXX和isXXX
			if(name != null && (name.startsWith("get") || name.startsWith("is"))) {
				//getXXX和isXXX上是否有ExcelSign这个annotation
				if(method.isAnnotationPresent(ExcelSign.class)) {
					ExcelSign sign = method.getAnnotation(ExcelSign.class) ;  //获取方法上的ExcelSign注解
					//method.getGenericReturnType():获取getxxx获取isxxx上的返回类型
					ExcelHeader header = new ExcelHeader(name, sign.title(), sign.order(), method.getGenericReturnType()) ;
					headers.add(header) ; //保存到List中
				}
			}
		}
		Collections.sort(headers) ; //对List进行排序
		return headers ;
	}

??

?

将实体对象导出到Excel中:

?

/**
	 * 根据路径将数据填充到Excel表中. 
	 * @param path 路径
	 * @param clazz Class
	 * @param entitys 实体集合
	 * @param hasXLS true:为Excel 2003版本 false:为Excel 2007以上版本
	 * @return ExcelUtil
	 */
	public void export2Obj(String path, Class<?> clazz, List<?> entitys, boolean hasXLS) {
		Workbook workbook = export(clazz, entitys, hasXLS) ;
		OutputStream stream = null ;
		try {
			stream = new FileOutputStream(path) ;
			workbook.write(stream) ;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if(stream != null) {
					stream.close() ;
					stream = null ;
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	private Workbook export(Class<?> clazz, List<?> entitys, boolean hasXLS) {
		Workbook workbook = null ;
		if(hasXLS) {
			workbook = new HSSFWorkbook() ;
		} else {
			workbook = new XSSFWorkbook() ;
		}
		try {
			Sheet sheet = workbook.createSheet() ;
			
			//输出标题
			List<ExcelHeader> headers = getHeader(clazz) ;
			Row row = sheet.createRow(0) ;
			int count = headers.size() ;
			for(int i=0; i<count; i++) {
				Cell cell = row.createCell(i) ;
				cell.setCellValue(headers.get(i).getTitle()) ;
			}
			
			//输出数据
			int number = entitys.size() ;
			Method method = null ;
			for(int i=0; i<number; i++) {
				row = sheet.createRow(i+1) ;
				Object obj = entitys.get(i) ;
				for(int j=0; j<count; j++) {
					method = clazz.getDeclaredMethod(headers.get(j).getMethodName()) ; //反射获取实体上的指定方法(根据方法的字符串名称)
					Cell cell = row.createCell(j) ;
					String type = headers.get(j).getType().toString() ; //字符串的形式输出实体每个字段的类型.
					//method.invoke(obj):获取调用方法(根据方法的getxxx)
					if(type.equals("class java.util.Date")) {
						cell.setCellValue((Date)method.invoke(obj)) ;
					} else if(type.equals("class java.lang.Boolean") || type.equals("boolean")) {
						cell.setCellValue((Boolean)method.invoke(obj)) ;
					} else if(type.equals("class java.lang.Integer") || type.equals("int")) {
						cell.setCellValue((Integer)method.invoke(obj)) ;
					} else if(type.equals("class java.lang.Double") || type.equals("double")) {
						cell.setCellValue((Double)method.invoke(obj)) ;
					} else if(type.equals("class java.util.Calendar")) {
						cell.setCellValue((Calendar)method.invoke(obj)) ;
					} else {
						cell.setCellValue((String)method.invoke(obj)) ;
					}
				}
			}
			
		} 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 workbook ;
	}

?测试:

@Test
	public void testExport2Obj() {
		List<User> users = new ArrayList<User>() ;
		User user = null ;
		for(int i=0; i<20; i++) {
			user = new User() ;
			user.setId(i) ;
			user.setAge(i) ;
			user.setCreateDate(new Date()) ;
			user.setHasVisible(true) ;
			user.setName("lfdlfdlfkdlfklfkdlfdsk" + i) ;
			user.setSex("男") ;
			users.add(user) ;
		}
		
		//数据输出到D:/Download目录下.
		export2Obj("D:/Download/lfd.xls", User.class, users, false) ;
 	}

?

将Excel的数据转换成实体对象: 首先要获取Excel每一列与实体属性的对应关系:

/**
	 * 读取模板头信息(标题信息)
	 */
	private Map<Integer, ExcelHeader> readHeader(Row row, Class<?> clazz) {
		List<ExcelHeader> headers = getHeader(clazz) ;
		Map<Integer, ExcelHeader> headerMap = new LinkedHashMap<Integer, ExcelHeader>() ;
		String value = null ;
		for(Cell cell : row) {
			if(cell.getCellType() != Cell.CELL_TYPE_STRING) continue ;
			value = cell.getStringCellValue().trim() ;
			for(ExcelHeader header : headers) {
				if(header.getTitle().equals(value)) {
					headerMap.put(cell.getColumnIndex(), header) ;
					break ;
				}
			}
		}
		return headerMap ;
	}

? 转换成实体对象:

/**
	 * 数据所在的位置
	 * @param path Excel所在的位置
	 * @param clazz Class
	 * @param startLine 标题所在行(从1开始,startLine-1为标题行,startLine为数据开始行)
	 * @param tailLine 不是数据所占的行数
	 * @param hasClasspath true:路径为classpath false:path为绝对路径
	 * @return 数据
	 */
	public <T> List<T> readExcel2Obj(String path, Class<T> clazz, int startLine, int tailLine, boolean hasClasspath) {
		Workbook workbook = null ;
		InputStream stream = null ;
		List<T> entitys = null ;
		try {
			if(hasClasspath) {
				if(path != null && !path.startsWith("/")) {
					path = new StringBuffer(path).insert(0, "/").toString() ;
				}
				stream = ExcelTemplate.class.getResourceAsStream(path) ;
				workbook = WorkbookFactory.create(stream) ;
			} else {
				workbook = WorkbookFactory.create(new File(path)) ;
			}
			entitys = getEntitys(workbook, clazz, startLine, tailLine) ;
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} finally {
			try {
				if(stream != null) {
					stream.close() ;
					stream = null ;
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return entitys ;
	}
	
	/**
	 * 将数据转换成实体
	 * @param workbook Workbook
	 * @param clazz Class
	 * @param startLine 标题所在行(从1开始,startLine-1为标题行,startLine为数据开始行)
	 * @param tailLine 不是数据所占的行数
	 * @return
	 */
	private <T> List<T> getEntitys(Workbook workbook, Class<T> clazz, int startLine, int tailLine) {
		List<T> entitys = null ;
		Sheet sheet = workbook.getSheetAt(0) ;
		//读取标题,这里输入标题行是从1开始不是从0开始所以startLine - 1
		Map<Integer, ExcelHeader> headers = readHeader(sheet.getRow(startLine - 1), clazz) ;
		int end = sheet.getLastRowNum() - tailLine ; 
		entitys = new ArrayList<T>() ;
		String type = null ;
		try {
			for(int i=startLine; i<end; i++) {
				Row row = sheet.getRow(i) ;
				T entity = clazz.newInstance() ; //反射new对象(要有空的构造方法)
				for(Cell cell : row) {
					ExcelHeader header = headers.get(cell.getColumnIndex()) ; //根据readHeader方法的映射关系获取对应的实体属性关系
					if(header != null) {
						String methodName = header.getMethodName() ;  //实体对象的字段属性名称
						//替换成setxxx方法
						if(methodName.startsWith("is")) {
							methodName = methodName.replaceFirst("is", "set") ;
						} else {
							methodName = methodName.replaceFirst("get", "set") ;
						}
						type = header.getType().toString() ;
						Method method = clazz.getDeclaredMethod(methodName, getFieldType(type)) ;
						if(type.equals("class java.util.Date")) {
							method.invoke(entity, cell.getDateCellValue()) ;
						} else if(type.equals("class java.lang.Boolean") || type.equals("boolean")) {
							method.invoke(entity, cell.getBooleanCellValue()) ;
						} else if(type.equals("class java.lang.Integer") || type.equals("int")) {
							method.invoke(entity, (int)cell.getNumericCellValue()) ;
						} else if(type.equals("class java.lang.Double") || type.equals("double")) {
							method.invoke(entity, cell.getNumericCellValue()) ;
						} else if(type.equals("class java.util.Calendar")) {
							Calendar calendar =  Calendar.getInstance() ;
							calendar.setTime(cell.getDateCellValue()) ;
							method.invoke(entity, calendar) ;
						} else {
							method.invoke(entity, cell.getStringCellValue()) ;
						}
					}
				}
				entitys.add(entity) ;
			}
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		return entitys ;
	}
	
	/**
	 * 获取对象字段的类型Class
	 * @param type
	 * @return
	 */
	private Class<?> getFieldType(String type) {
		if(type.equals("class java.util.Date")) {
			return Date.class ;
		} else if(type.equals("class java.lang.Boolean")) {
			return Boolean.class ;
		} else if(type.equals("boolean")) {
			return Boolean.TYPE ;
		} else if(type.equals("class java.lang.Integer")) {
			return Integer.class ;
		} else if(type.equals("int")) {
			return Integer.TYPE ;
		} else if(type.equals("class java.lang.Double")) {
			return Double.class ;
		} else if(type.equals("double")) {
			return Double.TYPE ;
		} else if(type.equals("class java.util.Calendar")) {
			return Calendar.class ;
		}
		return String.class ;
	}

?测试:

@Test
	public void testReadExcel2Obj() {
		List<User> users = readExcel2Obj("D:/Download/lfd.xls", User.class, 1, 2, false) ;
		for(User user : users) {
			System.out.println(user);
		}
	}

? 下面是上述例子的封装例子,包括使用模板输出数据(基于Maven项目): http://www.kuaipan.cn/file/id_129263676497613796.htm