Here I will explain how to export grid view data to excel, CSV, Text ( Tab Delimited File ) documnets using asp.net without paging problem
so in one gridview that has filled with Some details, now we need to export gridview data to user selected document based on selection. To implement this functionality first we need to design aspx page like this
ASPX page
<div>
<table width="100%">
<tr align="right">
<td align="center" width="800">
<asp:ImageButton ID="imgbtnxlsx" runat="server" ImageUrl="~/Images/xls_file.png"
Height="40" Width="40" ToolTip="xls" OnClick="imgbtnxlsx_Click" />
<asp:ImageButton ID="imgbtntxt" runat="server" ImageUrl="~/Images/txt_file.png" Height="40"
Width="40" ToolTip="txt" OnClick="imgbtntxt_Click" />
<asp:ImageButton ID="imgbtncsv" runat="server" ImageUrl="~/Images/csv_file.png" Height="40"
Width="40" ToolTip="csv" OnClick="imgbtncsv_Click" />
</td>
</tr>
<tr>
<td align="center" width="800">
<asp:GridView ID="Grid1" runat="server" AllowPaging="true"
PageSize="10" OnPageIndexChanging="Grid1_PageIndexChanging"
GridLines="None" AutoGenerateColumns="false">
<HeaderStyle HorizontalAlign="Center" Height="35"
<HeaderStyle HorizontalAlign="Center" Height="35"
BorderStyle="None" BackColor="#C6401B"
Font-Names="arial" Font-Size="16px" Font-Bold="true" />
<RowStyle HorizontalAlign="Center" Font-Names="helvetica, arial"
Font-Names="arial" Font-Size="16px" Font-Bold="true" />
<RowStyle HorizontalAlign="Center" Font-Names="helvetica, arial"
Font-Size="14px" BackColor="#F2F9FF" Wrap="true"
Height="35" BorderStyle="None" />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
<AlternatingRowStyle BackColor="ControlDark" />
<PagerStyle HorizontalAlign="Center" ForeColor="#8D8D8D"
<PagerStyle HorizontalAlign="Center" ForeColor="#8D8D8D"
Font-Names="helvetica, arial" />
<Columns>
<asp:BoundField DataField="StateID" HeaderText="StateID" ItemStyle-Width="200" />
<asp:BoundField DataField="CountryID" HeaderText="CountryID"/>
<Columns>
<asp:BoundField DataField="StateID" HeaderText="StateID" ItemStyle-Width="200" />
<asp:BoundField DataField="CountryID" HeaderText="CountryID"/>
<asp:BoundField DataField="StateName" HeaderText="StateName" />
<asp:BoundField DataField="Abbreviation" HeaderText="Abbreviation" />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
CODE :-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data.SqlClient;
using System.Data;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridd();
}
}
private void BindGridd()
{
SqlConnection con = new SqlConnection(@"Data Source=Servername;Initial Catalog=DBname;
User ID=username;Password=password");
SqlCommand cmd = new SqlCommand("select * from tablename", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
con.Open();
da.Fill(ds);
Session["GridData"] = ds;
Grid1.DataSource = ds;
Grid1.DataBind();
con.Close();
}
protected void Grid1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
Grid1.PageIndex = e.NewPageIndex;
Grid1.DataSource = Session["GridData"]; ;
Grid1.DataBind();
}
protected void imgbtnxlsx_Click(object sender, ImageClickEventArgs e)
{
Response.ClearContent();
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "xcell.xls"));
Response.ContentType = "application/text";
Grid1.AllowPaging = false;
Grid1.DataSource = Session["GridData"];
Grid1.DataBind();
StringBuilder strbldr = new StringBuilder();
for (int i = 0; i < Grid1.Columns.Count; i++)
{
//separting header columns text with comma operator
strbldr.Append(Grid1.Columns[i].HeaderText + '\t');
}
//appending new line for gridview header row
strbldr.Append("\r\n");
for (int j = 0; j < Grid1.Rows.Count; j++)
{
for (int k = 0; k < Grid1.Columns.Count; k++)
{
//separating gridview columns with comma
strbldr.Append(Grid1.Rows[j].Cells[k].Text + '\t');
}
//appending new line for gridview rows
strbldr.Append("\r\n");
}
Response.Write(strbldr.ToString());
Response.End();
}
protected void imgbtntxt_Click(object sender, ImageClickEventArgs e)
{
Response.ClearContent();
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "txtfile.txt"));
Response.ContentType = "application/text";
Grid1.AllowPaging = false;
Grid1.DataSource = Session["GridData"];
Grid1.DataBind();
StringBuilder strbldr = new StringBuilder();
for (int i = 0; i < Grid1.Columns.Count; i++)
{
//separting header columns text with comma operator
strbldr.Append(Grid1.Columns[i].HeaderText + ' ');
}
//appending new line for gridview header row
strbldr.Append("\r\n");
for (int j = 0; j < Grid1.Rows.Count; j++)
{
for (int k = 0; k < Grid1.Columns.Count; k++)
{
//separating gridview columns with comma
strbldr.Append(Grid1.Rows[j].Cells[k].Text + ' ');
}
//appending new line for gridview rows
strbldr.Append("\r\n");
}
Response.Write(strbldr.ToString());
Response.End();
}
protected void imgbtncsv_Click(object sender, ImageClickEventArgs e)
{
Response.ClearContent();
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}","csvfil.csv"));
Response.ContentType = "application/text";
Grid1.AllowPaging = false;
Grid1.DataSource = Session["GridData"];
Grid1.DataBind();
StringBuilder strbldr = new StringBuilder();
for (int i = 0; i < Grid1.Columns.Count; i++)
{
//separting header columns text with comma operator
strbldr.Append(Grid1.Columns[i].HeaderText + ',');
}
//appending new line for gridview header row
strbldr.Append("\n");
for (int j = 0; j < Grid1.Rows.Count; j++)
{
for (int k = 0; k < Grid1.Columns.Count; k++)
{
//separating gridview columns with comma
strbldr.Append(Grid1.Rows[j].Cells[k].Text + ',');
}
//appending new line for gridview rows
strbldr.Append("\n");
}
Response.Write(strbldr.ToString());
Response.End();
}
Note: This function is used to avoid the error like “control must be placed in inside of form tag”. If we setVerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly.
public override void VerifyRenderingInServerForm(Control control)
{
/* method must be implemented when using RenderControl */
}
And final Output Will be........
OutPut:-
CSV
EXCEL
NOTEPAD
