解析Excel，通过Excel驱动测试数据参数化

1.解析Excel：

public class ExcelDataProvider  implements Iterator<Object[]>{
	    private Workbook book = null;
	    private Sheet sheet = null;
	    private int rowNum = 0;
	    private int curPhysicalRowNo = 0;
	    private int columnNum = 0;
	    private String[] columnnName;
	    
	    
	    public ExcelDataProvider(String classname, String methodname)throws BiffException, IOException {
	      
	    	File directory = new File(".");

	        this.book = Workbook.getWorkbook(new File(directory.getCanonicalPath()+ "\\filepath\\" + classname.replaceAll("\\.", "/") + ".xls"));
	        System.out.println(directory.getCanonicalPath() + "\\filepath\\"+ classname.replaceAll("\\.", "/") + ".xls");

	    	
	        this.sheet = book.getSheet(methodname);
	        this.rowNum = sheet.getRows();
	        //System.out.println(rowNum);	  
	        Cell[] c = sheet.getRow(0);
	       /* for(Cell c1:c){
	        	System.out.println(c1.getContents());
	        }*/
	        this.columnNum = c.length;
	        //System.out.println(columnNum);
	        columnnName = new String[c.length];
	        for (int i = 0; i < c.length; i++) {
	            columnnName[i] = c[i].getContents().toString().replace("\n", "");
	        }	      	            
	        this.curPhysicalRowNo++;

	    }    
		@Override
		public boolean hasNext() {
			  if (this.rowNum == 0 || this.curPhysicalRowNo >= this.rowNum) {
		            try {
		                book.close();
		            } catch (Exception e) {
		                e.printStackTrace();
		            }
		            return false;
		        } else
		            return true;
		}
		
		@Override
		public Object[] next() {
			Map<String, ArrayList<String>> s = new HashMap<String, ArrayList<String>>();
	        int RangeRow = 1;
	        boolean ThisIsRange = false;
	        jxl.Range[] ranges = sheet.getMergedCells();
	       
	        //首先要确定第一列占了几行，要区分 Range 还是 Row
	        for (jxl.Range space : ranges) {
	            if (space.getTopLeft().getColumn() == 0
	                    && space.getBottomRight().getColumn() == 0
	                    && space.getTopLeft().getRow() == this.curPhysicalRowNo) {

	                RangeRow = space.getBottomRight().getRow()
	                        - space.getTopLeft().getRow() + 1;
	                ThisIsRange = true;
	                break;
	            }
	        }

	        for (int i = 0; i < this.columnNum; i++) {
	            ArrayList<String> temp = new ArrayList<String>();
	            if (ThisIsRange) {
	                if (i == 0) {
	                    temp.add(sheet.getRow(this.curPhysicalRowNo)[i].getContents().toString());
	                	
	                } else {
	                    for (int j = 0; j < RangeRow; j++) {
	                        temp.add(sheet.getRow(this.curPhysicalRowNo + j)[i].getContents().toString());
	                        
	                    }
	                }
	            } else {
	                temp.add(sheet.getRow(this.curPhysicalRowNo)[i].getContents().toString());
	            	
	            }
	            s.put(this.columnnName[i], temp);
	        }
	        Object r[] = new Object[1];
	        r[0] = s;
	        this.curPhysicalRowNo = this.curPhysicalRowNo + RangeRow;
	       
	        return r;
		}
		@Override
		public void remove() {
			 throw new UnsupportedOperationException("remove unsupported.");
			
		}

}

2.测试方法中调用dataProvider，将测试数据参数化：

方式一：直接定义数据

	 @Test(dataProvider = "dp")
	    public void testA(Map<String, ArrayList<String>> data) {
		 System.out.println(data.toString());
		
		 System.out.println("------------------");
		 
	        System.out.print("字段A:" + data.get("字段A"));
	        System.out.println("");
	        System.out.print("字段B:" + data.get("字段B"));
	        System.out.println("");
	        System.out.print("字段C:" + data.get("字段C"));
	        System.out.println("");
	        System.out.print("字段D:" + data.get("字段D"));
	        System.out.println("");
	        System.out.println("*******************");

	    }

	 	@DataProvider(name="dp")
	    public Iterator<Object[]> dataFortestMethod(Method method)throws IOException, BiffException {
	        return new ExcelDataProvider(this.getClass().getName(), method.getName());

	    }

方式二：

通过数据持久化层，将Excel中的数据值赋予给变量

数据持久化层：

public class Enterprise_reporting_demo {
	public String standardId;
	public String evaluateDate;
	public String evaluateMan;
	public String evaluateJoiner;
	
	public Enterprise_reporting_demo(String standardId,String evaluateDate,String evaluateMan,String evaluateJoiner){
		this.standardId=standardId;
		this.evaluateDate=evaluateDate;
		this.evaluateMan=evaluateMan;
		this.evaluateJoiner=evaluateJoiner;
	}

}

测试方法中调用：

	@Test(dataProvider="dp")
	public void test5(Map<String,ArrayList<String>>data){
		Enterprise_reporting_demo enterprise_reporting_demo=new Enterprise_reporting_demo(data.get("standardId").get(0), data.get("evaluateDate").get(0), data.get("evaluateMan").get(0), data.get("evaluateJoiner").get(0));
		enterprise_reporting.del_Enterprise_reporting(enterprise_reporting_demo);
		Assert.assertFalse(enterprise_reporting.del);
	}
	@DataProvider(name="dp")
    public Iterator<Object[]> dataFortestMethod(Method method)throws IOException, BiffException {
        return new ExcelDataProvider(this.getClass().getName(), method.getName());
        
    }

注意：Excel文件的命名应该和测试方法的命名一致，否则会提示：找不到文件