前两天总结了些关于Excel和CSV结合TestNG进行数据驱动测试的例子,对于Excel存放TestCase和关键字如何进行解析,也做了对应的总结,希望在学习的路上勇往直前,有不对的地方,希望大家指出,共同学习共同进步。
采用的是POI对Excel进行的解析,需要的Jar包文件:
poi-3.11-20141221.jar
poi-ooxml-3.11-20141221.jar
poi-ooxml-schemas-3.11-20141221.jar
xmlbeans-2.6.0.jar
代码如下:
package com.util.datadriver;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/** * 通过POI对Excel中的数据进行基本的操作 * * 需要导入额jar包: poi-3.11-20141221.jar、 poi-ooxml-3.11-20141221.jar、 * poi-ooxml-schemas-3.11-20141221.jar、 xmlbeans-2.6.0.jar * */public class ExcelController { Workbook workbook = null; Sheet sheet = null; public void setExcelFile(String filePath, String fileName, String sheetName) { try { FileInputStream fis = new FileInputStream(new File(filePath)); String type = fileName.substring(fileName.indexOf(".")); if (type.equals(".xlsx")) { workbook = new XSSFWorkbook(fis); } else if (type.equals(".xls")) { workbook = new HSSFWorkbook(fis); } sheet = workbook.getSheet(sheetName); fis.close(); } catch (Exception e) { e.printStackTrace(); } } //获取单元格的值 public String getCellData(int row, int column){ try { //获得Cell单元格对象 Cell cell = sheet.getRow(row).getCell(column); //设置cell返回值的类型 cell.setCellType(Cell.CELL_TYPE_STRING); //获取到cell单元格中的值 String cellData = cell.getStringCellValue(); return cellData; } catch (Exception e) { throw(e); } } //设置单元格的值 @SuppressWarnings("static-access") public void setCellData(String value, int rowCount, int columnCount, String filePath){ try { Row row = sheet.getRow(rowCount); Cell cell = row.getCell(columnCount,row.RETURN_BLANK_AS_NULL); if(cell == null){ row.createCell(columnCount).setCellValue(value); }else { cell.setCellValue(value); } FileOutputStream fos = new FileOutputStream(new File(filePath)); workbook.write(fos); fos.flush(); fos.close(); } catch (Exception e) { e.printStackTrace(); } } //获取Excel的行数 public int getColCount(Sheet sheet1){ int firstColCount = sheet1.getFirstRowNum(); int lastColCount = sheet1.getLastRowNum(); int sumColCount = lastColCount - firstColCount + 1; return sumColCount; } //获取Excel中每行的数据,并用数组返回每行所有数据,方便与TestNG做数据驱动 public Object[][] getExcelData(String filePath, String fileName, String sheetName) throws Exception { int sumRowCount = getColCount(sheet);; List