Export gridview all pages with paging enabled allowpaging to Excel in asp.net

Description:-

A GridView control often contains many rows and it’s usual to see that paging is enabled to the GridView to make data browsing easy. I have no intension to make this article lengthy. Therefore, we will get straight to the point.

I will add a GridView control to a web page and enable paging to it, and set the page size to“ten”. With paging enabled, now I wish to export entire GridView to an Excel file with little formatting too.

Firstly the GridView is again populated with data from database after setting AllowPaging to false. Then a loop is executed on all rows of the GridView and the colors of the Row and the Alternating Row are applied to their individual cells. If this is not done then the color will spread on all cells of the Excel sheet for each row.

Here is the demo example for export gridview all pages with paging enabled allow paging to excel.

Create Table:-

CREATE TABLE [dbo].[Customers](
 [ContactName] [varchar](20) NOT NULL,
 [City] [varchar](20) NOT NULL,
 [PostalCode] [int] NOT NULL,
 [Country] [varchar](20) NOT NULL,
 [Phone] [bigint] NOT NULL,
 [Fax] [bigint] NOT NULL,
 [CustomerId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
 [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Web.Config:-

<connectionStrings>
    <add connectionString="ConnectionString" name="DBCS" providerName="System.Data.SqlClient"/>   
</connectionStrings>

Default.aspx:-

    <div>
        <asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
            RowStyle-BackColor="#A1DCF2" AlternatingRowStyle-BackColor="White" AlternatingRowStyle-ForeColor="#000"
            runat="server" AutoGenerateColumns="false" AllowPaging="true" OnPageIndexChanging="OnPageIndexChanging">
            <Columns>
                <asp:BoundField DataField="ContactName" HeaderText="Contact Name" ItemStyle-Width="150px" />
                <asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="100px" />
                <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100px" />
            </Columns>
        </asp:GridView>
        <br />
        <asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="ExportToExcel" />
    </div>

Default.aspx.cs:-

protected void Page_Load(object sender, EventArgs e)
{
   if (!IsPostBack)
   {
      this.BindGrid();
   }
}

private void BindGrid()
{
  string strConnString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
  using (SqlConnection con = new SqlConnection(strConnString))
  {
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
    {
      using (SqlDataAdapter sda = new SqlDataAdapter())
      {
        cmd.Connection = con;
        sda.SelectCommand = cmd;
        using (DataTable dt = new DataTable())
        {
          sda.Fill(dt);
          GridView1.DataSource = dt;
          GridView1.DataBind();
        }
      }
    }
  }
}

protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
  GridView1.PageIndex = e.NewPageIndex;
  this.BindGrid();
}

        protected void ExportToExcel(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            using (StringWriter sw = new StringWriter())
            {
                HtmlTextWriter hw = new HtmlTextWriter(sw);

                //To Export all pages
                GridView1.AllowPaging = false;
                this.BindGrid();

                GridView1.HeaderRow.BackColor = Color.White;
                foreach (TableCell cell in GridView1.HeaderRow.Cells)
                {
                    cell.BackColor = GridView1.HeaderStyle.BackColor;
                }
                foreach (GridViewRow row in GridView1.Rows)
                {
                    row.BackColor = Color.White;
                    foreach (TableCell cell in row.Cells)
                    {
                        if (row.RowIndex % 2 == 0)
                        {
                            cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                        }
                        else
                        {
                            cell.BackColor = GridView1.RowStyle.BackColor;
                        }
                        cell.CssClass = "textmode";
                    }
                }

                GridView1.RenderControl(hw);

                //style to format numbers to string
                string style = @"<style> .textmode { } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }
        }

public override void VerifyRenderingInServerForm(Control control)
{
  /* Verifies that the control is rendered */
}

Author : Umesh Patel

Related Posts