Downloading excel file from web api using closedxml

Question

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

                }

If you save the file, rename it to .zip, can you open it? I'm trying to figure out whether the internals are corrupted or whether the packaging gets corrupted in the transfer. — Francois Botha
I saved the workbook on the server and renamed it to .zip, Its possible to open it and see the structure. So it seems to be the transfer that corrupts it — TwoHeadedSquirrel
If you can open the .zip file, then it's not the transfer that corrupted it. Can you check the content length vs the exact length of the file when you save it on the server? — Francois Botha
Also have a look at stackoverflow.com/a/24129082/179494 - It seems downloading files via AJAX is not supported well. It may apply to you. — Francois Botha

Francois Botha Francois Botha · Accepted Answer · 2017-08-24T14:04:50

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);
        }
    }
}

Downloading excel file from web api using closedxml

3 Answers