HandleThatError HandleThatError - 1 month ago 20
C# Question

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

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
}

Answer

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