使用apache-poi使用放心的json响应更新值中的excel [英] Updating excel with values from rest assured json response using apache-poi
问题描述
我是Rest Asured的新手，正在寻求帮助.我编写了代码，以从Excel工作表中读取值，并将其作为路径参数传递给我的放心"测试.并且这按预期工作.但是，从我发出的发布请求中获取json响应后，我想使用响应中的值更新相同的excel.
I am a newbie with Rest Assured and seek help. I have written code to read values from an excel sheet and pass them as a path params in my Rest Assured tests. And this works as expected. However after I get the json response from the post request I make, I want to update the same excel with values from the response.
excel具有称为火车编号"，旅程日期"和各种类别代码的列，如其他列，例如头等舱代码，商务舱代码，标准舱代码等应显示其可用性的东西，诸如此类:
The excel has columns called Train number, date of journey and various class codes as other columns like first class code, business class code, standard class code that should show the availabality in them and so on something like this:
最初:
ServiceName | Date | A0 | A1 | A2 | ...... 45 columns
9008 |2019-07-28| 0 | 0 | 0 |....... 45 columns
测试后:
ServiceName | Date | A0 | A1 | A2 | ...... 45 columns
9008 |2019-07-28| 45 | 23 | 64 |....... 45 columns
我遵循apache-poi文档从excel中读取值，并使用支持的方法读取行，列并获取单元格值.但是找不到任何建议如何使用Json响应中的值更新相同excel的信息.
I have followed apache-poi documentation to read values from excel with supporting methods to read rows, columns and getting cell value. However could not find anything that suggests how to update the same excel with the values from Json response.
这些是我用来从excel读取值的测试和数据提供程序方法
These are the test and data provider methods I have come up for reading values from excel
@DataProvider(name="logicalAvProvider")
String [][] getLogicalAv() throws IOException
{
String path = "C:\\Misc\\LogAv.xlsx";
int rownum = XLUtils.getRowCount(path, "LogAv");
int colcount=XLUtils.getCellCount(path, "LogAv",rownum );
String LogAvData[][] = new String[rownum-1][colcount];
for (int i=1; i <rownum; i++ ) {
for (int j=0; j<colcount; j++) {
LogAvData[i-1][j] = XLUtils.getCellData(path, "LogAv", i, j);
//System.out.println("Data is " +LogAvData[i-1][j]);
}
}
return(LogAvData);
}
@Test(dataProvider="logicalAvProvider")
public void LogicalAvailablity(String ServiceName, String Date) throws IOException {
Response res=
given()
//.log().all()
.spec(reqSpec)
.pathParams("service_name", ServiceName, "travel_date", Date)
.when()
.get(EndPoints.LOGICAL_AVAILABILTY)
.then()
.spec(resSpec)
.extract().response();
//.log().body();
}
这是我们在执行发布请求后看到的响应.我需要在excel的相应列下可以更新的数字.
This is the sort of response we see after doing the post request. I need the number available to be updated under the respective columns in the excel.
[
{
"od_pair": "7015400:8727100",
"buckets": [
{
"bucket": "C00",
"original": 2,
"available": 2
},
{
"bucket": "A01",
"original": 76,
"available": 0
},
{
"bucket": "B01",
"original": 672,
"available": 477
},
{
"bucket": "B03",
"original": 578,
"available": 383
}
]
}
]
有关使用值更新excel工作表的方法的建议.我不希望有人能给我确切解决我的问题的方法，但是，对此方法的任何建议或我可以参考的参考都将不胜感激.
An advice on the approach to update the excel sheet with the values. I am not expecting anyone to give me the exact solution of my problem but any advice on the approach or any reference that I can refer to achieve this would be highly appreciated.
推荐答案
从响应中提取所需的数据，然后将其写入Excel工作表. 使用restAssured/JsonPath可以从API响应中提取数据. 使用apachePOI，您应该可以写入Excel，有关更多详细信息，请参考下面的链接: https://www. codejava.net/coding/how-to-to-write-excel-files-in-java-using-apache-poi
Extract the required data from response and then write it to excel sheet. Using restAssured/JsonPath you can extract data from API response. Using apachePOI you should be able to write into Excel, refer below link for more details: https://www.codejava.net/coding/how-to-write-excel-files-in-java-using-apache-poi
让我们知道您是否遇到任何问题
Let us know if you run into any issues
这篇关于使用apache-poi使用放心的json响应更新值中的excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
