Export Gridview data to CSV

In this post, I will explain how to export Gridview data to Comma separated values (CSV) file in ASP.NET.


1. First of all please download Northwind Database. Northwind database is a sample database which contains many tables, queries, reports and other database features.

2. OK, Now declare the connection string in the Web.Config,

<connectionStrings>

<add name="NorthwindConnectionString" connectionString="Data Source=localhost;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=123456" providerName="System.Data.SqlClient" />

</connectionStrings>  

3. Now, create a gridview on the aspx page and declare a sqldatasource to bind the gridview (you can bind the gridview from code behind also. Here I just want to keep the process short and want to keep focus on exporting feature only. Hence, I'm declaring a sqldatasource to binding the gridview ).

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True">

<Columns>

<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="True" InsertVisible="False" SortExpression="EmployeeID"></asp:BoundField>

<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName"></asp:BoundField>

<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName"></asp:BoundField>

<asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address"></asp:BoundField>

<asp:BoundField DataField="City" HeaderText="City" SortExpression="City"></asp:BoundField>

<asp:BoundField DataField="PostalCode" HeaderText="PostalCode" SortExpression="PostalCode"></asp:BoundField>

<asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country"></asp:BoundField>

</Columns>

</asp:GridView>




<asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString='<%$ ConnectionStrings:NorthwindConnectionString %>' SelectCommand="SELECT [EmployeeID], [FirstName], [LastName], [Address], [City], [PostalCode], [Country] FROM [Employees]"></asp:SqlDataSource>

4. Let’s add a asp button to aspx page, call it as “Export to CSV” and on the “onClick” event of the button write following code.

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition", "attachment;filename=Report.csv");

Response.Charset = string.Empty;

Response.ContentType = "application/text";

GridView1.AllowPaging = false;

GridView1.DataBind();

StringBuilder stringBuilder = new StringBuilder();

for (int index = 0; index < GridView1.Columns.Count; index++)

{

//add separator

stringBuilder.Append(GridView1.Columns[index].HeaderText + ',');

}

//append new line

stringBuilder.Append("\r\n");

for (int index = 0; index < GridView1.Rows.Count; index++)

{

for (int index2 = 0; index2 < GridView1.Columns.Count; index2++)

{

//add separator

stringBuilder.Append(GridView1.Rows[index].Cells[index2].Text + ',');

}

//append new line

stringBuilder.Append("\r\n");

}

Response.Output.Write(stringBuilder.ToString());

Response.Flush();

Response.End();




5. It is possible that after providing the above code we get an error specifying “Control of type must be placed inside a form tag with runat=server.“. To avoid this kind of exceptions like “Control of type must be placed inside a form tag with runat=server.“, we need to add following method in the code behind:

public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}

6. Now let’s click on “Export to CSV” button to see the output in a notepad.

Click here to View my post for Exporting Gridview to XML.

*** Hope you enjoy the post. For any other query feel free to email me @ akashroy147@gmail.com