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 ? 

 

  • 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)

     

10 Replies

  • TanyaYatskovska's avatar
    TanyaYatskovska
    SmartBear Alumni (Retired)

    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

      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

        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