前两章介绍了POI对Excel的使用,对于java程序员来说,使用对象来操作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 ;
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项目):