TestNG+Selenium Webdriver 数据(Excel)驱动的方法

1.下载 jxl.jar复制到测试项目的lib 下，在项目中新建数据驱动类

ExcelData.java

[java] view plaincopyprint?

1. package com.annie; 
2.  
3. import java.io.File; 
4. import java.util.Iterator; 
5. import java.util.Map; 
6. import java.util.TreeMap; 
7. import java.util.regex.Matcher; 
8. import jxl.*; 
9.  
10. public class ExcelDataimplements Iterator<Object[]> { 
11.     private Workbook book =null; 
12.     private Sheet sheet = null; 
13.     private int rowNum =0;//行数 
14.     private int curRowNo =0;//当前行数 
15.     private int columnNum =0;//列数 
16.     private String[] columnnName;//列名 
17. /*在TestNG中，由@DataProvider（dataProvider="name"）修饰的方法读取Exel时，调用此类的构造方法（此方法会得到列名并将当前行移到下一行）执行完后，
18. *转到TestNG自己的方法中去，然后由他们调用此类实现的hasNext()、next() 方法；
19. *得到一行数据，然后返回给由@Test（dataProvider="name"）修饰的方法，如此反复到数据读完为止。
20. * @param filepath Excel文件名
21. * @param casename用例名
22. */ 
23. public ExcelData(String filepath, String casename) { 
24.         try { 
25.             File directory = new File("."); 
26.             String ss = "open.anniewang.newexcel."; 
27.             book = Workbook.getWorkbook(new File(directory.getCanonicalPath() 
28.                     + "\\resources\\" 
29.                     + ss.replaceAll("\\.", Matcher.quoteReplacement("\\")) 
30.                     + filepath + ".xls")); 
31.             this.sheet = book.getSheet(casename); 
32.             this.rowNum = sheet.getRows(); 
33.  
34.             Cell[] c = sheet.getRow(0); 
35.             this.columnNum = c.length; 
36.             columnnName = new String[c.length]; 
37.             for (int i =0; i < c.length; i++) { 
38.                 columnnName[i] = c[i].getContents().toString(); 
39.             } 
40.             this.curRowNo++; 
41.  
42.         } catch (Exception e) { 
43.             e.printStackTrace(); 
44.         } 
45.     } 
46.  
47.     @Override 
48.     public boolean hasNext() { 
49. /**
50. *方法功能：是否有下一条数据
51. *如果行数为0即空sheet或者 当前行数大于总行数
52. *就关闭对excel的操作返回false，否则返回true
53. */ 
54. if (this.rowNum ==0 ||this.curRowNo >=this.rowNum) { 
55.             try { 
56.                 book.close(); 
57.             } catch (Exception e) { 
58.                 e.printStackTrace(); 
59.             } 
60.             return false; 
61.         } else 
62.             return true; 
63.     } 
64.  
65.     @Override 
66.     public Object[] next() { 
67. /* 方法功能：得到并返回下一行数据
68. * 使用for将一行的数据放入TreeMap中（TreeMap默认按照Key值升序排列，HashMap没有排序）
69. *然后将Map装入Object[]并返回，且将curRowNo当前行下移
70. */ 
71. Cell[] c = sheet.getRow(this.curRowNo); 
72.         Map<String, String> s = new TreeMap<String, String>(); 
73.         for (int i =0; i <this.columnNum; i++) { 
74.             String temp = ""; 
75.             try { 
76.                 temp = c[i].getContents().toString(); 
77.             } catch (ArrayIndexOutOfBoundsException ex) { 
78.                 temp = ""; 
79.             } 
80.             s.put(this.columnnName[i], temp); 
81.         } 
82.  
83.         Object r[] = new Object[1]; 
84.         r[0] = s;
85.         this.curRowNo++; 
86.         return r; 
87.     } 
88.  
89.     @Override 
90.     public void remove() { 
91.         throw new UnsupportedOperationException("remove unsupported."); 
92.     } 
93. } 

