SharePoint & .Net quick samples & tricks: how to export gridview data to CSV file or Export Gridview data to CSV file using asp.net

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

</head>

<body>

<div>

<table>

<tr>

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

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

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 setVerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly

SharePoint & .Net quick samples & tricks

Sunday, December 9, 2012

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

1 comment: