ASP.NET GridView匯出資料到Excel

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.
    }