导出到Excel的最佳方法是什么 [英] What is the best way to export to Excel
问题描述
我需要从serer导出到Excel.
我可以使用Microsoft.Office.Interop.Excel创建excel，然后将其填充并通过响应返回给客户端.但是此选项创建了COM对象，然后我需要释放所有COM对象(Workbook，Excel ...)并调用CollectionGarbage进行释放.
第二种方法是创建DataGrid，将其填充并通过response.Write(StringWriter)发送给客户端，无需处理COM对象.
我的猜测是，第一种选择更容易.
您有什么建议?
I need to export to Excel from the serer.
I can use Microsoft.Office.Interop.Excel to create excel, to fill it and return through response to the client. But this option creates COM object and I then need to release all COM object(Workbook, Excel...) and call CollectionGarbage to release.
The second way is to create DataGrid, to fill it and send to the client through response.Write(StringWriter) and NO need to dispose COM object
My guess, that the 1st option is more easy one.
What can you suggest?
推荐答案
Microsoft不建议在服务器上使用Office互操作对象:
http://support.microsoft.com/default.aspx?scid=kb; EN-US; q257757#kb2 [ ^ ]
Microsoft当前不建议并且不支持从任何无人参与的非交互客户端应用程序或组件(包括ASP，ASP.NET，DCOM和NT Services)自动化Microsoft Office应用程序，因为Office可能表现出不稳定在这种环境中运行Office时的行为和/或死锁.
我在Web应用程序中成功使用了与此类似的代码:
Microsoft does not recommend using office interop objects on a server:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2[^]
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
I successfully use code similar to this in my web applications:
With Me.Page
.Response.Clear()
.Response.AddHeader("content-disposition", "attachment;filename=file.xls")
.Response.Charset = ""
.Response.ContentType = "application/vnd.xls"
.Response.Write("<html><body>")
.Response.Write(divContents.InnerHtml)
.Response.Write("</body></html>")
.Response.End()
End Wi
divContents通常包含一个表或几个表.
如果您需要对正在创建的excel文档进行非常精确的控制(格式，公式等)，则可以使用CarlosAg的免费Excel Xml Writer库
http://www.carlosag.net/Tools/ExcelXmlWriter [
divContents usually contains a table or several tables.
If you need very precise control over the excel document you are creating (formatting, formulas, etc...) you can use CarlosAg''s free Excel Xml Writer Library
http://www.carlosag.net/Tools/ExcelXmlWriter[^]
我正在使用以下Export Utility方法将GridView数据导出到excel和它真的很好.
I am using following Export utility method to export GridView data into excel and it works really good.
public static void Export(string fileName, GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid
Table table = new Table();
table.GridLines = gv.GridLines;
// add the header row to the table
if (gv.HeaderRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
GridViewExportUtil.PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (gv.FooterRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}
// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
/// <summary>
/// Replace any of the contained controls with literals
/// </summary>
/// <param name="control"></param>
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}
if (current.HasControls())
{
GridViewExportUtil.PrepareControlForExport(current);
}
}
}
}
这篇关于导出到Excel的最佳方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
