C# 输出 .XLSX 以响应时出现 EPPlus 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11549139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EPPlus Error When Outputting .XLSX to Response
提问by j00b
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.
我在这里使用 EPPlus 创建一些 .XLSX 文件时遇到了一个奇怪的问题。我正在创建一个包，然后将其输出到响应中。
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.
这会将文件正确保存到我的本地 C: 驱动器，并且当我打开它时效果很好。没有错误或任何东西，格式正确等。
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 文件时收到以下提示：
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
Excel 在 filename.xlsx 中发现不可读的内容。是否要恢复此工作簿的内容？如果您信任此工作簿的来源，请单击是
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?
无法使用 Microsoft Excel 打开此文件。是否要在 Microsoft Office Online 网站上搜索可以打开文件的转换器？
I select Nohere and then it opens the Excel file and displays this error:
我No在此处选择，然后打开 Excel 文件并显示此错误：
Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Excel 完成了文件级验证和修复。此工作簿的某些部分可能已被修复或丢弃。
BUTthe 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 onlywhen outputting to the response?
有没有人见过这样的事情？或者知道什么可能导致仅在输出到响应时才错误地保存文件？
回答by JustinStolle
Instead of the WriteTomethod, try this:
而不是WriteTo方法，试试这个：
context.Response.BinaryWrite(package.GetAsByteArray());
回答by j00b
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..catchblock where the exception was being logged and displayed.
这里的问题是我的代码被包装在一个try..catch块中，其中记录和显示了异常。
It came to my attention that when you call Response.End()a System.Threading.ThreadAbortExceptionis raised. When this is raised, it seems the output of the error was being appended to the end of my file.
它来到了我的注意，当你打电话到Response.End（）一System.Threading.ThreadAbortException上升。出现此问题时，似乎错误的输出已附加到我的文件末尾。
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
请参阅这篇文章了解更多信息http://epplus.codeplex.com/discussions/223843?ProjectName=epplus
//...output code...//
catch(Exception ex){
if (!(ex is System.Threading.ThreadAbortException))
{
//Log other errors here
}
}
回答by Muhammad Mubashir
page.aspx.cs code when button click for Export To Excel
单击按钮以导出到 Excel 时的 page.aspx.cs 代码
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;
}
}
回答by yedevtxt
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.
谢谢你的链接解决了我调用“GetAsByteArray()”的问题。使下面列出的内容和您提供的链接中的内容保持不变，以免附加异常。由一些神职人员。
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
http://epplus.codeplex.com/discussions/223843?ProjectName=epplus
回答by John
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.
我无法从 IE 打开 EPPlus，它保存得很好并且打开没有错误。（chrome 对保存和打开都很好）。这里的建议对我不起作用 - 可能是因为我使用的是 aspx 文件，而不是最终对我有用的 ashx 文件。
After hours of searching I ended up using an ashx file as per this stackoverflow postand I have it working in all browsers now without warnings/corupt file messages using EPPlus.
经过数小时的搜索，我最终按照这个 stackoverflow 帖子使用了一个 ashx 文件，并且我现在可以在所有浏览器中使用它，而没有使用 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.
我希望这可以节省其他人的时间。
