（六）Java版接口自动化-用Excel文件的方式做数据驱动

一、因为要用到Apache POI 和 Apache POI-OOXML 这两个类库，所以先在pom.xml导入对应的包，代码如下：

        <dependency>
            <groupId>org.apache.poigroupId>
            <artifactId>poiartifactId>
            <version>4.1.0version>
        dependency>

        <dependency>
            <groupId>org.apache.poigroupId>
            <artifactId>poi-ooxmlartifactId>
            <version>4.1.0version>
        dependency>

二、新增读取Excel的工具类，具体代码实现如下：

package com.automation.interfacetest.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @title:
 * @author: 2021/12/2814:08
 * @date: 2021/12/2814:08
 */
public class ExcelUtils {
//读取excel文件指定单元格数据(此方法只针对.xlsx后辍的Excel文件)
    public static String getCellData(String excelFilePath,String sheetName,int rowNum,int colNum) throws Exception{
        //声明一个file文件对象
        File file = new File(excelFilePath);
        //创建一个输入流
        FileInputStream in = new FileInputStream(file);
        //声明workbook对象
        Workbook workbook = null;
        //判断文件扩展名
        String fileExtensionName = excelFilePath.substring(excelFilePath.indexOf("."));
        if(fileExtensionName.equals(".xlsx")){
            workbook = new XSSFWorkbook(in);
        }else {
            workbook = new HSSFWorkbook(in);
        }

        //获取sheet对象
        Sheet sheet = workbook.getSheet(sheetName);
        //获取行对象
        Row row = sheet.getRow(rowNum);
        //获取列对象
        Cell cell = row.getCell(colNum);
        //获取指定行+列的数据
        String rowcoldata = getCellFormatValue(cell).toString();
        return rowcoldata;
    }

    //获取指定行的测试数据
    public static Map getRowData(String excelFilePath,String sheetName,int rowNum) throws Exception{
        //声明一个file文件对象
        File file = new File(excelFilePath);
        //创建一个输入流
        FileInputStream in = new FileInputStream(file);
        //声明workbook对象
        Workbook workbook = null;
        //判断文件扩展名
        String fileExtensionName = excelFilePath.substring(excelFilePath.indexOf("."));
        if(fileExtensionName.equals(".xlsx")){
            workbook = new XSSFWorkbook(in);
        }else {
            workbook = new HSSFWorkbook(in);
        }

        //获取sheet对象
        Sheet sheet = workbook.getSheet(sheetName);

        //获取行对象
        Row row = sheet.getRow(rowNum);

        //获取最后一列
        int lastCell = row.getLastCellNum();


        //用于map设置key值，自定义
        String columns[] = {"rowNumber", "testCaseName", "url", "params", "environment", "caseDescription"};
        //List< Map> list = new ArrayList<>();
        Map map = new HashMap<>();

        for (int i=0; i){
            //获取列对象
            Cell cell = row.getCell(i);
            //封装成map
            map.put(columns[i], getCellFormatValue(cell));
        }
        //将map放入List
        //list.add(map);
        return map;
    }


    //在EXCEL的执行单元格中写入数据(此方法只针对.xlsx后辍的Excel文件) rowNum 行号，colNum 列号
    public static void setCellData(String excelFilePath,String sheetName, int rowNum,int colNum,String Result) throws Exception{
        try {
            //声明一个file文件对象
            File file = new File(excelFilePath);

            //创建一个输入流
            FileInputStream in = new FileInputStream(file);

            Workbook workbook = new XSSFWorkbook(in);

            Sheet sheet = workbook.getSheet(sheetName);

            //获取行对象
            Row row = sheet.getRow(rowNum);

            //获取列对象，如果单元格为空，则返回null
            Cell cell = row.getCell(colNum);

            if(cell == null){
                cell=row.createCell(colNum);
                cell.setCellValue(Result);
            }else{
                cell.setCellValue(Result);
            }
            FileOutputStream out = new FileOutputStream(file);
            //将内容写入excel中
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //从EXCEL文件中获取所有测试数据
    public static Object[][] getTestData(String excelFilePath,String sheetName) throws IOException {
        //声明一个file文件对象
        File file = new File(excelFilePath);
        //创建一个输入流
        FileInputStream in = new FileInputStream(file);
        //声明workbook对象
        Workbook workbook = null;
        //判断文件扩展名
        String fileExtensionName = excelFilePath.substring(excelFilePath.indexOf("."));
        if(fileExtensionName.equals(".xlsx")){
            workbook = new XSSFWorkbook(in);
        }else {
            workbook = new HSSFWorkbook(in);
        }

        //获取sheet对象
        Sheet sheet = workbook.getSheet(sheetName);
        //获取sheet中数据的行数,行号从0始
        int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();

        List records = new ArrayList();
        //读取数据（省略第一行表头）
        for(int i=1; i){
            //获取行对象
            Row row = sheet.getRow(i);
            //声明一个数组存每行的测试数据
            String[] fields = new String[row.getLastCellNum()];
            for(int j=0; j){
                //获取每个单元格的数据
                Cell cell = row.getCell(j);
                fields[j] = getCellFormatValue(cell).toString();
                }
            records.add(fields);
        }
        //将list转为Object二维数据
        Object[][] results = new Object[records.size()][];
        //设置二维数据每行的值，每行是一个object对象
        for(int i=0; i){
            results[i]=records.get(i);
        }
        return results;
    }

    //格式化数值类型
    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        /**
         * getCellTypeEnum()方法是枚举类型，用于判断单元格值类型，有以下五种格式：
         *     _NONE(-1),
         *     NUMERIC(0),
         *     STRING(1),
         *     FORMULA(2),
         *     BLANK(3),
         *     BOOLEAN(4),
         *     ERROR(5);
         */
        switch(cell.getCellType()){
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                /**如果是数字类型转换成数字类型，但是初始化数字加的.0,因此可以转换成int类型去掉.0
                 * cell.getNumericCellValue()
                 */
                cellValue = new Double(cell.getNumericCellValue()).intValue();
                break;
            case BOOLEAN:
                break;
            case BLANK:
                break;
            case FORMULA:
                break;
            case ERROR:
                break;
            default:
                break;
        }
        return cellValue;
    }

}

三、联调测试

package com.automation.interfacetest;


import com.alibaba.fastjson.JSONObject;
import com.automation.interfacetest.base.RestTemplateRequest;
import com.automation.interfacetest.service.LoginService;
import com.automation.interfacetest.util.ExcelUtils;
import org.testng.Assert;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import java.lang.reflect.Method;


public class TestCase {

    public static String token;

    @BeforeClass
    public void testBefore() throws Exception{
        token =  LoginService.AppLogin("18683571131");

    }

    @DataProvider(name="testData")
    public static Object[][] data(Method method) throws Exception{
        return ExcelUtils.getTestData("D:\\接口自动化测试.xlsx","测试用例");
    }

    @Test(dataProvider = "testData",description = "影像云",groups = {"数字影像"})
    public void test001(String rowNumber,
                        String testCaseName,
                        String url,
                        String params,
                        String environment,
                        String caseDescription
    ) throws Exception{
        JSONObject object = JSONObject.parseObject(params);
        String result = RestTemplateRequest.postForObjectmap(url,object,token);
        System.out.println("打印结果:" + result);
    }


    @AfterClass
    public void testAfter() throws Exception{
        System.out.println("this is after class");
    }
}

运行结果：

 Excel文件内容：

更多参考：