彻底解决C#实现DataTable导出EXCEL表格

/// 将DataTable数据导出到EXCEL，调用该方法后自动返回可下载的文件流
    /// </summary>
    /// <param name="dtData">要导出的数据源</param>
    public static void DataTable1Excel(System.Data.DataTable dtData)
    {
        System.Web.UI.WebControls.GridView gvExport = null;
        // 当前对话
        System.Web.HttpContext curContext = System.Web.HttpContext.Current;
        // IO用于导出并返回excel文件
        System.IO.StringWriter strWriter = null;
        System.Web.UI.HtmlTextWriter htmlWriter = null;

        if (dtData != null)
        {
            // 设置编码和附件格式
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            curContext.Response.Charset = "utf-8";

            // 导出excel文件
            strWriter = new System.IO.StringWriter();
            htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
            // 为了解决gvData中可能进行了分页的情况，需要重新定义一个无分页的GridView
            gvExport = new System.Web.UI.WebControls.GridView();
            gvExport.DataSource = dtData.DefaultView;
            gvExport.AllowPaging = false;
            gvExport.DataBind();

            // 返回客户端
            gvExport.RenderControl(htmlWriter);
            curContext.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\" />" + strWriter.ToString());
            curContext.Response.End();
        }
    }

  

    /// <summary>
    /// 直接输出Excel
    /// </summary>
    /// <param name="dtData"></param>
    public static void DataTable2Excel(System.Data.DataTable dtData)
　　　　{
        　　System.Web.UI.WebControls.DataGrid dgExport = null;
　　　　　　// 当前对话
　　　　　　System.Web.HttpContext curContext = System.Web.HttpContext.Current;
　　　　　　// IO用于导出并返回excel文件
　　　　　　System.IO.StringWriter strWriter = null;
　　　　　　System.Web.UI.HtmlTextWriter htmlWriter = null;

　　　　　　if (dtData != null)
　　　　　　{
　　　　　　　　// 设置编码和附件格式
　　　　　　　　curContext.Response.ContentType = "application/vnd.ms-excel";
　　　　　　　　curContext.Response.ContentEncoding =System.Text.Encoding.UTF8;
　　　　　　　　curContext.Response.Charset = "";
                
　　　　　　　　// 导出excel文件
　　　　　　　　strWriter = new System.IO.StringWriter();
　　　　　　　　htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

　　　　　　　　// 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
　　　　　　　　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>
    /// dtData是要导出为Excel的DataTable,FileName是要导出的Excel文件名(不加.xls)
    /// </summary>
    /// <param name="dtData"></param>
    /// <param name="FileName"></param>
    private void DataTable3Excel(System.Data.DataTable dtData, String FileName)
    {
        System.Web.UI.WebControls.GridView dgExport = null;
        //当前对话
        System.Web.HttpContext curContext = System.Web.HttpContext.Current;
        //IO用于导出并返回excel文件
        System.IO.StringWriter strWriter = null;
        System.Web.UI.HtmlTextWriter htmlWriter = null;

        if (dtData != null)
        {
            //设置编码和附件格式
            //System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)作用是方式中文文件名乱码
            curContext.Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
            curContext.Response.ContentType = "application nd.ms-excel";
            curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
            curContext.Response.Charset = "GB2312";

            //导出Excel文件
            strWriter = new System.IO.StringWriter();
            htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

            //为了解决dgData中可能进行了分页的情况,需要重新定义一个无分页的GridView
            dgExport = new System.Web.UI.WebControls.GridView();
            dgExport.DataSource = dtData.DefaultView;
            dgExport.AllowPaging = false;
            dgExport.DataBind();

            //下载到客户端
            dgExport.RenderControl(htmlWriter);
            curContext.Response.Write(strWriter.ToString());
            curContext.Response.End();
        }
    }

    /// <summary>
    /// Datatable to Excel带自定文件名的导出
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="FileName"></param>
    private void DataTable4Excel(System.Data.DataTable dt, string FileName)
    {
        System.IO.StringWriter stringWriter = new System.IO.StringWriter();
        HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
        DataGrid excel = new DataGrid();
        //System.Web.UI.WebControls.TableItemStyle AlternatingStyle = new TableItemStyle();
        System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();
        //System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle();
        //AlternatingStyle.BackColor = System.Drawing.Color.LightGray;
        headerStyle.BackColor = System.Drawing.Color.LightGray;
        headerStyle.Font.Bold = true;
        headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
        //itemStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center; ;

        //excel.AlternatingItemStyle.MergeWith(AlternatingStyle);
        excel.HeaderStyle.MergeWith(headerStyle);
        //excel.ItemStyle.MergeWith(itemStyle);
        excel.GridLines = GridLines.Both;
        excel.HeaderStyle.Font.Bold = true;
        excel.DataSource = dt.DefaultView;   //输出DataTable的内容
        excel.DataBind();

        //for (int i = 0; i < excel.Items.Count; i++)
        //{
        //    //excel.Columns[3].ItemStyle.CssClass = "xlsText";
        //    //excel.Items[i].Cells[3].Style.Add("mso-number-format", "\"@\"");
        //    for (int j = 0; j < aColTxtFormat.Length; j++)
        //    {
        //        int nCol = aColTxtFormat[j];
        //        excel.Items[i].Cells[nCol].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
        //    }
        //}
        excel.RenderControl(htmlWriter);

        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename='"+FileName+".xls'");
        //HttpContext.Current.Response.Charset = "UTF-8";
        HttpContext.Current.Response.Charset = "UTF-8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
        //HttpContext.Current.Response.ContentType=".xls/.txt/.doc";image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
        HttpContext.Current.Response.ContentType = ".xls";
        HttpContext.Current.Response.Write(stringWriter.ToString());
        HttpContext.Current.Response.End();

    }