testng 接口测试，读取Excel表格数据，做数据驱动1(按照顺序读取Excel数据)

1、测试类

public class TestRegister {


    @Test(dataProvider="datas")
    public void test1(String username,String pwd){
        String url = "https://10.1.30.18:8443/demo";
        Map<String, String> params = new HashMap<String, String>();
        params.put("username", username);
        params.put("pwd", pwd);
        System.out.println(HttpUtils.doPost(params,url));
    }


    @DataProvider
    public Object[][] datas(){
        Object[][] datas = ExcelUtil.readExcel("C:\\Users\\liao\\Desktop\\register.xls",1,6,3,4);
        return datas;
    }

读取Excel

 

 

 

 

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory;

import java.io.File;
import java.io.IOException;

public class ExcelUtil {

//讀取Excel
/*
*startRow 开始列
*endRow  结束列
* startCell 开始行
* endCell 结束行
 */

    public static Object[] [] readExcel(String  url,int startRow,int endRow,int startCell,int endCell){

        Object[] [] datas = new Object[endRow-startRow+1][endCell-startCell+1];
        //获取Workbook对象
        try {
            File file = new File(url);
            Workbook workBook = WorkbookFactory.create(file);
            //获取sheet 对象
            Sheet sheet =  workBook.getSheet("Sheet1");
            DataFormatter  formatter = new DataFormatter();
            //获取行
            for(int i = startRow;i <= endRow;i++){
                Row row = sheet.getRow(i);
                for(int j = startCell;j <= endCell;j++){
                    Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    String value = formatter.formatCellValue(cell);
                    System.out.print(value+",");
                    datas[i-startRow][j-startCell] = value;
                }
                System.out.println();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return datas;
    }
}

HttpUtils类

package myutils;

import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.util.EntityUtils;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.*;

public class HttpUtils {


    public static String doPost(Map<String, String> params, String url) {
        //请求方式
        HttpPost post = new HttpPost(url);
        //请求参数
        List<BasicNameValuePair> parameters = new ArrayList<BasicNameValuePair>();
        Set<String> keys = params.keySet();
        for (String key : keys) {
            String value = params.get(key);
            //       System.out.println(key+","+value);
            parameters.add(new BasicNameValuePair(key, value));
        }
        String result = "";
        try {
            post.setEntity(new UrlEncodedFormEntity(parameters, "utf-8"));
            HttpClient client = HttpClients.createDefault();
            //发起请求
            HttpResponse httpResponse = client.execute(post);
            //返回结果
            int statusCode = httpResponse.getStatusLine().getStatusCode();
            result = EntityUtils.toString(httpResponse.getEntity());
            result = "code=" + statusCode + ",result = " + result;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static String doGet(Map<String, String> params, String url) {
        //拼接URL   https://10.1.30.18:8080/demo?param=value&param=value
        int target = 1;
        Set<String> keys = params.keySet();
        for (String key : keys) {
            if (target == 1) {
                url += "?" + key + "=" + params.get(key);
            } else {
                url += "&" + key + "=" + params.get(key);
            }
        }

        String result = "";
        //请求方式
        HttpGet get = new HttpGet(url);
        //发起请求
        HttpClient client = HttpClients.createDefault();
        try {
            HttpResponse httpResponse = client.execute(get);
            int code = httpResponse.getStatusLine().getStatusCode();
            result = EntityUtils.toString(httpResponse.getEntity()) + code;

        } catch (IOException e) {
            e.printStackTrace();
        }


        return result;
    }


}

 

需要jar

    <dependency>
        <groupId>org.testng</groupId>
        <artifactId>testng</artifactId>
        <version>7.4.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.httpcomponents/httpclient -->
    <dependency>
        <groupId>org.apache.httpcomponents</groupId>
        <artifactId>httpclient</artifactId>
        <version>4.5.2</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.httpcomponents/httpcore -->
    <dependency>
        <groupId>org.apache.httpcomponents</groupId>
        <artifactId>httpcore</artifactId>
        <version>4.4.14</version>
    </dependency>


    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.1.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.1.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
    <dependency>
        <groupId>commons-io</groupId>
        <artifactId>commons-io</artifactId>
        <version>2.11.0</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-math3 -->
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-math3</artifactId>
        <version>3.6.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-api -->
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-api</artifactId>
        <version>2.14.1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-core</artifactId>
        <version>2.14.1</version>
    </dependency>

    <!-- -->
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>