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?

Solution 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
    }
}

Solution 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

Comments

[j00b]

I tried that and found that it gives the following error when trying to open the file: "Excel cannot open the file '(filename)'.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."

[JustinStolle]

What version of Office/Excel are you using? You may need to upgrade or use a compatibility pack (see microsoft.com/en-us/download/details.aspx?id=3).

[JustinStolle]

I believe you would need Excel 2007 or later to read the newer Open Office XML compatible format that the EPPlus package creates.

[j00b]

I have Excel 2007. And want to output this file so that it opens in Excel 2007 with no errors. If you read my original post, the problem is that when I output the file to the STREAM ONLY, it gives me this error. I can open the file if I use the Save() method and save it to the hard drive. But when it goes to the stream it gives me these errors.

[JustinStolle]

Out of curiosity, does anything change if you use content type application/vnd.ms-excel?

[j00b]

No that is for the old format and cannot be opened. Gives corrupt file error as it should.

[JustinStolle]

let us continue this discussion in chat

[user1131926]

in vs2008 the exception is raised on Response.Flush().If I remove the method then the file opens fine without any error.

[Mahmoud Farahat]

Adding Response.End() solved my problem, Thanks 👍

[MikeDev]

Response.End() also worked for me. thanks Mahmoud. I didn't have to do anything else to fix.