上一篇文章中我分享了如何通过读取csv文件的形式绑定testng中的数据驱动注解,本文进一步分享下把csv文件换成excel多sheet文件后如何解析和绑定。
这里需要用到java jxl库来处理excel文件,返回testng接受的object[][]后就可以直接绑定数据驱动了,看下面的代码:
package cn.testfan.testng; import java.io.File; import java.io.IOException; import org.testng.annotations.DataProvider; import org.testng.annotations.Test; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; public class TestExcelWithDataProvider { public static Object[][] readData(String dataFile, String sheetName, int beginRowNum, int rowOffset, int beginColumnNum, int columnOffset) { return read(dataFile, sheetName, beginRowNum, rowOffset, beginColumnNum, columnOffset); } /** * @param dataFile 文件名 * @param sheetName excel中的sheet * @param beginRowNum 开始行 * @param rowOffset 行偏移量 * @param beginColumnNum 开始列 * @param columnOffset 列偏移量 * @return */ private static Object[][] read(String dataFile, String sheetName, int beginRowNum, int rowOffset, int beginColumnNum, int columnOffset) { File excelFile = new File(dataFile); Workbook wb = null; Object[][] data = null; try { wb = Workbook.getWorkbook(excelFile); Sheet sheet = wb.getSheet(sheetName); if (sheet == null) return null; int rows = sheet.getRows(); int cols = sheet.getColumns(); if (rowOffset == 0) { rowOffset = rows - beginRowNum; } else if (rows < (beginRowNum + rowOffset)) { rowOffset = rows - beginRowNum; } if (columnOffset == 0) { columnOffset = cols - beginColumnNum; } else if (cols < (beginColumnNum + columnOffset)) { columnOffset = cols - beginColumnNum; } data = new Object[rowOffset][columnOffset]; for (int i = beginRowNum; i < beginRowNum + rowOffset; i++) { for (int j = beginColumnNum; j < beginColumnNum + columnOffset; j++) { // 获取单元格数据 getCell(col,row); Cell cell = sheet.getCell(j, i); if (cell != null) { String celldata = cell.getContents().trim(); data[i - beginRowNum][j - beginColumnNum] = celldata; } } } } catch (Exception e) { throw new RuntimeException(e); } return data; } // 定义数据驱动 @DataProvider(name = "getData") public static Object[][] getData() throws IOException { return readData("C:\\test\\testfan_excel.xls", "sheet1", 0, 0, 0, 0); } // 测试案例绑定DataProvider后自动循环执行 @Test(dataProvider = "getData") public void testSearch(String p1, String p2) { System.out.println(p1 + " " + p2); } }
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!