Asp.net导出Excel（HTML输出方法） 笔记

主要思路：

实例化Gridview，将值绑定后输出。。。（用烂了的方法）

 

贴上核心代码：

 public static void ExportToExcel(DataTable dataList, string[] fields, string[] headTexts, string title, string TableName)
    {
        GridView gvw = new GridView();
        int ColCount, i;
        //如果筛选的字段和对应的列头名称个数相对的情况下只导出指定的字段
        if (fields.Length != 0 && fields.Length == headTexts.Length)
        {
            ColCount = fields.Length;
            gvw.AutoGenerateColumns = false;

            for (i = 0; i < ColCount; i++)
            {
                BoundField bf = new BoundField();
                bf.DataField = fields[i];
                bf.HeaderText = headTexts[i];
                gvw.Columns.Add(bf);
            }
        }
        else
        {
            gvw.AutoGenerateColumns = true;
        }
        gvw.DataSource = dataList;
        SetStype(gvw);
        gvw.DataBind();
        ExportToExcel(gvw, title, TableName);
    }
    /// <summary>
    /// 导出数据到Excel
    /// </summary>
    /// <param name="DataList">IList Data</param>
    /// <param name="Fields">要导出的字段</param>
    /// <param name="HeadName">字段对应显示的名称</param>
    /// <param name="TableName">导出Excel的名称</param>
    public static void ExportToExcel(DataTable dataList, string[] fields, string[] headTexts, string TableName)
    {
        ExportToExcel(dataList, fields, headTexts, string.Empty, TableName);
    }

    /// <summary>
    /// 设置样式
    /// </summary>
    /// <param name="gvw"></param>
    private static void SetStype(GridView gvw)
    {
        gvw.Font.Name = "Verdana";
        gvw.BorderStyle = System.Web.UI.WebControls.BorderStyle.Solid;
        gvw.HeaderStyle.BackColor = System.Drawing.Color.LightCyan;
        gvw.HeaderStyle.ForeColor = System.Drawing.Color.Black;
        gvw.HeaderStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
        gvw.HeaderStyle.Wrap = false;
        gvw.HeaderStyle.Font.Bold = true;
        gvw.HeaderStyle.Font.Size = 10;
        gvw.RowStyle.Font.Size = 10;
    }
    /// <summary>
    /// 导出GridView中的数据到Excel
    /// </summary>
    /// <param name="gvw"></param>
    /// <param name="DataList"></param>
    private static void ExportToExcel(GridView gvw, string title, string TableName)
    {

        //int coun = ExistsRegedit();
        //string fileName = string.Format("DataInfo{0:yyyy-MM-dd_HH_mm}.xls", DateTime.Now);
        //if (coun >0)
        //{
        //    fileName = string.Format("DataInfo{0:yyyy-MM-dd_HH_mm}.xls", DateTime.Now);
        //    //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF7;
        //}
        //else
        //{
        //    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        //    //Page.RegisterStartupScript("mess", "<script>alert('该机器没有安装任何office软件');</script>");
        //    //return;
        //}

        for (int i = 0; i < gvw.Rows.Count; i++)
        {
            for (int j = 0; j < gvw.HeaderRow.Cells.Count; j++)
            {
                //这里给指定的列编辑格式，将数字输出为文本，防止数字溢出  
                gvw.Rows[i].Cells[j].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
            }
        }
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.ClearHeaders();
        //fileName = string.Format("DataInfo{0:yyyy-MM-dd_HH_mm}.xls", DateTime.Now);
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + TableName + System.Web.HttpUtility.UrlEncode(title) + DateTime.Now.ToShortDateString() + ".xls");
        HttpContext.Current.Response.Charset = "UTF-8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");


        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        StringWriter tw = new System.IO.StringWriter();
        HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        gvw.RenderControl(hw);
        if (!string.IsNullOrEmpty(title))
        {
            HttpContext.Current.Response.Write("<b><center><font size=3 face=Verdana color=#0000FF>" + title + "</font></center></b>");
        }
        HttpContext.Current.Response.Write(tw.ToString());
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.Close();
        HttpContext.Current.Response.End();
        gvw.Dispose();
        tw.Dispose();
        hw.Dispose();
        gvw = null;
        tw = null;
        hw = null;
    }

    //检查office的版本
    public static int ExistsRegedit()
    {
        int ifused = 0;
        RegistryKey rk = Registry.LocalMachine;
        RegistryKey akey = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\11.0\Excel\InstallRoot\");//查询2003
        RegistryKey akey07 = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0\Excel\InstallRoot\");//查询2007
        RegistryKey akeytwo = rk.OpenSubKey(@"SOFTWARE\Kingsoft\Office\6.0\common\");//查询wps
        //检查本机是否安装Office2003
        if (akey != null)
        {
            string file03 = akey.GetValue("Path").ToString();
            if (File.Exists(file03 + "Excel.exe"))
            {
                ifused += 1;
            }
        }

        //检查本机是否安装Office2007

        //if (akey07 != null)
        //{
        //    string file07 = akey.GetValue("Path").ToString();
        //    if (File.Exists(file07 + "Excel.exe"))
        //    {
        //        ifused += 2;
        //    }
        //}
        ////检查本机是否安装wps
        //if (akeytwo != null)
        //{
        //    string filewps = akeytwo.GetValue("InstallRoot").ToString();
        //    if (File.Exists(filewps + @"\office6\et.exe"))
        //    {
        //        ifused += 4;
        //    }
        //}
        return ifused;
    }

 

类似的代码网上很多，代码上也加了注释易懂。

我的这部分代码特殊之处是能够直接将数字以文本显示（也是网上找的解决方法），我就直接整合了。

输入的两个数组分别代表字段名还有在Excel中显示的中文名。