导出到Excel的最佳方法是什么

本文介绍了导出到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?

推荐答案

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[^]

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的最佳方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！