SQL 导出Excel

/// <summary> /// /// </summary> /// <param name="dt"></param> /// <param name="strfilds">数据库字段</param> /// <param name="strheaders">Execl列头</param> /// <param name="title">文件名</param> public static void ExportToExcel(DataTable dt, string[] strfilds, string[] strheaders, string title) { GridView grid = new GridView(); grid.RowDataBound += new GridViewRowEventHandler(grid_RowDataBound); int intColCount; if (strfilds.Length > 0 && strfilds.Length == strheaders.Length) { intColCount = strheaders.Length; grid.AutoGenerateColumns = false; for (int i = 0; i < intColCount; i++) { BoundField bf = new BoundField(); bf.DataField = strfilds[i]; bf.HeaderText = strheaders[i]; grid.Columns.Add(bf); } } else { grid.AutoGenerateColumns = true; } SetStyle(grid); grid.DataSource = dt; grid.DataBind(); ExportToExcel(grid, title); } static void grid_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { e.Row.Cells[4].Text = LevelHelper.Convert(Convert.ToInt32(e.Row.Cells[4].Text)); e.Row.Cells[5].Text = Convert.ToDateTime(e.Row.Cells[5].Text).ToShortDateString(); e.Row.Cells[6].Text = StateHelper.Convert(int.Parse(e.Row.Cells[6].Text)); e.Row.Cells[7].Text = Convert.ToBoolean(e.Row.Cells[7].Text) ? "是" : "否"; } } private static void SetStyle(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; } private static void ExportToExcel(GridView gvw, string title) { string fileName; HttpContext.Current.Response.Buffer = false; HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.ContentType = "application/octet-stream"; fileName = string.Format(title + "{0:yyyyMMddHHmm}.xls", DateTime.Now); HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF7)); HttpContext.Current.Response.ContentType = "application/octet-stream"; HttpContext.Current.Response.Charset = "UTF-8"; HttpContext.Current.Response.HeaderEncoding = System.Text.Encoding.UTF8; //设置头部字符编码 HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; //设置内容字符编码 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; }