cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Occasional Contributor

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 ? 

 

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator

Re: Save API Response as Excel for Content-Type: application/ms-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)

 

Natalie
Customer Care Team

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others.↓↓↓↓↓
10 REPLIES 10
Community Manager

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

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?

---------
Tanya Gorbunova
SmartBear Community Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others.↓↓↓↓↓
Occasional Contributor

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

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.

Community Hero

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

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

 

 

if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the title of the post to something more descriptive? This will help people when searching for problems.
Occasional Contributor

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

Hi  Richie, 

 

I am attaching the Outline tab for the API (please check the screenshot Capture3.png) .

 

After looking into it further , i see that if I hit this API and get the Response and in the Response Section > HTML Tab >  Click on Save to file ... and Save it as filename.xls , then i am able to view the API Response Data (see attachment ExcelExport1.docx)

 

Now my goal to remove the manual intervention so that the each time i hit the API with certain input params , the xls response gets saved on a particular location.

 

any ideas if that is possible  ?

 

As again , thanks a lot to people helping me on this . 

Community Hero

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

Hey @ashutosh,

I cant help with automating the saving of the spreadsheet to your drive and subsequent verification of the spreadsheet contents...that would require extensive groovy i expect (i suspect saving the response to the drive would be fairly straightforward groovy, but the following verifications/assertions would be a lot more difficult).

However. You have an .html representation of the data that would populate your spreadsheet in your outline tab. You could split out the .html string to verify the data content is correct. It's fiddly, but is possible.

Cheers,

richie
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the title of the post to something more descriptive? This will help people when searching for problems.
Moderator

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

@richie , thanks for looking into this.

 

@ashutosh, have you managed to find a solution? If so, could you please share it?

 


Olga Terentieva
SmartBear Assistant Community Manager

Highlighted
Moderator

Re: Save API Response as Excel for Content-Type: application/ms-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)

 

Natalie
Customer Care Team

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others.↓↓↓↓↓
Moderator

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

Thanks for the tip, Natalie!

@ashutosh , does any of the above help?
We are looking forward to hearing from you.

 


Olga Terentieva
SmartBear Assistant Community Manager

Occasional Contributor

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

unfortunately i havent gotten time to look into this and have been pulled into something else. 

I will make sure to look into the valuable suggetions and post it on this thread.

New Here?
Join us and watch the welcome video:
Top Kudoed Authors