GridView匯出Excel的超好例子

 

         其實網上有很多關於Excel的例子，但是不是很好，他們的代碼沒有很全，讀的起來還很晦澀。經過這幾天的摸索，終於可以完成我想要匯出報表Excel的效果了。下面是我的。

一.前台的頁面圖

GridView的第一頁的內容

GridView第二頁的內容：

大家可能遇到這種情況，就是這個時候匯出Excel的時候，開啟Excel的時候發現GridView的第二頁的內容卻沒有匯出到Excel表裡面。其實解決這種情況，很簡單，只要在匯出之前，把Gridview的設定分頁設定為Flase就行了。

下面是我匯出Gridview裡面的全部內容，開啟Excel表如下：

 

這就可以匯出全部的GridView裡面的內容了，包括了GridView的第一頁和第二頁的內容。

 

二、實現的代碼

1.前台的代碼：

 <div style=" margin:20px;">
        <asp:GridView ID="gvRecord" runat="server" AllowPaging="True" CellPadding="3" 

            AutoGenerateColumns="False"  BorderColor="White"
            BorderStyle="Ridge" BorderWidth="2px" BackColor="White" CellSpacing="1"

            GridLines="None" onprerender="gvRecord_PreRender"
            onpageindexchanged="gvRecord_PageIndexChanged"
            onpageindexchanging="gvRecord_PageIndexChanging" >
            <PagerSettings FirstPageText="Home Page" LastPageText="Last Page"
                NextPageText="Next" PreviousPageText="Previous" />
            <RowStyle  ForeColor="Black" BackColor="#E5F1FF" HorizontalAlign="Center" />
            <Columns>
                <asp:TemplateField HeaderText="Extension">
    
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("Extn") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle Width="200px" />
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Wake up time">
            
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("CallTime") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle Width="300px" />
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Status">
 
                    <ItemTemplate>
                        <asp:Label ID="Label3" runat="server" Text='<%# getResult(Eval("Status").ToString()) %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle Width="250px" />
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Call number">
      
                    <ItemTemplate>
                        <asp:Label ID="Label4" runat="server" Text='<%# Bind("callcount") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle Width="150px" />
                </asp:TemplateField>
            </Columns>
            <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
            <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
            <SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#C9E2FF" Font-Bold="True" ForeColor="#000000" />
        </asp:GridView>
       
        <p>
            <asp:Button ID="btnExcel" CssClass="button"  runat="server" Text="Statements
"
                onclick="btnExcel_Click" /></p>
    </div>

 

2.後台代碼如下：

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Model;
using DAL;
using System.Collections.Generic;
using System.IO;

public partial class _3C_CallManager_CallRecord : System.Web.UI.Page
{
    int selectFlag = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bind();
        }
    }

     

    public string getResult(string str)
    {
        if (str == "0")
            return "Haven't called";
        if (str == "1")
            return "check-out";
        if (str == "2")
            return "success";
        if (str == "3")
            return "fail";
        if (str == "4")
            return "dealing";
        if (str == "5")
            return "Artificial wake";
        else
            return "unkown";

    }
    public void bind() {
        selectFlag = 0;
        gvRecord.DataSource = VgCallService.GetInfo();
        gvRecord.DataBind();
    }
    protected void btnExcel_Click(object sender, EventArgs e)
    {
        DateTime dt = System.DateTime.Now;
        string str = dt.ToString("yyyyMMddhhmmss");
        str = str + ".xls";

        gvRecord.AllowPaging = false;
        if (selectFlag == 0)
            bind();
        if (selectFlag == 1)
            selectBind();

        GridViewToExcel(gvRecord, "application/ms-excel", str);

       // Export(gvRecord, "application/ms-excel", str);

  

    }

    /// <summary>
    /// 將網格資料匯出到Excel
    /// </summary>
    /// <param name="ctrl">網格名稱(如GridView1)</param>
    /// <param name="FileType">要匯出的檔案類型(Excel:application/ms-excel)</param>
    /// <param name="FileName">要儲存的檔案名稱</param>
    public static void GridViewToExcel(Control ctrl, string FileType, string FileName)
    {
        HttpContext.Current.Response.Charset = "GB2312";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;//注意編碼
        HttpContext.Current.Response.AppendHeader("Content-Disposition",
            "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString());
        HttpContext.Current.Response.ContentType = FileType;//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword

        ctrl.Page.EnableViewState = false;
        StringWriter tw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(tw);
        ctrl.RenderControl(hw);
        HttpContext.Current.Response.Write(tw.ToString());
        HttpContext.Current.Response.End();
    }
    /// <summary>
    /// ReLoad this VerifyRenderingInServerForm is neccessary
    /// </summary>
    /// <param name="control"></param>

    public override void VerifyRenderingInServerForm(Control control)
    {

    }

    protected void gvRecord_PreRender(object sender, EventArgs e)
    {
        if(selectFlag==0)
            bind();
      }
    protected void gvRecord_PageIndexChanged(object sender, EventArgs e)
    {

    }
    protected void gvRecord_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        this.gvRecord.PageIndex = e.NewPageIndex;

        bind();
    }
}

 

 

注意問題說明：

1)前台頁面<%@ Page Language="C#" EnableEventValidation="false"  AutoEventWireup="true" CodeFile="CallRecord.aspx.cs" Inherits="_3C_CallManager_CallRecord" %>注意添加EnableEventValidation="false"，要不然會報錯的。

2）GridView的綁定資料庫的代碼

     gvRecord.DataSource = VgCallService.GetInfo();//VgCallService.GetInfo()是擷取資料庫的集合，我是封裝好的，根據你們的擷取的集合做不同的調整。
        gvRecord.DataBind();
3）點擊GridView下一頁的關鍵代碼,在PageIndexChanging事件裡面

  this.gvRecord.PageIndex = e.NewPageIndex;  //注意這個不能少。

        bind();

4）在匯出Excel的按鈕事件裡面記得先gvRecord.AllowPaging = false;把GridView的分頁取消之後，再調用GridViewToExcel方法。

5）下面的不可少。

    public override void VerifyRenderingInServerForm(Control control)
    {

    }