Forum Discussion

ashutosh's avatar
ashutosh
Occasional Contributor
6 years ago

Save API Response as Excel for Content-Type: application/ms-excel

Hi , 

 

I have an API whose Raw Response is as below 

 

Request : GET {URL}?isActiveUser=true HTTP/1.1 

 

Raw Response : 

HTTP/1.1 200 OK
Cache-Control: private
Transfer-Encoding: chunked
Content-Type: application/ms-excel
Server: Microsoft-IIS/10.0
content-disposition: attachment; filename=AllUsers_Active.xls
X-AspNet-Version: 4.0.30319
Persistent-Auth: true
X-Powered-By: ASP.NET
Access-Control-Allow-Credentials: true
Access-Control-Allow-Origin: https://url.com
Access-Control-Allow-Headers: Accept,Content-Type
Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS
Date: Mon, 03 Jun 2019 14:42:21 GMT

 

<div>
<table cellspacing="0" rules="all" border="1" style="border-collapse:collapse;">
<tr>
<th scope="col">EmployeeNumber</th><th scope="col">FirstName</th><th scope="col">MiddleName</th><th scope="col">SurName</th><th scope="col">Phone</th><th scope="col">Title</th><th scope="col">RegionName</th><th scope="col">ReportsTo</th><th scope="col">ReportsToName</th><th scope="col">IsActive</th><th scope="col">Group</th>
</tr><tr>
<td>100221076</td><td>Pilu</td><td>&nbsp;</td><td>Goopos</td><td>&nbsp;</td><td>&nbsp;</td><td>Atlantic</td><td>&nbsp;</td><td>N/A</td><td>1</td><td>&nbsp;</td>
</tr><tr>
<td>100221845</td><td>Losedaci</td><td>&nbsp;</td><td>Siyezke</td><td>&nbsp;</td><td>&nbsp;</td><td>Atlantic</td><td>&nbsp;</td><td>N/A</td><td>1</td><td>&nbsp;</td>
</tr><tr>
<td>100224203</td><td>Euoxmi</td><td>&nbsp;</td><td>Olova</td><td>&nbsp;</td><td>&nbsp;</td><td>Atlantic</td><td>&nbsp;</td><td>N/A</td><td>1</td><td>&nbsp;</td>
</tr>

</table>
</div>

 

On UI ,  This API is used to save the Data of a Grid in Excel (based on the input Params)

As per other Forum Topics , in my Test Case  i added a Property Transfer Step and Data Sink Step to save the API Response in Excel. 

However ,  in the saved excel , I get all the API response Data in One Cell . Data is not structured as a grid .

 

Please see the document containing Prop Transfer and Data Sink settings and my excel output . Can you please help point out how to get the API response in Excel ? 

 

API Response MS Excel
  • NBorovykh's avatar
    NBorovykh
    6 years ago

    ashutosh,

     

    Here is just a quick tip for you: saving a response content to a file can be done with the "Create File" step:

     

    (the actual content has the html format rather than xls so Excel shows the warning when opening the file, but still can open it)

     

  • TanyaYatskovska's avatar
    TanyaYatskovska
    SmartBear Alumni (Retired)
    6 years ago

    Hi ashutosh,

     

    You are transferring HTML data to an Excel file. I'm not sure how HTML should be structured inside Excel. Could you please clarify this?

    • ashutosh's avatar
      ashutosh
      Occasional Contributor
      6 years ago

      Hi Tanya, 

       

      That is how the API response is received. When i hit this API on Swagger, in the Response i get an option to download the xlsx file.

       

      I am not sure how to test this API  where the Content-Type is application/ms-excel.

      • richie's avatar
        richie
        Community Hero
        6 years ago

        Hi ashutosh 

         

        I don't think I can help - but I'm curious the datatype defined in the response is application/ms-excel?

         

        I can see .html you mention in your RAW - but what is present in your outline tab - is it the .html? if it's .html can't you use xpath to do your assertions?

         

        My only other option and I doubt this will work is you can try using the Accept header on the request that generates the response.

         

        I use the 'Accept' header (value = application/json or application/xml) to switch the type of the response from .json to .xml or .xml to .json

         

        I don't think you can change .xlsx to something else (all the markup would get in the way I'm guessing) - but if the response includes .html -  I don't see why you can't switch it to .xml or .json.

         

        Obviously this is dependent on your application server.

         

        Doubt I've helped - there's probably other reasons why it won't work that I don't know - but I'd give it a try anyway, just in case.

         

        Cheers,

         

        richie