初衷:单元测试要求实现隔离,但实际的系统中的类往往是相互依赖的。比如常用的分层实现,业务层一般依赖于数据存取层。业务层多是使用数据存取层实例化实体类,然后进行业务处理。如果业务层处理的是单个或很少的实体类,则单元测试时,在程序中初始实体便没有什么问题,但如果业务层要处理的大量的实体类时,要在单元测试中初始实体类列表就比较麻烦。基于这个情况,我想使用excel数据来批量初始实体列表。
过程:首先网络搜索从excel读取数据生成javabean的相关实现,结果没有现成的实现,但我知道dbunit有从excel读取数据保存到数据库表的实现,这样从Excel读取数据的方案就有了。然后利用java的反射机制给实体类属性赋值。
代码:
用于打开文件的XlsWorkBook:
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* 功能说明:
* 日期:2012-11-1
* Author: 常绍新
*/
public class XlsWorkBook {
private Map<String, XlsWorkSheet> _sheets ;
public XlsWorkBook(String fileName) throws IOException {
this(new FileInputStream(new File(fileName)));
}
public XlsWorkBook(InputStream in) throws IOException{
_sheets = new HashMap<String, XlsWorkSheet>();
HSSFWorkbook workbook = new HSSFWorkbook(in);
int sheetCount = workbook.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++)
{
XlsWorkSheet sheet = new XlsWorkSheet(workbook.getSheetName(i),
workbook.getSheetAt(i));
_sheets.put(workbook.getSheetName(i), sheet);
}
}
public XlsWorkSheet getSheet(String sheetName){
return _sheets.get(sheetName);
}
}
用于读取数据并生成实体类列表的XlsWorkSheet:
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.datatype.DataTypeException;
import org.dbunit.dataset.excel.XlsDataSetWriter;
/**
* 功能说明:
* 日期:2012-10-31
* Author: 常绍新
*/
public class XlsWorkSheet {
private final HSSFSheet _sheet;
private final DecimalFormatSymbols symbols = new DecimalFormatSymbols();
private List<String> _columnList = new ArrayList<String>();
private int _maxColumnNum = 0;
public XlsWorkSheet(String sheetName, HSSFSheet sheet){
_sheet = sheet;
symbols.setDecimalSeparator('.');
if(_sheet.getLastRowNum()>0)
createColumnList(_sheet.getRow(0));
}
private void createColumnList(HSSFRow sampleRow){
for (int i = 0; ; i++)
{
HSSFCell cell = sampleRow.getCell(i);
if (cell == null)
{
break;
}
String columnName = cell.getRichStringCellValue().getString();
if (columnName != null)
{
columnName = columnName.trim();
}
// Bugfix for issue ID 2818981 - if a cell has a formatting but no name also ignore it
if(columnName.length()<=0)
{
break;
}
_maxColumnNum = i;
//Column column = new Column(columnName, DataType.UNKNOWN);
_columnList.add(columnName.toUpperCase());
}
//Column[] columns = (Column[])_columnList.toArray(new Column[0]);
}
/**
* 功能说明:得到行数
* @return
*/
public int getRowCount()
{
return _sheet.getLastRowNum();
}
/**
* 功能说明:得到列数
* @return
*/
public int getMaxColumnNum(){
return _maxColumnNum;
}
/**
* 功能说明:根据列名得到列号
* @param column
* @return
*/
private int getColumnIndex(String column){
return _columnList.indexOf(column.toUpperCase());
}
public Object getValue(int row, String column) throws Exception
{
int columnIndex = getColumnIndex(column);
HSSFCell cell = _sheet.getRow(row + 1).getCell(columnIndex);
if (cell == null)
{
return null;
}
int type = cell.getCellType();
switch (type)
{
case HSSFCell.CELL_TYPE_NUMERIC:
HSSFCellStyle style = cell.getCellStyle();
if (HSSFDateUtil.isCellDateFormatted(cell))
{
return getDateValue(cell);
}
else if(XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString()))
{
// The special dbunit date format
return getDateValueFromJavaNumber(cell);
}
else
{
return getNumericValue(cell);
}
case HSSFCell.CELL_TYPE_STRING:
return cell.getRichStringCellValue().getString();
case HSSFCell.CELL_TYPE_FORMULA:
throw new Exception("Formula not supported at row=" +
row + ", column=" + column);
case HSSFCell.CELL_TYPE_BLANK:
return null;
case HSSFCell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;
case HSSFCell.CELL_TYPE_ERROR:
throw new Exception("Error at row=" + row +
", column=" + column);
default:
throw new Exception("Unsupported type at row=" + row +
", column=" + column);
}
}
protected Object getDateValueFromJavaNumber(HSSFCell cell)
{
double numericValue = cell.getNumericCellValue();
BigDecimal numericValueBd = new BigDecimal(String.valueOf(numericValue));
numericValueBd = stripTrailingZeros(numericValueBd);
return new Long(numericValueBd.longValue());
// return new Long(numericValueBd.unscaledValue().longValue());
}
protected Object getDateValue(HSSFCell cell)
{
double numericValue = cell.getNumericCellValue();
Date date = HSSFDateUtil.getJavaDate(numericValue);
// Add the timezone offset again because it was subtracted automatically by Apache-POI (we need UTC)
long tzOffset = TimeZone.getDefault().getOffset(date.getTime());
date = new Date(date.getTime() + tzOffset);
return new Long(date.getTime());
}
/**
* Removes all trailing zeros from the end of the given BigDecimal value
* up to the decimal point.
* @param value The value to be stripped
* @return The value without trailing zeros
*/
private BigDecimal stripTrailingZeros(BigDecimal value)
{
if(value.scale()<=0){
return value;
}
String valueAsString = String.valueOf(value);
int idx = valueAsString.indexOf(".");
if(idx==-1){
return value;
}
for(int i=valueAsString.length()-1; i>idx; i--){
if(valueAsString.charAt(i)=='0'){
valueAsString = valueAsString.substring(0, i);
}
else if(valueAsString.charAt(i)=='.'){
valueAsString = valueAsString.substring(0, i);
// Stop when decimal point is reached
break;
}
else{
break;
}
}
BigDecimal result = new BigDecimal(valueAsString);
return result;
}
protected BigDecimal getNumericValue(HSSFCell cell)
{
String formatString = cell.getCellStyle().getDataFormatString();
String resultString = null;
double cellValue = cell.getNumericCellValue();
if((formatString != null))
{
if(!formatString.equals("General") && !formatString.equals("@")) {
DecimalFormat nf = new DecimalFormat(formatString, symbols);
resultString = nf.format(cellValue);
}
}
BigDecimal result;
if(resultString != null) {
try {
result = new BigDecimal(resultString);
}
catch(NumberFormatException e) {
result = toBigDecimal(cellValue);
}
}
else {
result = toBigDecimal(cellValue);
}
return result;
}
/**
* @param cellValue
* @return
* @since 2.4.6
*/
private BigDecimal toBigDecimal(double cellValue)
{
String resultString = String.valueOf(cellValue);
// To ensure that intergral numbers do not have decimal point and trailing zero
// (to restore backward compatibility and provide a string representation consistent with Excel)
if (resultString.endsWith(".0")) {
resultString=resultString.substring(0,resultString.length()-2);
}
BigDecimal result = new BigDecimal(resultString);
return result;
}
public <T extends Object> List<T> buildBeans(Class cls){
List<T> list = new ArrayList<T>();
try {
Method[] ms = cls.getDeclaredMethods();
for(int row=0;row<this.getRowCount();row++){
T bean = (T)cls.newInstance();
for(int i=0;i<ms.length;i++){
String methodName = ms[i].getName().toUpperCase();
if(methodName.startsWith("SET") && this._columnList.contains(methodName.substring(3))){
//System.out.println(ms[i].getGenericParameterTypes()[0].toString());
String val = this.getValue(row, methodName.substring(3)).toString();
String pType = ms[i].getGenericParameterTypes()[0].toString();
if(pType.equals("int"))
ms[i].invoke(bean, Integer.parseInt(val));
else if(pType.equals("float")){
ms[i].invoke(bean, Float.parseFloat(val));
}
else if(pType.equals("boolean")){
ms[i].invoke(bean, Boolean.parseBoolean(val));
}
else if(pType.equals("double")){
ms[i].invoke(bean, Double.parseDouble(val));
}
else if(pType.equals("short")){
ms[i].invoke(bean, Short.parseShort(val));
}
else if(pType.equals("long")){
ms[i].invoke(bean, Long.parseLong(val));
}
else if(pType.equals("class java.util.Date")){
Date date = new Date();
date.setTime(Long.parseLong(val));
ms[i].invoke(bean, date);
}
else {
//System.out.println(pType);
ms[i].invoke(bean, val);
}
}
}
list.add(bean);
}
} catch (InstantiationException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (IllegalAccessException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
测试:
import java.io.File;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import junit.framework.Assert;
import org.junit.BeforeClass;
import org.junit.Test;
import com.dfsoft.hummer.domain.entity.MyDemoEntity;
import com.dfsoft.hummer.domain.entity.field.HeaderGroup;
import static org.hamcrest.MatcherAssert.*;
import static org.hamcrest.Matchers.*;
/**
* 功能说明:
* 日期:2012-11-1
* Author: 常绍新
*/
public class XlsWorkBookTest {
private static XlsWorkBook workbook;
// XlsWorkSheet sheet;
@BeforeClass
public static void initSheet() throws IOException{
String fileName = "D:/myTest.xls";
File f = new File(fileName);
// System.out.println(f.getAbsolutePath());
// f.getAbsolutePath();
workbook = new XlsWorkBook(fileName);
}
@Test
public void testSheetNames() {
Assert.assertNotNull(workbook);
Assert.assertNotNull(workbook.getSheet("Sheet1"));
}
@Test
public void testCellValue() throws Exception
{
XlsWorkSheet sheet = workbook.getSheet("Sheet1");
String name = sheet.getValue(0, "displayname").toString();
String age = sheet.getValue(0, "serial").toString();
String sex = sheet.getValue(0, "sex").toString();
Assert.assertEquals("张三", name);
Assert.assertEquals("23", age);
Assert.assertEquals("男", sex);
}
@Test
public void testBuildBeans(){
XlsWorkSheet sheet = workbook.getSheet("Sheet1");
List <HeaderGroup> hgList = sheet.buildBeans(HeaderGroup.class);
Assert.assertEquals(3, hgList.size());
Assert.assertEquals("张三", hgList.get(0).getDisplayName());
Assert.assertEquals("李四", hgList.get(1).getDisplayName());
Assert.assertEquals("王五", hgList.get(2).getDisplayName());
Assert.assertEquals(23, hgList.get(0).getSerial());
Assert.assertEquals(29, hgList.get(1).getSerial());
Assert.assertEquals(40, hgList.get(2).getSerial());
}
//@Ignore
@Test
public void testBuildBeans1() throws ParseException{
XlsWorkSheet sheet = workbook.getSheet("Sheet2");
List <MyDemoEntity> myList = sheet.buildBeans(MyDemoEntity.class);
Assert.assertEquals(4, myList.size());
double d = 15;
short s = 4;
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Date dt = df.parse("2010-5-5");
MyDemoEntity e1 = new MyDemoEntity(1,dt,"good",12,d,s,true,79824);
MyDemoEntity e2 = new MyDemoEntity();
MyDemoEntity e3 = new MyDemoEntity();
MyDemoEntity e4 = new MyDemoEntity();
//Assert.assertSame(expected, actual)
//System.out.println(myList.get(0));
System.out.println(myList.get(0));
System.out.println(e1);
//Matchers.is(e1);
//MatcherAssert.assertThat(myList.get(0), Matchers.);
assertThat(myList.get(0), equalTo(e1));
// Assert.assertTrue(e1.equals(myList.get(0)));
// Assert.assertEquals(e1, myList.get(0));
// Assert.assertEquals(e2, myList.get(1));
// Assert.assertEquals(e3, myList.get(2));
// Assert.assertEquals(e4, myList.get(3));
}
@Test
public void testBeanValue(){
XlsWorkSheet sheet = workbook.getSheet("Sheet1");
List <HeaderGroup> hgList = sheet.buildBeans(HeaderGroup.class);
}
@Test
public void testTemp() throws ParseException{
String str = "setDisplayName";
SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
Date dt = df.parse("2012-1-10");
Date dt1 = df.parse("2012-1-10");
Assert.assertEquals("DisplayName", str.substring(3));
Assert.assertTrue(dt.equals(dt1));
}
}
有了它,再测试业务层,就可以方便的从excel生成实体列表,而不再依赖于数据库。
不足之处是只能读取excel2003格式,而不能读取excel2007或以上的excel格式。