最近做自动化需要从文件读取数据做参数化，网上发现一个不错的解决方案。
准备：新建一个excel文件，文件名为测试类名，sheet名为测试方法名
excel第一行为标题，从第二行开始为测试数据
build path：jxl.jar
code：
1 import java.io.FileInputStream; 2 import java.io.InputStream; 3 import java.util.HashMap; 4 import java.util.Iterator; 5 import java.util.Map; 6 7 import org.testng.Assert; 8 9 import jxl.*; 10 11 /** 12 * Excel放在Data文件夹下</p> 13 * Excel命名方式：测试类名.xls</p> 14 * Excel的sheet命名方式：测试方法名</p> 15 * Excel第一行为Map键值</p> 16 * 代码参考郑鸿志的Blog 17 * {@link www.zhenghongzhi.cn/post/42.html} 18 * @ClassName: ExcelDataProvider 19 * @Description: TODO(读取Excel数据) 20 */ 21 public class ExcelDataProvider implements Iterator<Object[]> { 22 23 private Workbook book = null; 24 private Sheet sheet = null; 25 private int rowNum = 0; 26 private int currentRowNo = 0; 27 private int columnNum = 0; 28 private String[] columnnName; 29 30 public ExcelDataProvider(String classname, String methodname) { 31 32 try { 33 34 int dotNum = classname.indexOf("."); 35 36 if (dotNum > 0) { 37 classname = classname.substring(classname.lastIndexOf(".") + 1, 38 classname.length()); 39 } 40 //从/data文件夹下读取以类名命名的excel文件 41 String path = "data/" + classname + ".xls"; 42 InputStream inputStream = new FileInputStream(path); 43 44 book = Workbook.getWorkbook(inputStream); 45 //取sheet 46 sheet = book.getSheet(methodname); 47 rowNum = sheet.getRows(); 48 Cell[] cell = sheet.getRow(0); 49 columnNum = cell.length; 50 columnnName = new String[cell.length]; 51 52 for (int i = 0; i < cell.length; i++) { 53 columnnName[i] = cell[i].getContents().toString(); 54 } 55 this.currentRowNo++; 56 57 } catch (Exception e) { 58 e.printStackTrace(); 59 Assert.fail("unable to read Excel data"); 60 } 61 } 62 63 public boolean hasNext() { 64 65 if (this.rowNum == 0 || this.currentRowNo >= this.rowNum) { 66 67 try { 68 book.close(); 69 } catch (Exception e) { 70 e.printStackTrace(); 71 } 72 return false; 73 } else { 74 // sheet下一行内容为空判定结束 75 if ((sheet.getRow(currentRowNo))[0].getContents().equals("")) 76 return false; 77 return true; 78 } 79 } 80 81 public Object[] next() { 82 83 Cell[] c = sheet.getRow(this.currentRowNo); 84 Map<String, String> data = new HashMap<String, String>(); 85 // List<String> list = new ArrayList<String>(); 86 87 for (int i = 0; i < this.columnNum; i++) { 88 89 String temp = ""; 90 91 try { 92 temp = c[i].getContents().toString(); 93 } catch (ArrayIndexOutOfBoundsException ex) { 94 temp = ""; 95 } 96 97 // if(temp != null&& !temp.equals("")) 98 // list.add(temp); 99 data.put(this.columnnName[i], temp); 100 } 101 Object object[] = new Object[1]; 102 object[0] = data; 103 this.currentRowNo++; 104 return object; 105 } 106 107 public void remove() { 108 throw new UnsupportedOperationException("remove unsupported."); 109 } 110 } |
