C# HttpGet response gives me an empty Excel file with EPPlus

Question

I've created an endpoint that generates an Excel file. It's supposed to function as a GET in case I want some other code to POST it to a different endpoint for emailing, or in case I want to just download the Excel file by hitting the endpoint manually in a browser. It's downloading the Excel file, but when I try to open it I see the message "Excel cannot open the file 'blahblah' 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."

After getting that error, I've tried changing the MIME type in my response content field and/or file extension, and the error goes away and the file opens with the following warning: "The file format and extension of "blah blah" don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?" If I open it anyway, the file is still empty.

Here is the code where I take the ExcelPackage I created and add it to the response.

var response = HttpContext.Current.Response;
response.ContentType = "application/vnd.openxmlformats-  officedocument.spreadsheetml.sheet";
var fileName = string.Format("blahblah-{0}.xls",     InstantPattern.CreateWithInvariantCulture("yyyy-dd-M-HH-mm-ss").Format(_clock.Now));
response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
response.BinaryWrite(excelPackage.GetAsByteArray());

I've tried adding in a different mime type like application/excel. I've tried using the .xlsx file extension instead of xls. Nothing has really worked. I know that the ExcelPackage workbook's worksheets actually have the data I want, though, because when I debug and hover over the objects I see the cell values that I'm expecting to make it into the file. So what am I doing wrong?

I've tried generating the excelPackage in two ways, both while inside a using block. Like this:

using (var excelPackage = new ExcelPackage())
{
    // generate and download excel file
}

And also like this:

using (var excelPackage = new ExcelPackage(new FileInfo(fileName)))
{
   // generate and download excel file
}

The mime type just tells the browser what app to use, so if the browser is opening Excel when you do your Get, your Mime Type is correct. If you then get an Excel error it's because whatever Bytes are being passed are not in the expected format. How are you generating the excelPackage? That is likely the issue. — Theo
@Theo I tried generating the ExcelPackage with the default constructor (no arguments) and also with the FileInfo constructor, passing in the same fileName. I'm currently doing all of this code inside a using block that I will edit into the original question. — HandleThatError
It could be that something else in your Controller Action is modifying your response content after you write the bytes to it. Try adding a response.End after writing the bytes. — Theo
@Theo When I do that, the download says "Failed - Network Error" and it doesn't complete. My controller action returns a Task<IHttpActionResult>, so I return Ok() after generating the excel file. — HandleThatError
If I save the excelPackage as a local file, I can open it without any problems. It has the expected content inside of it. It's only when I try to add it to the response that I get the errors. — HandleThatError

VDWWD VDWWD · Accepted Answer · 2016-11-01T13:17:28

I use this to send the Excel file to the browser.

HttpResponse Response = HttpContext.Current.Response;

//first convert to byte array
byte[] bin = excelPackage.GetAsByteArray();

//clear the buffer stream
Response.ClearHeaders();
Response.Clear();
Response.Buffer = true;

//add the content type
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

//set the content length, without it, length is set to -1 and could give errors
Response.AddHeader("content-length", bin.Length.ToString());

//add a filename
Response.AddHeader("content-disposition", "attachment; filename=\"" + fileName + ".xlsx\"");

//send the file to the browser
Response.OutputStream.Write(bin, 0, bin.Length);

//cleanup
Response.Flush();
HttpContext.Current.ApplicationInstance.CompleteRequest();

C# HttpGet response gives me an empty Excel file with EPPlus

1 Answers