前台:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExcelDemo.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>按照Gridview样式导出到EXCEL</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GV" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" OnRowCreated="GV_RowCreated" OnRowDataBound="GV_RowDataBound">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<Columns>
<asp:BoundField />
<asp:BoundField DataField="id" HeaderText="编号" />
<asp:BoundField DataField="name" HeaderText="姓名" />
<asp:BoundField DataField="addr" HeaderText="地址" />
<asp:BoundField DataField="age" HeaderText="年龄" />
<asp:BoundField DataField="sex" HeaderText="性别" />
</Columns>
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导出" /><br />
</div>
</form>
</body>
</html>
后台:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataTable dt = DBHelper.Query("select * from student").Tables[0];
GV.DataSource = dt;
GV.DataBind();
}
}
/// <summary>
/// 导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt = DBHelper.Query("select * from student").Tables[0];
if (dt.Rows.Count > 0)
{
GV.ShowFooter = true;
GV.DataSource = dt;
GV.DataBind();
ExcelExport.ExportToExcel(GV, "student.xls");
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "warning", "<script>alert('没有符合条件的记录,无法导出!')</script>");
}
}
/// <summary>
/// 该方法必须存在，要不不能导出Excel数据
/// </summary>
/// <param name="control"></param>
public override void VerifyRenderingInServerForm(Control control)
{
}
/// <summary>
/// GridView页脚格式化
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Footer)
{
e.Row.Cells[1].Text = "领导签字：";
e.Row.Cells[3].Text = "日期：";
e.Row.Height = Unit.Pixel(30);
}
}
/// <summary>
/// 创建行数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void GV_RowCreated(object sender, GridViewRowEventArgs e)
{
switch (e.Row.RowType)
{
case DataControlRowType.Header:
//表头
TableCellCollection tcHeader = e.Row.Cells;
tcHeader.Add(new TableHeaderCell());
tcHeader[0].Attributes.Add("colspan", "6"); //跨Row
tcHeader[0].Attributes.Add("height", "30");
tcHeader[0].Text = "学生信息</th></tr><tr><th>";
break;
}
}
}
APPCODE:
//数据操作
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// DBHelper 的摘要说明
/// </summary>
public class DBHelper
{
//连接字符串
protected static string connectionString = "";
public DBHelper()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
}
//导出EXCEL
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// ExcelExport 的摘要说明
/// </summary>
public class ExcelExport
{
public ExcelExport()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 将GridView的数据导出为Excel表
/// </summary>
/// <param name="ctl">控件ID</param>
/// <param name="FileName">导出EXCEL名字</param>
public static void ExportToExcel(System.Web.UI.Control ctl, string FileName)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.AppendHeader("content-disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + "\"");
HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=UTF-8\">");
ctl.Page.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
}
