使用POI读取Excel测试用例

使用POI读取Excel测试用例

一、引入POI依赖jar

注意：poi.jar和poi-ooxml.jar版本要一致

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

二、读取Excel数据

1、使用字节输入流读取Excel文件

String fileName = "Excel文件";
InputStream is = new FileInputStream(new File(fileName));

2、创建工作簿对象

Workbook wb = null;

//根据后缀创建不同类型的工作簿对象
if (fileName.endsWith("xlsx")) {
    wb = new XSSFWorkbook(is);//Excel 2007
} else if (fileName.endsWith("xls")) {
    wb = new HSSFWorkbook(is);//Excel2003
}

3、读取工作表

//根据工作表名称读取工作表
Sheet sh = wb.getSheet("工作表名");
---------------------------------------------
//获取工作表总数
int sheets = wb.getNumberOfSheets();
//通过索引值读取工作表
Sheet sheetAt = wb.getSheetAt(0);

4、读取行数和列数

//行数
int rowNum = sh.getLastRowNum();

//根据第一行数据为基准，读取列数
int colNum = sh.getRow(0).getLastCellNum();

5、读取数据

//获取第m行数据
Row row = sh.getRow(m);
//读取第n列的值
Cell cell = row.getCell(n);
//数值类型
cell.getNumericCellValue();
//字符串类型
cell.getStringCellValue();
//布尔类型
cell.getBooleanCellValue();
//错误类型
cell.getErrorCellValue();
//公式类型
cell.getCellFormula();

1）读取数据到List<Map<String, Object>>中

//定义存放读取数据的List
List<Map<String, Object>> dataList = new ArrayList<>();

//定义存放标题的List
List<String> title = new ArrayList<>();

//存放每一条用例的map集合
Map<String, Object> data;

//获取第一行的标题行
Row titleRow = sh.getRow(0);

//列数
int colNum = titleRow.getLastCellNum();

//循环添加标题到存放标题的List中
for (int i = 0; i < colNum; i++) {
    title.add(titleRow.getCell(i).getStringCellValue());
}

//读取用例数据（从第二行开始，一般第一行都是title）
for (int i = 1; i <= rowNum; i++) {
    //实例化map集合
    data = new HashMap<>();
    //获取行对象
    Row row = sh.getRow(i);
    //循环打印每一列的数据
    for (int j = 0; j < colNum; j++) {
        //获取列对象
        Cell cell = row.getCell(j);
        if (cell == null) {
            //判断如果列对象为空,则在map集合中写去null
            data.put(title.get(j), null);
            continue;
        }
        //获取列的类型
        CellType cellType = cell.getCellType();
        //通过类型转换对应的数据类型
        switch (cellType) {
            case NUMERIC:
                //数值类型
                data.put(title.get(j), (int) row.getCell(j).getNumericCellValue());
                break;
            case STRING:
                //字符串类型
                data.put(title.get(j), row.getCell(j).getStringCellValue());
                break;
            default:
                data.put(title.get(j), "");
                break;
        }
    }
    //把每条的用例集合添加到List中
    dataList.add(data);
}

根据上面excel获取的List转换为Object[][]

 ///根据excel获取的list转换为  Object[][]
public static Object[][] getObjArrByList(List<Map<String, Object>> dataList) {
    //定义一个二维数组
    Object[][] objArray = new Object[dataList.size()][1];
    //循环遍历添加到数组中
    for (int i = 0; i < dataList.size(); i++) {
        objArray[i][0] = dataList.get(i);
    }
    return objArray;
}

2）直接读取数据到二维数组Object[][]中（这样做的好处：可以直接在testng中进行参数化）

//定义存放标题的List
List<String> title = new ArrayList<>();

//定义存放读取数据的二维数组
Object[][] datas = new Object[rowNum][1];

//存放每一条用例的map集合
Map<String, Object> data;

//获取第一行的标题行
Row titleRow = sh.getRow(0);
//列数
int colNum = titleRow.getLastCellNum();

//循环添加标题到存放标题的List中
for (int i = 0; i < colNum; i++) {
    title.add(titleRow.getCell(i).getStringCellValue());
}

//读取用例数据
for (int i = 1; i <= rowNum; i++) {
    Row row = sh.getRow(i);
    data = new HashMap<>();
    //循环每一列的数据，然后添加到集合中
    for (int j = 0; j < colNum; j++) {
        //获取列对象
        Cell cell = row.getCell(j);
        if (cell == null) {
            //判断如果列对象为空,则在map集合中写去null
            data.put(title.get(j), null);
            continue;
        }
        //获取列的类型
        CellType cellType = cell.getCellType();
        //通过类型转换对应的数据类型
        switch (cellType) {
            case NUMERIC:
                //数值类型
                data.put(title.get(j), (int) row.getCell(j).getNumericCellValue());
                break;
            case STRING:
                //字符串类型
                data.put(title.get(j), row.getCell(j).getStringCellValue());
                break;
            default:
                data.put(title.get(j), "");
                break;
        }
    }
    //把第i行的map数据添加到二维数组中
    datas[i - 1][0] = data;
}

6、关闭流资源

is.close();

三、写入数据

1）获取工作表对象

//定义输入流对象
InputStream is = new FileInputStream(new File(fileName));
// 拿到文件转化为JavaPoi可操纵类型
Workbook wb = WorkbookFactory.create(is);
//获取到Workbook对象后，就可以关闭输入流了
is.close();
//获取工作表对象
Sheet sh = wb.getSheet("Sheet1");

2）给指定单元格添加值

1. 通过指定行列进行写入

Row row = sh.getRow(rowNum);
Cell cell = row.getCell(colNum);
if (cell == null) {
    row.createCell(colNum).setCellValue(value);
} else {
    cell.setCellValue(value);
}

2. 通过指定单元格坐标进行写入

String coordinate = "H5";
//获取单元格的row和cell
CellAddress address = new CellAddress(coordinate);
// 获取行
int rowNum = address.getRow();
Row row = sh.getRow(rowNum);
// 获取列
int colNum = address.getColumn();
Cell cell = row.getCell(colNum);
if (cell == null) {
    row.createCell(colNum).setCellValue(value);
} else {
    cell.setCellValue(value);
}

3）进行写入操作

//写入数据
FileOutputStream os = new FileOutputStream(new File(fileName));
wb.write(os);
os.flush();
os.close();

4）设置单元格背景色（读取用例后，用例的执行结果可以突出一点）

设置背景色

//创建单元格样式对象
CellStyle cellStyle = wb.createCellStyle();
//指定填充的颜色(17：GREEN绿色;10：RED红色)
//0-64都可选
cellStyle.setFillForegroundColor((short) 17);
//指定填充模式
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

给单元格添加背景色

Row row = sh.getRow(rowNum);
Cell cell = row.getCell(colNum);
cell.setCellStyle(cellStyle);