Download Gridview To Excel With Allow Paging True
While implementing a Web Project for a Logistic company I have found it very difficult to download a GridView to Excel. It is really easy to download that GridView when the AllowPaging property is set to False but my project demands to populate more than 6,00,000 records at a time. So Paging is must for that GridView.
Here I have implemented the following .Net programming for the task.
For ASPX.CS file code is listed below:
protected void Page_Load(object sender, EventArgs e)
{
data_pop();
}
void data_pop()
{
try
{
String cn = Session["searchcode"].ToString();
String str = Session["usercode"].ToString();
String strConn1 = System.Configuration.ConfigurationManager.ConnectionStrings["constringNIMBUS"].ToString();
SqlConnection con = new SqlConnection(strConn1);
con.Open();
SqlCommand cmd = new SqlCommand(“select * from pod_entry where cnno = ‘” + cn.ToString() + “‘ “, con);
SqlDataReader reader = default(SqlDataReader);
reader = cmd.ExecuteReader();
string compname = “”;
while (reader.Read())
{
compname = (string)reader["compname"];
}
if (compname == str)
{
Label1.Visible = false;
SqlConnection con1 = new SqlConnection(strConn1);
con1.Open();
String cmd1 = “select CNNo, CNDate,PODNo, PickUpDate, RefNo, PersonalName, PersonalAdd1, PersonalAdd2, PersonalAdd3, PersonalPostCode, PersonalCity, DELIVERYATEMPTDATETIME, relationship, returnreason FROM POD_ENTRY where cnno = ‘” + cn.ToString() + “‘ ORDER BY CNNO “;
SqlDataAdapter da = new SqlDataAdapter(cmd1, con1);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
con1.Close();
}
else
{
Label1.Visible = true;
}
con.Close();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
protected void BtnExportGrid_Click(object sender, EventArgs args)
{
// pass the grid that for exporting …
if (this.rdoBtnListExportOptions.SelectedIndex == 1)
{
// the user wants all rows exported, turn off paging
// and rebing the grid before sending it to the export
// utility
this.GridView1.AllowPaging = false;
this.GridView1.DataBind();
}
else if (this.rdoBtnListExportOptions.SelectedIndex == 2)
{
// the user wants just the first 100,
// adjust the PageSize and rebind
this.GridView1.PageSize = 100;
this.GridView1.DataBind();
}
GridViewExportUtil.Export(“AnyFile.xls”, this.GridView1);
}
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
protected void BtnExportGrid_Click(object sender, EventArgs args)
{
// pass the grid that for exporting …
if (this.rdoBtnListExportOptions.SelectedIndex == 1)
{
// the user wants all rows exported, turn off paging
// and rebing the grid before sending it to the export
// utility
this.GridView1.AllowPaging = false;
this.GridView1.DataBind();
}
else if (this.rdoBtnListExportOptions.SelectedIndex == 2)
{
// the user wants just the first 100,
// adjust the PageSize and rebind
this.GridView1.PageSize = 100;
this.GridView1.DataBind();
}
GridViewExportUtil.Export(“AnyFile.xls”, this.GridView1);
}
}
This code will give you an option to open/save an Excel file called AnyFile.xls.
Also this code will empower you with the option of downloading the data of all pages or the current page or even the top 100 records.
Abhijit Dey is a Software Professional working in India for last 4 years and his expertise is on .Net technologies and Sql Server. He is also pursuing PhD on Distributed Database.
Article Source
