CODEWITHSUNDEEP
javascriptexcelasp.net-web-apidownloadclosedxml
TwoHeadedSquirrel
TwoHeadedSquirrel

Reputation: 31

Downloading excel file from web api using closedxml

I can't get it to work to download an excel file that was created by closedxml through web API. If I save the file on the server it looks good, but as soon as I put it in a stream and return it to the web api, then only a corrupt file is recieved in the browser.

As suggested on several posts I use httpResponseMessage, but also in the browser the filename in the header never arrives.

We are using:

"Microsoft.AspNet.WebApi" version="5.2.3" targetFramework="net461

"ClosedXML" version="0.88.0" targetFramework="net461"

WebAPI Code:

 var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Parcel List");


            MemoryStream fs = new MemoryStream();
            wb.SaveAs(fs);
            fs.Position = 0;


            HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
            result.Content = new ByteArrayContent(fs.GetBuffer());
            result.Content.Headers.ContentLength = fs.Length;
            result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            {
                FileName = "List" + "_" + DateTime.Now.ToShortDateString() + ".xlsx"
            };
            result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");

            return result;

Here the javascript code:

  context.$http.post(config.get_API_URL() + 'api_call',  excel_list,
        {responseType: 'application/octet-stream'})
  .then(
    success_function,
    error_function)
}

success_function:

function(response) {

                  var headers = response.headers;
                 var blob = new Blob([response.body],
                                     {type:'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'},
                                   );

                 window.open(window.URL.createObjectURL(blob));

                }

Upvotes: 2

Views: 9352

Answers (3)

TwoHeadedSquirrel
TwoHeadedSquirrel

Reputation: 31

The problem seems to be that the response type for the web api call has to be {responseType: 'arraybuffer'} instead of {responseType: 'application/octet-stream'}

context.$http.post('api-url', excel_list, {responseType: 'arraybuffer'}) .then( success_function, error_function) }

Thanks anyhow for your quick help

Upvotes: 1

Francois Botha
Francois Botha

Reputation: 4839

I could successfully download a workbook with this code now:

using ClosedXML.Excel;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Threading;
using System.Threading.Tasks;
using System.Web.Http;

namespace ClosedXML.Extensions.WebApi.Controllers
{
    public class ValuesController : ApiController
    {
        public IHttpActionResult Get(int id)
        {
            return new TestFileActionResult(id);
        }
    }

    public class TestFileActionResult : IHttpActionResult
    {
        public TestFileActionResult(int fileId)
        {
            this.FileId = fileId;
        }

        public int FileId { get; private set; }

        public Task<HttpResponseMessage> ExecuteAsync(CancellationToken cancellationToken)
        {
            HttpResponseMessage response = null;

            var ms = new MemoryStream();
            using (var wb = new XLWorkbook())
            {
                var ws = wb.AddWorksheet("Sheet1");
                ws.FirstCell().Value = this.FileId;

                wb.SaveAs(ms);

                ms.Seek(0, SeekOrigin.Begin);

                response = new HttpResponseMessage(HttpStatusCode.OK);
                response.Content = new StreamContent(ms);
                response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
                response.Content.Headers.ContentDisposition.FileName = "test.xlsx";
                response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

                response.Content.Headers.ContentLength = ms.Length;
                ms.Seek(0, SeekOrigin.Begin);
            }

            return Task.FromResult(response);
        }
    }
}

Upvotes: 4

Francois Botha
Francois Botha

Reputation: 4839

Have a look at the Mvc extension package at https://www.nuget.org/packages/ClosedXML.Extensions.Mvc/

PS: I've been told I have to disclaim this everytime. I'm the maintainer of ClosedXML and ClosedXML.Extensions.Mvc.

Upvotes: 1

Related Questions