EPPlus Error When Outputting .XLSX to Response

Question

I have a weird issue here using EPPlus to create some .XLSX files. I have a package being created, and then being output to the response.

I have created a package as follows:

var file = new FileInfo(@"C:\Test.xlsx");
ExcelPackage package = new ExcelPackage(file);
//...code to output data...//
package.Save();

This saves the file to my local C: drive correctly, and when I open it it works great. No errors or anything, formatting is correct, etc.

However, I now wish to output this file to the response stream so I have modified the code I had to look like this:

ExcelPackage package = new ExcelPackage();
//...code to output data...//
MemoryStream result = new MemoryStream();
package.SaveAs(result);
context.Response.Clear();
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";                                                       
context.Response.AddHeader("Content-Disposition", "attachment;filename=MissionDetails.xlsx");
result.WriteTo(context.Response.OutputStream);
context.Response.End(); 

BUT when I run THIS code I get the following prompt when trying to open the Excel file:

Excel found unreadable content in filename.xlsx. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes

Clicking yes then displays the following prompt:

This file cannot be opened by using Microsoft Excel. Do you want to search the Microsoft Office Online Web site for a converter that can open the file?

I select No here and then it opens the Excel file and displays this error:

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

BUT the file then loads fine and appears to be formatted correctly and everything. But every time I try to open the file it gives the same prompts and error message.

Note: The code to output the data does not change for either of these scenarios.

Has anyone seen anything like this? Or have any idea what could cause this to save the file incorrectly only when outputting to the response?

Answer 1

I have found the solution to this problem! As expected it did have something to do with the response, as I was able to open the file when saved locally, but not through the response.

The issue here is that my code was wrapped in a try..catch block where the exception was being logged and displayed.

It came to my attention that when you call Response.End() a System.Threading.ThreadAbortException is raised. When this is raised, it seems the output of the error was being appended to the end of my file.

When I got rid of the error logging for that specific exception, it worked great!

Please refer to this post for more info http://epplus.codeplex.com/discussions/223843?ProjectName=epplus

//...output code...//
catch(Exception ex){
    if (!(ex is System.Threading.ThreadAbortException))
    {
        //Log other errors here
    }
}

Answer 2

thanks joob your link soved my problem it was calling "GetAsByteArray()". Making the as listed below and in the link you gave, i gess keeps the exception from being appended. by some majic.

you get and up vote!

mrxrsd
Editor

Aug 17, 2010 at 12:30 PM


Call response.clear before send stream back to client.

                    Response.Clear();
                     Response.AddHeader("content-disposition", "attachment;  filename=file.xlsx");
                     Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";                   
                     Response.BinaryWrite(pck.GetAsByteArray());
                     Response.End();

http://epplus.codeplex.com/discussions/223843?ProjectName=epplus