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

Answer 3

Instead of the WriteTo method, try this:

context.Response.BinaryWrite(package.GetAsByteArray());

Answer 4

page.aspx.cs code when button click for Export To Excel

string templateFileName = Server.MapPath("~/ReportingTemplate/test.xlsx");
                System.IO.FileInfo templateFile = new System.IO.FileInfo(templateFileName);

                String message = ExcelPackagePlusLibrary.EPPlus.ExportToExcel(templateFile, dt, false, exportFileName, Response, "Data", "Summary", "Please type the client name here");
                if (String.IsNullOrEmpty(message) == false)
                {
                    /* Exception occur. */
                }
                dt.Clear();
///////////////////////////////////////////////////////////////////////////////////////

    /// <summary>
            /// ExportToExcel is a method used for Export To Excel with template file.
            ///
            /// </summary>
            /// <param name="templateFile">The fully qualified name of the new file, or the relative file name. Do not end the path with the directory separator character.</param>
            /// <param name="dt">Datatable for export.</param>
            /// <param name="printHeaders">Datatable's header used or not, when Export it. </param>
            /// <param name="exportFileName">provide fileName only not path. </param>
            /// <param name="Response">System.Web.HttpResponse. </param>
            /// <param name="sheetNames">arg[0] means provide sheet name where you want to load data. \n (Optional Parameter) arg[1] means provide sheet name where you want to edit. (Optional Parameter) arg[2] means if your intention is to Edit sheet so provide searchText.</param>
            /// 
            public static string ExportToExcel(FileInfo templateFile, DataTable dt, bool printHeaders, string exportFileName, System.Web.HttpResponse Response, params String[] sheetNames)
            {
                try
                {
                    using (ExcelPackage p = new ExcelPackage(templateFile, false))
                    {
                        EPPlus.AddSheetWithTemplate(p, dt, sheetNames[0], printHeaders);


                        String[] clientName = exportFileName.Split(new char[] { '_' }, 2);

                        if (sheetNames.Count() > 2)
                        {
                            ExcelPackagePlusLibrary.EPPlus.EditSheet(p, sheetNames[1], sheetNames[2], clientName[0] ?? exportFileName);
                        }

                        Byte[] fileBytes = p.GetAsByteArray(); //Read the Excel file in a byte array

                        //Clear the response
                        Response.ClearHeaders();
                        Response.ClearContent();
                        Response.Clear();

                        //Response.Cookies.Clear();


                        //Add the header & other information
                        //Response.Cache.SetCacheability(System.Web.HttpCacheability.Private);
                        //Response.CacheControl = "private";
                        //Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
                        //Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
                        //Response.AppendHeader("Content-Length", fileBytes.Length.ToString());
                        //Response.AppendHeader("Pragma", "cache");
                        //Response.AppendHeader("Expires", "60");
                        Response.AddHeader("Content-Disposition",
                        "attachment; " +
                        "filename=" + exportFileName + "; " +
                        "size=" + fileBytes.Length.ToString() + "; " +
                        "creation-date=" + DateTime.Now.ToString("R").Replace(",", "") + "; " +
                        "modification-date=" + DateTime.Now.ToString("R").Replace(",", "") + "; " +
                        "read-date=" + DateTime.Now.ToString("R").Replace(",", ""));

                        //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        Response.ContentType = "application/x-msexcel";

                        //Write it back to the client
                        Response.BinaryWrite(fileBytes);
                        Response.Flush();
                        Response.Close();

                        /* Download to Client Side. */
                        //DirectoryInfo dir = new DirectoryInfo(Server.MapPath("~/Testing/Downloaded/" + DateTime.Now.ToString("MM-dd-yyyy")));
                        //if (!dir.Exists)
                        //{
                        //    dir.Create();
                        //}
                        //File.WriteAllBytes(dir.FullName + "\\" + fileName, fileBytes);

                        return String.Empty;
                    }
                }
                catch (Exception ex)
                {
                    _ErrorMessage = ex.Message.ToString();
                    return _ErrorMessage;
                }
            }

Answer 5

I could not get EPPlus to open from IE, it saved fine and opened without errors. (chrome worked fine for both save and open). The suggestions here didn't work for me - potentially becuase I was using an aspx file, not an ashx file which did work for me in the end.

After hours of searching I ended up using an ashx file as per this stackoverflow post and I have it working in all browsers now without warnings/corupt file messages using EPPlus.

Additional resources that may help others:

• http://epplus.codeplex.com/discussions/223843?ProjectName=epplus
• EPPlus Error When Outputting .XLSX to Response

I hope this save someone else some time.