Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

how to export gridview data to CSV file or Export Gridview data to CSV file using asp.net

Apr 3, 2011
Introduction:

Here I will explain how to export gridview data to CSV or text document using asp.net.


Description:

I have one gridview that has filled with user details now I need to export gridview data to CSV file or text file. First we need to learn what is CSV file? CSV file is a text based file in which data are separated by comma and it can be opened by Excel. Each row of data in CSV file separated with commas.

To implement this functionality first we need to design aspx page like this 


<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td align="right">
<asp:ImageButton ID="btnCSV" runat="server" ImageUrl="~/CSVImage.jpg"
onclick="btnCSV_Click" />
</td>
</tr>
<tr>
<td>
<asp:GridView runat="server" ID="gvdetails" DataSourceID="dsdetails"  AllowPaging="true" AllowSorting="true" AutoGenerateColumns="false">
<RowStyle BackColor="#EFF3FB" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="UserId" HeaderText="UserId" />
<asp:BoundField DataField="UserName" HeaderText="UserName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" />
<asp:BoundField DataField="Location" HeaderText="Location" />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
<asp:SqlDataSource ID="dsdetails" runat="server" ConnectionString="<%$ConnectionStrings:dbconnection %>"
SelectCommand="select * from UserInformation"/>
</div>
</form>
</body>
</html>
Here don’t forgot to set the connection string in web.config file here I am getting database connection from web.config file for that reason you need to set the connectionstring in web.config file like this

<connectionStrings>
<add name="dbconnection" connectionString="Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"/>
</connectionStrings>
After set the connection string in web.config file now we are able to get the data from database and we can bind that data to gridview by using sqldatasource. Now if you run application gridview appears like this


Now in code behind add this reference

using System.IO;
After that write the following code in code behind


public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
/// <summary>
/// This event is used to export gridview data to CSV document
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnCSV_Click(object sender, ImageClickEventArgs e)
{
Response.ClearContent();
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.csv"));
Response.ContentType = "application/text";
gvdetails.AllowPaging = false;
gvdetails.DataBind();
StringBuilder strbldr = new StringBuilder();
for (int i = 0; i < gvdetails.Columns.Count; i++)
{
//separting header columns text with comma operator
strbldr.Append(gvdetails.Columns[i].HeaderText + ',');
}
//appending new line for gridview header row
strbldr.Append("\n");
for (int j = 0; j < gvdetails.Rows.Count; j++)
{
for (int k = 0; k < gvdetails.Columns.Count; k++)
{
//separating gridview columns with comma
strbldr.Append(gvdetails.Rows[j].Cells[k].Text + ',');
}
//appending new line for gridview rows
strbldr.Append("\n");
}
Response.Write(strbldr.ToString());
Response.End();
}

Demo for CSV document


If you observe above code I added one function that is VerifyRenderingInServerForm this function is used to avoid the error like “control must be placed in inside of form tag”. If we set VerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly

Download sample code attached





Now if you’re getting any error message like
Control 'gvdetails' of type 'GridView' must be placed inside a form tag with runat=server


Check this post to solve this problem



Otherwise if you’re getting any error message like 

RegisterForEventValidation can only be called during Render();

Check this post to solve your problem



If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

15 comments :

Anonymous said...

Hi

This code is error free and is very useful

Thanks

Anonymous said...

Hi i used gridview to PDF file this result PDF file is create but it's not come record..
error is created- Token type was not recognized..
i can't solve this error.. Give me the solution

Suresh Dasari said...

hi to export gridview data to PDF check this link here i explained clearly it will work for you
http://www.aspdotnet-suresh.com/2011/04/how-to-export-gridview-data-to-pdf.html

parvez said...

Hi,
i m using above code it work fine when all column was bound column in grid view,but i m getting stuck when the grid view have a label control for this in CSV found black value.

Anonymous said...

I am trying to export to csv and I am only getting headers and no data rows. I have data in my data grid. Any help would be appreciated.

Anonymous said...

how to import .csv file

Anonymous said...

If I have to enter some notes at the end of csv file then what to do???
Means 4 lines of notes.
but it does not affect above columns.

Anonymous said...

Dear Sir, I have grid view with template fields. While exporting to CSV only header row is exporting but other rows are showing blank values. Please help


... Ramesh Raghuvanshi

vivek said...

whenever i click on csv image button, a message comes that your current setting do not allowed file to be downloaded

Anonymous said...

Thank you!!! Worked great

Unknown said...

i want to change the format of pdf file when exporting data from gridview plssss tell me the easy way

Anonymous said...

This works great for simple columns. I have some rows with html tags like (br,ul). I want these tags to be replaced with their real meaning and these data then need to go into a csv excel file. CSV file cannot be formatted is true. But data can be changed before moving to the csv. I mean to say instead of sending (br). I want to put a space. Instead of sending (ul,li). I want to put a number followed with the content.
I could not able to put HTML tags in comments. So used just like that.

Shrikant said...

I want to download csv file like gridview colomn contain comma and new line in content.

Anonymous said...

I had the same problem with the program only saving the header text, but not the data rows. I fixed it by taking out the lines starting with 'Response' and the two 'gvdetails' lines, and just using StreamWriter to write to the text file after StringBuilder has done its work, i.e.:

using (StreamWriter writer = new StreamWriter(Server.MapPath("~/Regions.csv"), false))
{
writer.WriteLine(strbldr.ToString());
}


The above few lines of code works just fine.

Davezilla

Anonymous said...

Sorry... I forgot to mention that I changed the name of the data file, because I used my own SQL table and data as opposed to that given above in the example program. :)

Davezilla

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.