java post excel_Java 进阶 & 使用HttpClient发送多个post请求，并将响应结果写入Excel

接口测试中，经常会遇到一次发送多个http请求，然后获取响应结果中的重点字段。并将字段的值写入到某个文件中，以便查看各个请求的具体响应内容。

本文场景：从excel中读取要测试的VIN码，发送Http请求，调用接口，实现车辆VIN码定型并将定型结果处理后，写入到excel中。

具体实现步骤：

1、使用Excel4j工具包下的readExcel2List方法读取存取VIN码的excel，并将其存储到List中

2、使用httpclient工具包，创建post请求，并遍历存储VIN的List，将VIN传入到要发送的post请求中。

HttpClientUtils类:

package com.sc.vmi;

import org.apache.http.HttpEntity;

import org.apache.http.NameValuePair;

import org.apache.http.client.entity.UrlEncodedFormEntity;

import org.apache.http.client.methods.CloseableHttpResponse;

import org.apache.http.client.methods.HttpPost;

import org.apache.http.impl.client.CloseableHttpClient;

import org.apache.http.impl.client.HttpClients;

import org.apache.http.impl.conn.PoolingHttpClientConnectionManager;

import org.apache.http.message.BasicNameValuePair;

import org.apache.http.protocol.HTTP;

import org.apache.http.util.EntityUtils;

import java.io.IOException;

import java.util.ArrayList;

import java.util.List;

import java.util.Map;

public class HttpClientUtils{

// private static CookieStore cookieStore = new BasicCookieStore();

// private static CloseableHttpClient httpClient = HttpClients.custom().setDefaultCookieStore(cookieStore).build();

private static CloseableHttpClient httpClient;

private static CloseableHttpResponse httpResponse = null;

private static HttpEntity postEntity = null;

static {

PoolingHttpClientConnectionManager manager = new PoolingHttpClientConnectionManager();

manager.setMaxTotal(200); //连接池最大并发连接数

manager.setDefaultMaxPerRoute(200);//单路由最大并发数,路由是对maxTotal的细分

httpClient = HttpClients.custom().setConnectionManager(manager).build();

}

public static String doPost(String url, Map params){

HttpPost post = new HttpPost(url);

post.addHeader(HTTP.CONTENT_ENCODING, "UTF-8");

String ret = null;

try {

if (params != null) {

List list = new ArrayList();

for (Map.Entry entry : params.entrySet()) {

list.add(new BasicNameValuePair(entry.getKey(), entry.getValue().toString()));

}

postEntity = new UrlEncodedFormEntity(list);

post.setEntity(postEntity);

}

httpResponse = httpClient.execute(post);

if (httpResponse.getStatusLine().getStatusCode() == 200) {

ret = EntityUtils.toString(httpResponse.getEntity(), "UTF-8");

} else {

throw new Exception(

"System level error, Code=[" + httpResponse.getStatusLine().getStatusCode() + "].");

}

} catch (Exception e) {

e.printStackTrace();

}

return ret;

}

public static void close(){

if(postEntity!=null) {

try {

EntityUtils.consume(postEntity);

} catch (IOException e) {

}

}

if (httpResponse != null) {

try {

httpResponse.close();

} catch (IOException e) {

}

}

}

}

这里的post请求需要发送两次，在进行VIN定型之前，需要指定用户登录，然后再定型。

3、创建VIN定型 响应结果的VO，将post请求的响应结果中对应字段mapping到VO的对应字段

VO：VMIResult类

package com.sc.vmi;

import com.github.crab2died.annotation.ExcelField;

public class VMIResult {

@ExcelField(title = "vin")

private String vin;

@ExcelField(title = "resultCode")

private String resultCode;

@ExcelField(title = "resultMsg")

private String resultMsg;

@ExcelField(title = "vehicleSubModelId")

private String vehicleSubModelId;

public String getVin() {

return vin;

}

public void setVin(String vin) {

this.vin = vin;

}

public String getResultCode() {

return resultCode;

}

public void setResultCode(String resultCode) {

this.resultCode = resultCode;

}

public String getResultMsg() {

return resultMsg;

}

public void setResultMsg(String resultMsg) {

this.resultMsg = resultMsg;

}

public String getVehicleSubModelId() {

return vehicleSubModelId;

}

public void setVehicleSubModelId(String vehicleSubModelId) {

this.vehicleSubModelId = vehicleSubModelId;

}

@Override

public String toString() {

return "VMIResult{" +

"vin='" + vin + '\'' +

", resultCode='" + resultCode + '\'' +

", resultMsg='" + resultMsg + '\'' +

", vehicleSubModelId='" + vehicleSubModelId + '\'' +

'}';

}

}

4、使用Excel4j工具包下的exportObjects2Excel方法将响应结果指定字段的值写入到excel中

VMITest 类:

package com.sc.vmi;

import com.alibaba.fastjson.JSON;

import com.alibaba.fastjson.JSONObject;

import com.github.crab2died.ExcelUtils;

import java.io.File;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class VMITest {

static List stringList = new ArrayList();

public static void main(String[] args) {

getVMIResult();

}

private static void login() {

Map map = new HashMap();

map.put("j_username", "vip");

map.put("j_password", "1");

map.put("captchaStr", "1");

String loginUrl = "http://**.**.**:8080/web-suite/j_spring_security_check";

HttpClientUtils.doPost(loginUrl,map);

}

private static String vtm(String vin) {

Map map = new HashMap();

map.put("vin", vin);

map.put("claimId", "589");

String vtmUrl = "http://**.**.**:8080/web-suite/metadata/getQuestionByVin";

return HttpClientUtils.doPost(vtmUrl,map);

}

private static void getVMIResult(){

String filePath = System.getProperty("user.dir")+File.separator+ "data"+File.separator+"vin.xlsx";

String destPath = System.getProperty("user.dir")+File.separator+ "data"+File.separator+"vinResult.xlsx";

try {

List> list = ExcelUtils.getInstance().readExcel2List(filePath);

for(List ll : list) {

for (String vin : ll) {

String[] vinRes = vtmPost(vin).split(";");

VMIResult vmiResult = new VMIResult();

vmiResult.setVin(vinRes[0]);

vmiResult.setResultCode(vinRes[1]);

vmiResult.setResultMsg(vinRes[2]);

vmiResult.setVehicleSubModelId(vinRes[3]);

stringList.add(vmiResult);

}

}

System.out.println(stringList.toString());

ExcelUtils.getInstance().exportObjects2Excel(stringList,VMIResult.class,destPath);

} catch (Exception e) {

e.printStackTrace();

}

}

private static String vtmPost(String vin){

login();

String result = vtm(vin);

JSONObject jsonObject = (JSONObject) JSON.parse(result);

String resultCode = jsonObject.get("resultCode").toString();

String resultMsg = jsonObject.get("resultMsg").toString();

String vehicleSubModelId = getStringValue(jsonObject,"vehicleSubModelId");

if(vehicleSubModelId.equals("") && "1".equals(resultCode)){

vehicleSubModelId = "多个款型";

resultMsg = "二步定款";

}

if(resultCode.equals("-3")){

vehicleSubModelId = "无";

resultMsg = "定型失败";

}

if(resultCode.equals("-20")){

vehicleSubModelId = "无";

}

String str = vin+";"+resultCode+";"+resultMsg+";"+vehicleSubModelId;

HttpClientUtils.close();

return str;

}

private static String getStringValue(JSONObject jsonObject,String key){

return jsonObject.get(key)==null?"":jsonObject.get(key).toString();

}

}

写入到excel后的响应结果如下：

image.png