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: chu...
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)

     

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

 

 

  • ashutosh's avatar
    ashutosh
    Occasional Contributor
    6 years ago

    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 . 

    • richie's avatar
      richie
      Community Hero
      6 years ago
      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
      • Olga_T's avatar
        Olga_T
        SmartBear Alumni (Retired)
        6 years ago

        richie , thanks for looking into this.

         

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