ASP.NET GridView导出数据到Excel
首先要把GridView控件放到panel中
<asp:Panel runat="server" Width="100%">
<asp:GridView runat="server" Width="100%" CellPadding="4" ForeColor="#333333"
AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True" On
On
Font-Size="14px">
<Columns>
<asp:HyperLinkField DataTextField="sno" HeaderText="学号" DataNavigateUrlFields="sno"
DataNavigateUrlFormatString="StudentInfo.aspx? />
<asp:BoundField DataField="sname" HeaderText="姓名" />
<asp:BoundField DataField="ssex" HeaderText="性别" />
<asp:BoundField DataField="snational" HeaderText="民族" />
<%--<asp:BoundField DataField="sdegree" HeaderText="学历" />--%>
<asp:BoundField DataField="spoliticalFace" HeaderText="整治面貌" />
<asp:BoundField DataField="sphone" HeaderText="电话" />
<asp:BoundField DataField="semail" HeaderText="E-mail" />
<asp:BoundField DataField="saddress" HeaderText="住址" />
<asp:BoundField DataField="sidcard" HeaderText="身份证号" />
<asp:CommandField HeaderText="编辑" ShowEditButton="True" ButtonType="Image" EditImageUrl="~/Admin/Images/edit.gif" />
<asp:CommandField HeaderText="删除" ShowDeleteButton="True" ButtonType="Image" DeleteImageUrl="~/Admin/Images/del.gif" />
</Columns>
<PagerTemplate>//以下为自定义分页
<div />
</asp:GridView>
</asp:Panel>
再添加一个按钮，用于触发导出事件：
<asp:LinkButton runat="server" Text="导出到Excel" On
以上为aspx页面代码，后台代码则是：
protected void lbtnexcel_Click(object sender, EventArgs e)
{
gridview1.BottomPagerRow.Visible = false;//导出到Excel表后，隐藏分页部分
gridview1.Columns[9].Visible = false;//隐藏“编辑”列
gridview1.Columns[10].Visible = false;//隐藏“删除”列
gridview1.AllowPaging = false;//取消分页，便于导出所有数据，不然只能导出当前页面的几条数据
gridview1.DataSource = ds;//取消分页后重新绑定数据集,ds为数据集dataset
gridview1.DataBind();
DateTime dt = DateTime.Now;//给导出后的Excel表命名，结合表的用途以及系统时间来命名
string filename = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Second.ToString();
/*如导出的表中有某些列为编号、身份证号之类的纯数字字符串，如不进行处理，则导出的数据会默认为数字，例如原字符串"0010"则会变为数字10，字符串"1245787888"则会变为科学计数法1.236+E9，这样便达不到我们想要的结果，所以需要在导出前对相应列添加格式化的数据类型，以下为格式化为字符串型*/
foreach (GridViewRow dg in this.gridview1.Rows)
{
dg.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");
dg.Cells[5].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");
dg.Cells[6].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");
dg.Cells[8].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");
}
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("学生表" + filename, System.Text.Encoding.UTF8) + ".xls");//导出文件命名
Response.ContentEncoding = System.Text.Encoding.UTF7;//如果设置为"GB2312"则中文字符可能会乱码
Response.ContentType = "applicationshlnd.xls";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
Panel1.RenderControl(oHtmlTextWriter);//Add the Panel into the output Stream.
Response.Write(oStringWriter.ToString());//Output the stream.
Response.Flush();
Response.End();
}
//重载VerifyRenderingInServerForm方法，否则运行的时候会出现如下错误提示：“类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内”
public override void VerifyRenderingInServerForm(Control control)
{
//override VerifyRenderingInServerForm.
}
搞定！