ASP.NET GridView导出数据到Excel

首先要把GridView控件放到panel中

                <asp:Panel ID="Panel1" runat="server" Width="100%">
                    <asp:GridView ID="gridview1" runat="server" Width="100%" CellPadding="4" ForeColor="#333333"
                        AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True" OnRowDeleting="gridview1_RowDeleting"
                        OnRowEditing="gridview1_RowEditing" DataKeyNames="sno" OnDataBound="gridview1_DataBound"
                        Font-Size="14px">
                        <Columns>
                            <asp:HyperLinkField DataTextField="sno" HeaderText="学号" DataNavigateUrlFields="sno"
                                DataNavigateUrlFormatString="StudentInfo.aspx?id={0}" />
                            <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 id="main">
                                <table>
                                    <tr>
                                        <td style="text-align: left;">
                                            <div id="info">
                                                &nbsp;&nbsp;页次：<asp:Label ID="lblPageCurrent" runat="server" Text="1" CssClass="txtInfo"></asp:Label>
                                                /<asp:Label ID="lblPageCount" runat="server" Text="1"></asp:Label>&nbsp;&nbsp; 共&nbsp;<asp:Label
                                                    ID="lblPageRow" runat="server" Text="1" CssClass="txtInfo"></asp:Label>&nbsp;条记录
                                            </div>
                                        </td>
                                        <td style="text-align: right;">
                                            <div id="page">
                                                <asp:LinkButton ID="btnFirst" runat="server" CssClass="link" CommandName="Pager"
                                                    CommandArgument="First" OnCommand="NavigateToPage">[首页]</asp:LinkButton>&nbsp;
                                                <asp:LinkButton ID="btnPrev" runat="server" CssClass="link" CommandName="Pager" CommandArgument="Prev"
                                                    OnCommand="NavigateToPage">[前一页]</asp:LinkButton>&nbsp;
                                                <asp:LinkButton ID="btnNext" runat="server" CssClass="link" CommandName="Pager" CommandArgument="Next"
                                                    OnCommand="NavigateToPage">[下一页]</asp:LinkButton>&nbsp;
                                                <asp:LinkButton ID="btnLast" runat="server" CssClass="link" CommandName="Pager" CommandArgument="Last"
                                                    OnCommand="NavigateToPage">[尾页]</asp:LinkButton>&nbsp;&nbsp;
                                            </div>
                                        </td>
                                    </tr>
                                </table>
                            </div>
                        </PagerTemplate>
                        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                        <SelectedRowStyle BackColor="#D1DDF1" ForeColor="#333333" Font-Bold="True" />
                        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <AlternatingRowStyle BackColor="White" />
                        <RowStyle BackColor="#EFF3FB" />
                        <EditRowStyle BackColor="#2461BF" />
                    </asp:GridView>
                </asp:Panel>

 

再添加一个按钮，用于触发导出事件：

<asp:LinkButton ID="lbtnexcel" runat="server" Text="导出到Excel" OnClick="lbtnexcel_Click"></asp:LinkButton>

 

以上为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.
    }

搞定！