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, Aug 24 '17 at 13:05
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, Aug 24 '17 at 13:35
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, Aug 24 '17 at 13:37
Also have a look at https://stackoverflow.com/a/24129082/179494 - It seems downloading files via AJAX is not supported well. It may apply to you. — Francois Botha, Aug 24 '17 at 13:41

score 3 · Answer 1 · answered Aug 24 '17 at 14:04

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

Thanks, but how does the javascript part look for web api call and download itself? Have the feeling that there is the issue. — TwoHeadedSquirrel, Aug 24 '17 at 14:27
I'm not a JavaScript expert. But look at the Q&A in https://stackoverflow.com/a/24129082/179494 . Looks like there is varied support for what you try to do. My recommendation is try to use a straight `` tag instead of downloading the file with an AJAX call — Francois Botha, Aug 24 '17 at 15:10

score 1 · Accepted Answer · answered Aug 24 '17 at 15:11

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

score 0 · Answer 3 · answered Aug 24 '17 at 10:55

0

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.

answered Aug 24 '17 at 10:55

Francois Botha

3,434
28
39

Unfortunately that also doesn't work. We are using Web API and not MVC. I receive the same result in the javascript, also with the "FileStreamResult" from that extension. – TwoHeadedSquirrel Aug 24 '17 at 11:37
Ok. I'll monitor this question and if there is a solution I'll add it to the that extension. – Francois Botha Aug 24 '17 at 13:03

Downloading excel file from web api using closedxml

3 Answers3