Download Grid Data to excel,Csv and Txt (Tab Delimited File) avoiding Paging ~ DotNet Technology

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

<form id="form1" runat="server">
<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"

BorderStyle="None" BackColor="#C6401B"
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" />

<AlternatingRowStyle BackColor="ControlDark" />

<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"/>

<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........

DotNet Technology

Thursday, 11 July 2013