.NET 导出Excel

ExportData类：

/// <summary>
    /// xxx
    /// 导出报表数据存入word或excel文件
    /// 2012-03-21
    /// </summary>
    public class ExportData
    {
        #region 构造函数
         public ExportData()
         {
             //
             // TODO: 在此处添加构造函数逻辑
             //
         }
         #endregion
 
         #region 导出页面或web控件方法
         /// <summary>
         /// 将Web控件或页面信息导出(不带文件名参数)
         /// </summary>
         /// <param name="source">控件实例</param>        
         /// <param name="DocumentType">导出类型:Excel或Word</param>
         public void ExportControl(System.Web.UI.Control source, string DocumentType)
         {
             //设置Http的头信息,编码格式
             if (DocumentType == "Excel")
             {
                 //Excel            
                 HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ HttpUtility.UrlEncode("下载文件.xls",System.Text.Encoding.UTF8));
                 HttpContext.Current.Response.ContentType = "application/ms-excel";
             }
 
             else if (DocumentType == "Word")
            {
                 //Word
                 HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ HttpUtility.UrlEncode("下载文件.doc",System.Text.Encoding.UTF8));
                 HttpContext.Current.Response.ContentType = "application/ms-word";
             }
 
             HttpContext.Current.Response.Charset = "UTF-8";   
             HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; 
 
             //关闭控件的视图状态
             source.Page.EnableViewState =false;    
 
             //初始化HtmlWriter
             System.IO.StringWriter writer = new System.IO.StringWriter() ;
             System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(writer);
             source.RenderControl(htmlWriter); 
 
             //输出
             HttpContext.Current.Response.Write(writer.ToString());
             HttpContext.Current.Response.End();
         }
 
         /// <summary>
         /// 将Web控件或页面信息导出(带文件名参数)
         /// </summary>
         /// <param name="source">控件实例</param>        
         /// <param name="DocumentType">导出类型:Excel或Word</param>
         /// <param name="filename">保存文件名</param>
         public void ExportControl(System.Web.UI.Control source, string DocumentType, string filename)
         {
             //设置Http的头信息,编码格式
            if (DocumentType == "Excel")
             {
                 //Excel            
                 HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ HttpUtility.UrlEncode(filename+".xls",System.Text.Encoding.UTF8));
                 HttpContext.Current.Response.ContentType = "application/ms-excel";            
            }
 
             else if (DocumentType == "Word")
             {
                 //Word
                 HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+ HttpUtility.UrlEncode(filename+".doc",System.Text.Encoding.UTF8));
                 HttpContext.Current.Response.ContentType = "application/ms-word";
             }
 
             HttpContext.Current.Response.Charset = "UTF-8";   
             HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; 
 
             //关闭控件的视图状态
             source.Page.EnableViewState =false;    
 
             //初始化HtmlWriter
             System.IO.StringWriter writer = new System.IO.StringWriter() ;
             System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(writer);
             source.RenderControl(htmlWriter); 
 
             //输出
             HttpContext.Current.Response.Write(writer.ToString());
             HttpContext.Current.Response.End();
         }
         #endregion

         #region   从DataSet到出到Excel
        /// <summary>
         /// 从DataSet到出到Excel方法一
        /// </summary>
        /// <param name="dtData">导出的数据</param>
        /// <param name="filename">导出的文件名</param>
         public void OutputExcel(System.Data.DataTable dtData, string filename) 
        {  
            System.Web.UI.WebControls.DataGrid dgExport = null;  
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;  
            System.IO.StringWriter strWriter = null;  
            System.Web.UI.HtmlTextWriter htmlWriter = null;   
            if (dtData != null)
            {
                curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename + ".xls", System.Text.Encoding.UTF8));
                curContext.Response.ContentType = "application/vnd.ms-excel";  
                curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;  
                curContext.Response.Charset = "";   
                strWriter = new System.IO.StringWriter();  
                htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);   
                dgExport = new System.Web.UI.WebControls.DataGrid();  
                dgExport.DataSource = dtData.DefaultView;  
                dgExport.AllowPaging = false;  
                dgExport.DataBind();   
                dgExport.RenderControl(htmlWriter);  
                curContext.Response.Write(strWriter.ToString());  
                curContext.Response.End();  
            }  
        }

         /// <summary>
         /// 导出Excel,需要导入com控件Microsoft Excel 12.0 Object Library
         /// </summary>
         /// <param name="dt">要导出的数据</param>
         /// <param name="isTitle">是否显示表头</param>
         public void OutputExcel(DataTable dt, bool isTitle)
         {
             Excel.ApplicationClass MyExcel = new Excel.ApplicationClass();
             MyExcel.Application.Workbooks.Add(true);

             Excel._Worksheet sheet = (Excel._Worksheet)MyExcel.Workbooks[1].Worksheets[1];
             sheet.Name = "sheet1";

             try
             {
                 if (isTitle)
                 {
                     for (int i = 0; i < dt.Columns.Count; i++)
                     {
                         MyExcel.Cells[1, i + 1] = dt.Columns[i].ColumnName;//生成表头
                     }
                     MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Bold = true;
                     MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Size = 12;
                     MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置对齐方式
                     MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Borders.LineStyle = "1";
                 }

                 int Rows = 1;
                 for (int i = 0; i < dt.Rows.Count; i++)
                 {
                     Rows += 1;
                     for (int j = 0; j < dt.Columns.Count; j++)
                     {
                         MyExcel.Cells[Rows, j + 1] = "" + dt.Rows[i][j].ToString();//导入数据
                     }
                     MyExcel.get_Range(MyExcel.Cells[Rows, 1], MyExcel.Cells[Rows, dt.Columns.Count]).Borders.LineStyle = "1";
                 }
                 sheet.Columns.AutoFit();//自动适应宽度
                 MyExcel.Visible = true;
             }
             catch (Exception) { }
             finally { }
         }

         /// <summary>
         /// 导出一定格式的Excle,需要导入com控件Microsoft Excel 12.0 Object Library
         /// </summary>
         /// <param name="strTitle">标题</param>
         /// <param name="dt">数据源</param>
         /// <param name="isTitle">是否显示列名</param>
         public void OutputExcel(string strTitle, DataTable dt, bool isTitle)
         {
             Excel.ApplicationClass MyExcel = new Excel.ApplicationClass();
             MyExcel.Application.Workbooks.Add(true);

             Excel._Worksheet sheet = (Excel._Worksheet)MyExcel.Workbooks[1].Worksheets[1];
             //System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strTitle + ".xls", System.Text.Encoding.UTF8));
             sheet.Name = "sheet1";

             try
             {
                 MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).MergeCells = true;//合并Excle单元格

                 MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Bold = true; //设置字体
                 MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Size = 20;
                 MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置对齐方式

                 MyExcel.Cells[1, 1] = strTitle;

                 if (isTitle)
                 {
                     for (int i = 0; i < dt.Columns.Count; i++)
                     {
                         MyExcel.Cells[2, i + 1] = dt.Columns[i].ColumnName;//生成表头
                     }
                     MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).Font.Bold = true;
                     MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).Font.Size = 12;
                     MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置对齐方式
                     MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).Borders.LineStyle = "1";
                 }

                 int Rows = 2;
                 for (int i = 0; i < dt.Rows.Count; i++)
                 {
                     Rows += 1;
                     for (int j = 0; j < dt.Columns.Count; j++)
                     {
                         MyExcel.Cells[Rows, j + 1] = "" + dt.Rows[i][j].ToString();//导入数据
                     }
                     MyExcel.get_Range(MyExcel.Cells[Rows, 1], MyExcel.Cells[Rows, dt.Columns.Count]).Borders.LineStyle = "1";
                 }
                 sheet.Columns.AutoFit();//自动适应宽度
                 MyExcel.Visible = true;
             }
             catch (Exception) { }
             finally { }
         }
         #endregion 

        #region 调用说明
        //方法ExportControl(System.Web.UI.Control source, string DocumentType,string filename)中
        //第一个参数source表示导出的页面或控件名,当为datagrid或dataList控件时，在导出Excel/word文件时，必须把控件的分页、排序等属性去除并重新绑定，
        //第二个参数DocumentType表示导出的文件类型word或excel
        //第三个参数filename表示需要导出的文件所取的文件名
        //调用方法：
        //ExportData export=new ExportData();        
        //export.ExportControl(this, "Word","testfilename");//当为this时表示当前页面
        //这是将整个页面导出为Word,并命名为testfilename
        #endregion

 注：选用ExportControl（）方法时

