使用 apache-poi 使​​用来自放心 json 响应的值更新 excel [英] Updating excel with values from rest assured json response using apache-poi

问题描述

我是 Rest Assured 的新手，正在寻求帮助.我已经编写了代码来从 Excel 工作表中读取值，并将它们作为路径参数在我的 Rest Assured 测试中传递.这按预期工作.但是，从我发出的 post 请求中获得 json 响应后，我想使用响应中的值更新相同的 excel.

Excel 中有名为列车号、旅程日期和各种舱位代码的列，其他列如头等舱代码、商务舱代码、标准舱代码应显示其中的可用性等等，如下所示:

最初:

服务名称 |日期 |A0 |A1 |A2 |...... 45列9008 |2019-07-28|0 |0 |0 |....... 45 列

测试后:

服务名称 |日期 |A0 |A1 |A2 |...... 45列9008 |2019-07-28|45 |23 |64 |....... 45 列

我已经按照 apache-poi 文档从 excel 中读取值，并使用支持方法读取行、列和获取单元格值.但是找不到任何建议如何使用 Json 响应中的值更新相同的 excel 的内容.

这些是我为从 excel 读取值而提出的测试和数据提供程序方法

@DataProvider(name="logicalAvProvider")String [][] getLogicalAv() 抛出 IOException{字符串路径 = "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 

}

这是我们在执行 post 请求后看到的那种响应.我需要可以在 excel 的相应列下更新的数字.

<预><代码>[{"od_pair": "7015400:8727100",桶":[{"桶": "C00",原始":2，可用":2},{"桶": "A01",原始":76，可用":0},{"桶": "B01",原始":672，可用":477},{"桶": "B03",原始":578，可用":383}]}]

关于使用值更新 Excel 表的方法的建议.我不期望任何人为我提供我的问题的确切解决方案，但我将不胜感激任何有关该方法的建议或我可以参考的任何参考资料.

解决方案

从响应中提取所需的数据，然后将其写入 Excel 表格.使用 restAssured/JsonPath，您可以从 API 响应中提取数据.使用 apachePOI，您应该可以写入 Excel，有关详细信息，请参阅以下链接:https://www.codejava.net/coding/how-to-write-excel-files-in-java-using-apache-poi

如果您遇到任何问题，请告诉我们

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.

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:

Initially:

ServiceName  | Date     | A0 | A1 | A2 | ...... 45 columns

9008         |2019-07-28| 0  |  0 |  0 |....... 45 columns

After test:

ServiceName  | Date     | A0 | A1 | A2 | ...... 45 columns

9008         |2019-07-28| 45 | 23 | 64 |....... 45 columns

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.

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();

}

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
        }
]
}
]

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.

解决方案

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屋！