使用excel数据初始javabean

初衷：单元测试要求实现隔离，但实际的系统中的类往往是相互依赖的。比如常用的分层实现，业务层一般依赖于数据存取层。业务层多是使用数据存取层实例化实体类，然后进行业务处理。如果业务层处理的是单个或很少的实体类，则单元测试时，在程序中初始实体便没有什么问题，但如果业务层要处理的大量的实体类时，要在单元测试中初始实体类列表就比较麻烦。基于这个情况，我想使用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 blocke1.printStackTrace();} catch (IllegalAccessException e1) {// TODO Auto-generated catch blocke1.printStackTrace();} catch (IllegalArgumentException e) {// TODO Auto-generated catch blocke.printStackTrace();}    catch (InvocationTargetException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (Exception e) {// TODO Auto-generated catch blocke.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;@BeforeClasspublic 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);}@Testpublic void testSheetNames() {Assert.assertNotNull(workbook);Assert.assertNotNull(workbook.getSheet("Sheet1"));}@Testpublic 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);}@Testpublic 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@Testpublic 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));}@Testpublic void testBeanValue(){XlsWorkSheet sheet = workbook.getSheet("Sheet1");List <HeaderGroup> hgList = sheet.buildBeans(HeaderGroup.class);}@Testpublic 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格式。