package com.annie;import java.io.File;import java.util.Iterator;import java.util.Map;import java.util.TreeMap;import java.util.regex.Matcher;import jxl.*;public class ExcelData implements Iterator<Object[]> {private Workbook book = null;private Sheet sheet = null;private int rowNum = 0;//行数private int curRowNo = 0;//当前行数private int columnNum = 0;//列数private String[] columnnName;//列名/*在TestNG中，由@DataProvider（dataProvider="name"）修饰的方法读取Exel时，调用此类的构造方法（此方法会得到列名并将当前行移到下一行）执行完后，*转到TestNG自己的方法中去，然后由他们调用此类实现的hasNext()、next() 方法；*得到一行数据，然后返回给由@Test（dataProvider="name"）修饰的方法，如此反复到数据读完为止。* @param filepath Excel文件名* @param casename用例名 */ public ExcelData(String filepath, String casename) {try {File directory = new File(".");String ss = "open.anniewang.newexcel.";book = Workbook.getWorkbook(new File(directory.getCanonicalPath()+ "\\resources\\"+ ss.replaceAll("\\.", Matcher.quoteReplacement("\\"))+ filepath + ".xls"));this.sheet = book.getSheet(casename);this.rowNum = sheet.getRows();Cell[] c = sheet.getRow(0);this.columnNum = c.length;columnnName = new String[c.length];for (int i = 0; i < c.length; i++) {columnnName[i] = c[i].getContents().toString();}this.curRowNo++;} catch (Exception e) {e.printStackTrace();}}@Overridepublic boolean hasNext() {/***方法功能：是否有下一条数据*如果行数为0即空sheet或者 当前行数大于总行数*就关闭对excel的操作返回false，否则返回true*/ if (this.rowNum == 0 || this.curRowNo >= this.rowNum) {try {book.close();} catch (Exception e) {e.printStackTrace();}return false;} elsereturn true;}@Overridepublic Object[] next() {/* 方法功能：得到并返回下一行数据* 使用for将一行的数据放入TreeMap中（TreeMap默认按照Key值升序排列，HashMap没有排序）*然后将Map装入Object[]并返回，且将curRowNo当前行下移*/ Cell[] c = sheet.getRow(this.curRowNo);Map<String, String> s = new TreeMap<String, String>();for (int i = 0; i < this.columnNum; i++) {String temp = "";try {temp = c[i].getContents().toString();} catch (ArrayIndexOutOfBoundsException ex) {temp = "";}s.put(this.columnnName[i], temp);}Object r[] = new Object[1];r[0] = s;this.curRowNo++;return r;}@Overridepublic void remove() {throw new UnsupportedOperationException("remove unsupported.");}}

EXECL数据驱动：ExcelTest.xls

注意：此处的要用office2003扩展名为xls（office 2007 的excel 扩展名为xlsx），否则会报I/O 输入输出流的错误。

测试类调用：TheExcelTest.java

[java] view plaincopyprint?

1. package com.annie; 
2.  
3. import java.util.Iterator; 
4. import java.util.Map; 
5. import java.util.Set; 
6.  
7. import org.testng.annotations.DataProvider; 
8. import org.testng.annotations.Test; 
9.  
10. public class TheExcelTest { 
11.      
12.     @Test(dataProvider = "db1") 
13.     public void ts(Map<String, String> data)throws Exception{ 
14.         this.prmap(data); 
15.         System.out.println("=====over====="); 
16.         System.out.println(""); 
17.     } 
18.      
19.     @DataProvider(name ="db1") 
20.     public Iterator<Object[]> data()throws Exception{ 
21.         return (Iterator<Object[]>)new ExcelData("ExcelTest","testB"); 
22.     } 
23.      
24.     public  void prmap(Map<String,String>arr){ 
25.         Set<String> set=arr.keySet(); 
26.         Iterator<String> it=set.iterator(); 
27.         while(it.hasNext()){ 
28.             String s=(String)it.next(); 
29.              System.out.println(arr.get(s)); 
30.         } 
31.     } 
32.  
33. }