本文將解說如何設計一組annotation和API，我命名為Cell Mapper，讓開發人員只要在POJO上面標記想要對應到Excel的欄位位置，就可以自動匯出Excel或是從Excel讀取資料，底層的library使用老牌的JExcelApi。
首先定義interface：
CellMapper
package com.gss.gmo.cao.jexcel;
import java.util.List;
import jxl.Workbook;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import com.gss.gmo.cao.jexcel.read.ReadException;
/**
* @author linus_chien
*
*/
public interface CellMapper {
/**
* Read the sheet according to the model class.
*
* @param workbook
* @param modelClass
* @return
* @throws ReadException
*/
<Model> List<Model> read(Workbook workbook, Class<Model> modelClass) throws ReadException;
/**
* Write the sheet according to the model class.
*
* @param workbook
* @param data
* @param modelClass
* @throws WriteException
*/
<Model> void write(WritableWorkbook workbook, List<Model> data, Class<Model> modelClass) throws WriteException;
}
這裡利用method level generic type保留彈性。
因為JExcelApi沒有定義ReadException，所以我們自訂一個：
ReadException
package com.gss.gmo.cao.jexcel.read;
import jxl.JXLException;
/**
* @author linus_chien
*
*/
public class ReadException extends JXLException {
private static final long serialVersionUID = 1L;
public ReadException(String message) {
super(message);
}
}
接下來定義兩個annotation，@Sheet和@Column：
@Sheet
package com.gss.gmo.cao.jexcel.annotation;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Excel sheet.
*
* @author linus_chien
*
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Sheet {
/**
* @return sheet name
*/
String name();
}
@Column
package com.gss.gmo.cao.jexcel.annotation;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Excel column.
*
* @author linus_chien
*
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Column {
/**
* @return column index, numbered from 0
*/
int index();
/**
* @return column title
*/
String title();
}
然後我們做兩個type converter，分別將JExcelApi的Cell轉換成Java type，以及將Java type轉換成WritableCell：
CellTypeReader
package com.gss.gmo.cao.jexcel.read;
import java.util.Date;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
/**
* Read cell content by cell type.
*
* @author linus_chien
*
*/
public class CellTypeReader {
/**
* @param cell
* @return
*/
public String read(LabelCell cell) {
return cell.getString();
}
/**
* @param cell
* @return
*/
public Double read(NumberCell cell) {
return cell.getValue();
}
/**
* @param cell
* @return
*/
public Date read(DateCell cell) {
return cell.getDate();
}
/**
* @param cell
* @return
*/
public Object read(Cell cell) {
CellType cellType = cell.getType();
if (cellType == CellType.LABEL) {
return read((LabelCell) cell);
} else if (cellType == CellType.NUMBER) {
return read((NumberCell) cell);
} else if (cellType == CellType.DATE) {
return read((DateCell) cell);
} else {
return cell.getContents();
}
}
}
WritableCellFactory
package com.gss.gmo.cao.jexcel.write;
import java.util.Date;
import jxl.write.Blank;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCell;
import org.apache.commons.lang3.StringUtils;
/**
* Create WritableCell instance by value type.
*
* @author linus_chien
*
*/
public class WritableCellFactory {
/**
* @param col
* @param row
* @param value
* @return Label or Blank
*/
public WritableCell create(int col, int row, String value) {
if (StringUtils.isBlank(value)) {
return create(col, row);
} else {
return new Label(col, row, value);
}
}
/**
* @param col
* @param row
* @param value
* @return jxl.write.Number or Blank
*/
public WritableCell create(int col, int row, Number value) {
if (value == null) {
return create(col, row);
} else {
return new jxl.write.Number(col, row, value.doubleValue());
}
}
/**
* @param col
* @param row
* @param value
* @return DateTime or Blank
*/
public WritableCell create(int col, int row, Date value) {
if (value == null) {
return create(col, row);
} else {
return new DateTime(col, row, value);
}
}
/**
* @param col
* @param row
* @param value
* @return Label or jxl.write.Number or DateTime or Blank
*/
public WritableCell create(int col, int row, Object value) {
if (value == null) {
return create(col, row);
} else if (value instanceof String) {
return create(col, row, (String) value);
} else if (value instanceof Number) {
return create(col, row, (Number) value);
} else if (value instanceof Date) {
return create(col, row, (Date) value);
} else {
return create(col, row, value.toString());
}
}
/**
* @param col
* @param row
* @return Blank
*/
public WritableCell create(int col, int row) {
return new Blank(col, row);
}
}
重頭戲，利用這組annotation實作的Cell Mapper implementation：
AnnotationCellMapperImpl
package com.gss.gmo.cao.jexcel.impl;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.commons.beanutils.BeanUtils;
import org.springframework.util.ReflectionUtils;
import com.gss.gmo.cao.jexcel.CellMapper;
import com.gss.gmo.cao.jexcel.annotation.Helper;
import com.gss.gmo.cao.jexcel.read.CellTypeReader;
import com.gss.gmo.cao.jexcel.read.ReadException;
import com.gss.gmo.cao.jexcel.write.WritableCellFactory;
/**
* Depend on annotation.
*
* @author linus_chien
*
*/
public class AnnotationCellMapperImpl implements CellMapper {
/**
* Helper instance.
*/
private Helper helper = new Helper();
/**
* WritableCellFactory instance.
*/
private WritableCellFactory factory = new WritableCellFactory();
/**
* CellTypeReader instance.
*/
private CellTypeReader reader = new CellTypeReader();
@Override
public <Model> List<Model> read(Workbook workbook, Class<Model> modelClass) throws ReadException {
List<Model> results = new ArrayList<Model>();
if (helper.isClassWithSheetAnnotation(modelClass)) {
List<Field> fields = helper.getAnnotatedFields(modelClass);
Sheet sheet = getSheet(workbook, modelClass);
for (int row = 1; row < sheet.getRows(); row++) {
Map<String, Object> data = new HashMap<String, Object>();
for (Field field : fields) {
int col = helper.getAnnotatedColumnIndex(field);
Cell cell = sheet.getCell(col, row);
data.put(field.getName(), reader.read(cell));
}
try {
Model model = modelClass.newInstance();
BeanUtils.populate(model, data);
results.add(model);
} catch (Exception e) {
e.printStackTrace();
throw new ReadException(e.getMessage());
}
}
}
return results;
}
@Override
public <Model> void write(WritableWorkbook workbook, List<Model> datas, Class<Model> modelClass) throws WriteException {
if (!helper.isClassWithSheetAnnotation(modelClass)) {
return;
}
List<Field> fields = helper.getAnnotatedFields(modelClass);
WritableSheet sheet = createSheet(workbook, modelClass);
for (Field field : fields) {
int col = helper.getAnnotatedColumnIndex(field);
sheet.addCell(factory.create(col, 0, helper.getAnnotatedColumnTitle(field)));
}
for (int row = 0; row < datas.size(); row++) {
Model data = datas.get(row);
for (Field field : fields) {
int col = helper.getAnnotatedColumnIndex(field);
ReflectionUtils.makeAccessible(field);
Object value = ReflectionUtils.getField(field, data);
sheet.addCell(factory.create(col, row + 1, value));
}
}
}
/**
* @param workbook
* @param modelClass
* @return WritableSheet
*/
private WritableSheet createSheet(WritableWorkbook workbook, Class<?> modelClass) {
String sheetName = helper.getAnnotatedSheetName(modelClass);
WritableSheet sheet = workbook.createSheet(sheetName, workbook.getNumberOfSheets());
return sheet;
}
/**
* @param workbook
* @param modelClass
* @return
*/
private Sheet getSheet(Workbook workbook, Class<?> modelClass) {
String sheetName = helper.getAnnotatedSheetName(modelClass);
Sheet sheet = workbook.getSheet(sheetName);
return sheet;
}
}
Helper
package com.gss.gmo.cao.jexcel.annotation;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
/**
* Help CellMapper to retrieve annotation data.
*
* @author linus_chien
*
*/
public class Helper {
/**
* @param modelClass
* @return
*/
public boolean isClassWithSheetAnnotation(Class<?> modelClass) {
return modelClass.isAnnotationPresent(Sheet.class);
}
/**
* @param modelClass
* @return
*/
public String getAnnotatedSheetName(Class<?> modelClass) {
String sheetName;
Sheet sheetAnnotation = modelClass.getAnnotation(Sheet.class);
if (StringUtils.isBlank(sheetAnnotation.name())) {
sheetName = modelClass.getSimpleName();
} else {
sheetName = sheetAnnotation.name();
}
return sheetName;
}
/**
* @param modelClass
* @return
*/
public List<Field> getAnnotatedFields(Class<?> modelClass) {
List<Field> annotatedFields = new ArrayList<Field>();
Field[] fields = modelClass.getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(Column.class)) {
annotatedFields.add(field);
}
}
return annotatedFields;
}
/**
* @param field
* @return
*/
public String getAnnotatedColumnTitle(Field field) {
String title;
Column columnAnnotation = field.getAnnotation(Column.class);
if (StringUtils.isBlank(columnAnnotation.title())) {
title = field.getName();
} else {
title = columnAnnotation.title();
}
return title;
}
/**
* @param field
* @return
*/
public int getAnnotatedColumnIndex(Field field) {
Column columnAnnotation = field.getAnnotation(Column.class);
return columnAnnotation.index();
}
}
最後是一個簡單的test case：
AnnotationCellMapperImplTest
package com.gss.gmo.cao.jexcel.impl;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import junit.framework.Assert;
import jxl.Workbook;
import jxl.write.WritableWorkbook;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import com.gss.gmo.cao.jexcel.CellMapper;
import com.gss.gmo.cao.jexcel.annotation.Column;
import com.gss.gmo.cao.jexcel.annotation.Sheet;
public class AnnotationCellMapperImplTest {
private static ByteArrayOutputStream os;
private static ByteArrayInputStream is;
@BeforeClass
public static void setUpBeforeClass() throws Exception {
os = new ByteArrayOutputStream();
}
@AfterClass
public static void tearDownAfterClass() throws Exception {
os.close();
is.close();
}
@Test
public void testWrite() throws Exception {
WritableWorkbook workbook = Workbook.createWorkbook(os);
CellMapper cellMapper = new AnnotationCellMapperImpl();
List<User> users = new ArrayList<User>();
User u1 = new User();
u1.setName("Linus");
u1.setAge(36);
u1.setBirthday(new Date());
users.add(u1);
User u2 = new User();
u2.setName("Mime");
u2.setAge(35);
u2.setBirthday(new Date());
users.add(u2);
cellMapper.write(workbook, users, User.class);
workbook.write();
workbook.close();
}
@Test
public void testRead() throws Exception {
is = new ByteArrayInputStream(os.toByteArray());
Workbook workbook = Workbook.getWorkbook(is);
CellMapper cellMapper = new AnnotationCellMapperImpl();
List<User> users = cellMapper.read(workbook, User.class);
Assert.assertEquals("Linus", users.get(0).getName());
Assert.assertEquals("Mime", users.get(1).getName());
workbook.close();
}
@Sheet(name = "User")
public static class User {
@Column(index = 0, title = "姓名")
private String name;
@Column(index = 1, title = "年齡")
private int age;
@Column(index = 2, title = "生日")
private Date birthday;
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "User [name=" + name + ", age=" + age + ", birthday=" + birthday + "]";
}
}
}
