Asp.net匯出Excel（HTML輸出方法）

標籤：style   blog   http   color   os   io   資料   for   

主要思路：

執行個體化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中顯示的中文名。