1. 提示：“控件“GridView1”必须放在具有 runat=server 的窗体标记内”，则需在页面中重写Page基类的VerifyRenderingInServerForm方法：

public override void VerifyRenderingInServerForm(Control control)
    {
        // Confirms that an HtmlForm control is rendered for
    }

MSDN对该方法的解释如下：

必须位于 <form runat=server> 标记中的控件可以在呈现之前调用此方法，以便在控件被置于标记外时显示错误信息。发送回或依赖于注册的脚本块的控件应该在 Control.Render 方法的重写中调用此方法。呈现服务器窗体元素的方式不同的页可以重写此方法以在不同的条件下引发异常。

如果回发或使用客户端脚本的服务器控件没有包含在 HtmlForm 服务器控件 (<form runat="server">) 标记中，它们将无法正常工作。这些控件可以在呈现时调用该方法，以在它们没有包含在 HtmlForm 控件中时提供明确的错误信息。

开发自定义服务器控件时，通常在为任何类型的输入标记重写 Render 方法时调用该方法。这在输入控件调用 GetPostBackEventReference 或发出客户端脚本时尤其重要。复合服务器控件不需要作出此调用。

      2.提示：******，则需在前台页面添加“EnableEventValidation="false"”，如：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="****.aspx.cs" EnableEventValidation="false" Inherits="**.***.****" %>