Export GridView to Excel with Paging in Asp.Net C# VB.Net

For reporting purposes or for system integration most of the times we need to transfer or export data to Excel. Since for tabular data representation GridView is more popular than other controls in Asp.Net. Hence need to Export data from GridView is crucial. In my previous article I have described “How to Export GridView Data to Excel with custom Number DateTime Format Asp.Net C# VB.Net“. Here in this article I would like to share How one can Export GridView Data to Excel with Paging in Asp.Net C# VB.Net.

Objective/Outcome:
Export GridView to Excel with Paging in Asp.Net C# VB.Net

First create a DataBase Table:

CREATE TABLE [dbo].[tblSupplier](
	[Id] [int] NOT NULL,
	[Code] [char](6) NOT NULL,
	[Name] [varchar](200) NOT NULL,
	[Address] [varchar](200) NOT NULL,
	[ContactNo] [varchar](20) NOT NULL,
	[LastDelivery] [datetime] NULL,
 CONSTRAINT [PK_tblSupplier] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now enter some sample Data to Test:

INSERT INTO tblSupplier VALUES(1,'st001','S.R. STeel','Uttara, Dhaka','01711xxxxxx',GETDATE()-27)
INSERT INTO tblSupplier VALUES(2,'st002','Shadesh Builders','Badda, Dhaka','01611xxxxxx',GETDATE()-10)
INSERT INTO tblSupplier VALUES(3,'st003','Orchard Confec','Rampura, Dhaka','01811xxxxxx',GETDATE()-17)
INSERT INTO tblSupplier VALUES(4,'st004','WindBlow','Uttara, Dhanmondi','01911xxxxxx',GETDATE()-5)
INSERT INTO tblSupplier VALUES(5,'st005','REX','Bashundhara, Dhaka','01511xxxxxx',GETDATE())

Now need to Modify Web.Config file to Connect to Database:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now add a GridView Control & a Button Control Like Below:

    <asp:GridView ID="GridView1" runat="server" AllowPaging="true" PageSize="3" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
    </asp:GridView>
    <br />
    <asp:Button runat="server" ID="cmdExport" Text="Export to Excel With GridView Paging" 
        onclick="cmdExport_Click" />

Now bind Database Table to GridView:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
            {
                SqlDataAdapter ad = new SqlDataAdapter("SELECT * from tblSupplier", conn);
                ad.Fill(dt);
                ViewState["dtSupplier"] = dt;
            }

            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }

Note: Don’t forget to add “System.Data”,”System.Configuration”,”System.Data.SqlClient” namespaces. Another important thing is here i have used Viewstate to retain Data. Why? I will explain later.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim dt As DataTable = New DataTable()
            Using conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
                Dim ad As SqlDataAdapter = New SqlDataAdapter("SELECT * from tblSupplier", conn)
                ad.Fill(dt)
                ViewState("dtSupplier") = dt
            End Using

            GridView1.DataSource = dt
            GridView1.DataBind()
        End If
    End Sub

Note: Don’t forget to add “System.Data”,”System.Configuration”,”System.Data.SqlClient” namespaces. Another important thing is here i have used Viewstate to retain Data. Why? I will explain later.

Now Export GridView Data with Paging into Excel:
C# Code:

    protected void cmdExport_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment; filename=SupplierList.xls");
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter WriteItem = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlText = new HtmlTextWriter(WriteItem);
        GridView1.AllowPaging = false;
        DataTable dtSupplier = (DataTable)ViewState["dtSupplier"];
        GridView1.DataSource = dtSupplier;
        GridView1.DataBind();
        GridView1.RenderControl(htmlText);
        Response.Write(WriteItem.ToString());
        Response.End();
    }

Note: The main tricks is first disable the paging property of GridView & then render to Excel. That’s why rebinding was required & for rebinding stored Viewstate data was also required.

VB.Net Code:

    Protected Sub cmdExport_Click(sender As Object, e As System.EventArgs) Handles cmdExport.Click
        Response.Clear()
        Response.AddHeader("content-disposition", "attachment; filename=SupplierList.xls")
        Response.ContentType = "application/vnd.xls"
        Dim WriteItem As System.IO.StringWriter = New System.IO.StringWriter()
        Dim htmlText As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(WriteItem)
        GridView1.AllowPaging = False
        Dim dtSupplier As DataTable = CType(ViewState("dtSupplier"), DataTable)
        GridView1.DataSource = dtSupplier
        GridView1.DataBind()
        GridView1.RenderControl(htmlText)
        Response.Write(WriteItem.ToString())
        Response.End()
    End Sub

Note: The main tricks is first disable the paging property of GridView & then render to Excel. That’s why rebinding was required & for rebinding stored Viewstate data was also required.

Don’t forget to override the VerifyRenderingInServerForm method. For exporting each page must have override this method:

C# Code:

public override void VerifyRenderingInServerForm(Control control) { }

VB.Net Code:

Public Overrides Sub VerifyRenderingInServerForm(control As Control)
    End Sub

Now run the project hope you will get an Excel sheet containing all GridView Data.

To Export GridView Data to Excel with custom Number DateTime Format CLICK HERE.

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, Asp.net, C#, Gridview, VB.Net
One comment on “Export GridView to Excel with Paging in Asp.Net C# VB.Net
  1. keving xiong says:

    I was getting a reder() problem so I went and make this change : EnableEventValidation=”false”. It’s working now. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*