使用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 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格式。