Upgrading to VS 2015, now having problems exporting Excel due to Response.End

Question

I'm in the process of upgrading a project from VS 2012 to 2015, I'm down to the final error, which is getting Excel files to export. I'm using NPOI to create the Excel file and the code below to export it. Once the code hits the Response.End() line, I get the following error message:

Exception Thrown: 'System.Threading.ThreadAbortException' in mscorlib.dll Additional information: Thread was being aborted.

The file still downloads however and opens up perfectly. I tried commenting out the Response.End() line, and received no error from the code, however, when opening the Excel file I get this error:

We found a problem with some content in 'ContactNPOI.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

So I assuming without Response.End() the stream isn't closing properly or something? I started to research and discovered that this is a known issue, and to try using HttpContext.Current.ApplicationInstance.CompleteRequest() instead of Response.End(). I tried this and received no error from the code, but unfortunately still received the above error when trying to open the Excel file.

Below is my code snippet, can anyone suggest a fix to this, or perhaps a cleaner way to export Excel files?

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet("Instructional Hrs");

//Create Header Row
var headerRow1 = sh.CreateRow(0);
headerRow1.CreateCell(0).SetCellValue("Jimmy G Rocks!!");

using (var stream = new MemoryStream())
{
    Response.Clear();
    wb.Write(stream);
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "ContactNPOI.xlsx"));
    Response.BinaryWrite(stream.ToArray());
    Response.Flush();
    Response.End();
    //HttpContext.Current.ApplicationInstance.CompleteRequest();
}

Couldn't reproduce your error. Your code was working for me. Note that [Response.End](https://msdn.microsoft.com/en-us/library/system.web.httpresponse.end(v=vs.110).aspx) throwing a `ThreadAbortException` is normal — krlzlx, Jun 12 '17 at 13:54
well thanks for trying...this same code worked fine in VS 2012, so I don't know if maybe I did something to hide the message years ago and don't remember or what. Do you know if there's a better way to export files that doesn't produce an error? — Jimmy Genslinger, Jun 12 '17 at 14:14
I used the exact same code as you to send files to the client, except I have a `Response.Close()` before the `Response.End()` but I don't think it makes a difference. Have you check this [question](https://stackoverflow.com/q/20988445/1351076)? — krlzlx, Jun 12 '17 at 14:34

score 0 · Answer 1 · answered Jun 12 '17 at 14:43

Thanks to @krlzlx for the assist...here's the solution that ultimately worked for me, that does not throw an error in either visual studio or upon opening the Excel file:

using (var stream = new MemoryStream())
{
    Response.Clear();
    wb.Write(stream);
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "CCR Student Instructional Hours.xlsx"));
    Response.BinaryWrite(stream.ToArray());
    Response.Flush();
    HttpContext.Current.ApplicationInstance.CompleteRequest();
    Response.Close();
}

I saw a lot of sites saying to replace the Response.End() with HttpContext.Current.ApplicationInstance.CompletelRequest(), but just swapping that line out was causing an error when opening the Excel sheet. After continuing to play around, I found that adding Response.Close() after the CompleteRequest line eliminated the Excel error, and now I'm downloading error free!!

Please note that I'm using NPOI to create the Excel file, and the wb variable is my NPOI-created workbook... — Jimmy Genslinger, Jun 12 '17 at 14:45

Upgrading to VS 2015, now having problems exporting Excel due to Response.End

1 Answers1