Stuck generating + sending .xlsx file from ASP.NET Web API backend to Vue.js frontend

Question

I am trying to generate an excel file in my ASP.NET Web API backend, and send it to my Vue.js front end for download.

The API request is being made by calling getQueryResults:

const apiClient = axios.create({
  baseURL: process.env.NODE_ENV == "production" ? PROD_URL : TEST_URL,
  withCredentials: true, // This is the default
  headers: {
    Accept: "application/json",
    "Content-Type": "application/json"
  }
});

getQueryResults(table, filters, selected, clientId, fundId, periodId, pageNumber, pageSize, exportExcel){
    return apiClient.post(`queryResults`, {
      responseType: 'blob',
      table,
      filters,
      selected,
      clientId,
      fundId,
      periodId,
      pageNumber,
      pageSize,
      exportExcel
    });
  }

In the backend, the excel file is generated inside of QueryResultsExport.cs (EPPlus is version 4.1.0):

public static MemoryStream exportToExcel(IQueryable<object> itemsToExport)
{
    if (itemsToExport.Count() == 0)
        return null;
    ExcelPackage excel = new ExcelPackage();
    ExcelWorksheet workSheet = excel.Workbook.Worksheets.Add("Query Results");

    DataTable dataTable = toDataTable(itemsToExport.ToList());
    workSheet.Cells["A1"].LoadFromDataTable(dataTable, true);

    /*
    string path = @"C:\Users\asdfq321\Downloads\test11.xlsx";
    Stream stream = File.Create(path);
    excel.SaveAs(stream);
    stream.Close();
    return null;
    */

    MemoryStream ms = new MemoryStream(excel.GetAsByteArray());
    excel.Dispose();
    return ms;            
}

You can see i commented out some code that saves the excel file locally as an .xlsx file, instead of returning a memorystream. I did this to ensure the problem was not in the excel generation. The file saved locally on the server appears to be correct, and it has a size of 3442 Bytes (this is relevant later on).

Next, the memorystream is sent back to the client:

[HttpPost]
[Route("queryResults")]
public HttpResponseMessage GetQueryResults([FromBody]Dictionary<string, object> queryParams)
{
        // continued from above...
        MemoryStream ms = QueryResultsExport.exportToExcel(queryItems);
        HttpResponseMessage httpResponseMessage = Request.CreateResponse(HttpStatusCode.OK);
        httpResponseMessage.Content = new StreamContent(ms);
        httpResponseMessage.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
        httpResponseMessage.Content.Headers.ContentDisposition.FileName = "asdfq.xlsx";
        httpResponseMessage.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream");
        return httpResponseMessage;
}

Finally, on the Vue front end, the response is handled:

.then(result => {
    var myBlob = new Blob([result.data], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'})

    const url = window.URL.createObjectURL(myBlob);
    const link = document.createElement('a');
    link.href = url;
    link.setAttribute('download', 'asdfq.xlsx'); //or any other extension
    document.body.appendChild(link);
    link.click();
})

The result is that a popup box appears prompting me to download an excel file. However, the file cannot be read, and when it is opened the message appears "We found a problem with some content in asdfq.xlsx. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes." When I click yes, I get "Excel cannot open the file asdfq.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

When I look at the response in the browser dev tools Network tab, I see that the response has Content-Length: 3442, which seems to be correct: this matches the excel file I successfully saved on the server earlier. However, the excel file that ends up being downloaded has a size of 5635 bytes.

Here is a picture of the response headers:

Here is a picture of Console.log(result):

I have tried various things like changing the responseType, the type passed into the blob constructor, the header ContentType, the Accept header in the request, how the memory stream is added to the response, etc, but I have not had any luck getting this to work: the result is always the same; a corrupted excel file with size of 5635 bytes instead of the expected 3442 bytes. Any help would be very much appreciated; thanks!

edit: I noticed in the code snippets I pasted, I have

 httpResponseMessage.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream");

In the back end, but in the front end I have

var myBlob = new Blob([result.data], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'})

I did try setting these both to "application/octet-stream", or both to "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", but it makes no difference.

I also tried different ways of generating the response:

//attempt 1
MemoryStream ms = QueryResultsExport.exportToExcel(queryItems);
HttpResponseMessage httpResponseMessage = Request.CreateResponse(HttpStatusCode.OK);
httpResponseMessage.Content = new StreamContent(ms);
httpResponseMessage.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
httpResponseMessage.Content.Headers.ContentDisposition.FileName = "asdfq.xlsx";
httpResponseMessage.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream");
// ms.Close();
return httpResponseMessage;

//attempt 2
MemoryStream ms = QueryResultsExport.exportToExcel(queryItems);
var result = new HttpResponseMessage(HttpStatusCode.OK)
{
    Content = new ByteArrayContent(ms.ToArray())
};
result.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
result.Content.Headers.ContentDisposition.FileName = "asdfq.xlsx";
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
return result;



// atempt 3
MemoryStream ms = QueryResultsExport.exportToExcel(queryItems);
ms.Position = 0;
var result = new HttpResponseMessage(HttpStatusCode.OK)
{
    Content = new ByteArrayContent(ms.GetBuffer())
};
result.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment"){FileName = "export.xlsx"};
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
result.Content.Headers.ContentType = new MediaTypeHeaderValue(contentType);
result.Content.Headers.Add("content-length", ms.Length.ToString());
return result;

Finally, in QueryResultsExport.exportToExcel, I also tried

var ms = new MemoryStream();
excel.SaveAs(ms);
return ms;

But none of this makes any difference.

Answer 1

I was able to get it working by sending the excel file as StringContent instead of ByteArrayContent, as seen here: How to download ByteArrayContent of HttpResponseMessage as zip

MemoryStream ms = QueryResultsExport.exportToExcel(queryItems);
string base64String = System.Convert.ToBase64String(ms.ToArray(), 0, ms.ToArray().Length);
var result = new HttpResponseMessage(HttpStatusCode.OK)
{
    Content = new StringContent(base64String)
};
return result;

.then(result => {
    const link = document.createElement('a');
    link.href = 'data:application/octet-stream;charset=utf-8;base64,' + result.data;
    link.target = '_blank';
    link.download = 'asdfq.xlsx';
    link.click();
})

Assigning a responseType in the api request doesn't seem necessary. Neither does setting a Content.Headers.ContentDisposition or Content.Headers.ContentType.