Forum Discussion

ashutosh's avatar
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-Headers: Accept,Content-Type
Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS
Date: Mon, 03 Jun 2019 14:42:21 GMT


<table cellspacing="0" rules="all" border="1" style="border-collapse:collapse;">
<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>



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
    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
      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
        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.