自动化测试过程中如何使用excel数据驱动(下)

上一篇文章中我分享了如何通过读取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);
		}

}