Gridview export to CSV
From the previous lesson, we saw how to export GridView data to an Excel file.
Export to Excel Gridview export to CSV
In this lesson, we will focus on exporting data from a GridView to a .CSV (Comma-Separated Values) file. The .CSV file format is widely used for data interchange due to its simplicity and compatibility with various applications.
To achieve this, we will explore the necessary steps and coding techniques involved in exporting the GridView data to a .CSV file format. This process will enable users to conveniently save the data displayed in the GridView as a separate .CSV file, which can be easily opened and manipulated using spreadsheet software or other compatible applications.
Throughout this lesson, we will provide detailed instructions, code snippets, and best practices to guide you through the export process effectively. By the end of this lesson, you will have a clear understanding of how to extract GridView data and transform it into a .CSV file format, thereby facilitating seamless data exchange and analysis.
Database
In this article we are using Microsoft's Pubs database for retrieving data. You can download Pubs database from the following link:
Download
Drag and Drop one GridView control and a Button control to the defailt.aspx file. In the code behind, write the code for connecting database in the page_load event and in the Button click event write the code for export to .CSV file.
protected void btntoCsv_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=gvtocsv.csv");
Response.Charset = "";
Response.ContentType = "application/text";
StringBuilder sBuilder = new System.Text.StringBuilder();
for (int index = 0; index < GridView1.Columns.Count; index++)
{
sBuilder.Append(GridView1.Columns[index].HeaderText + ',');
}
sBuilder.Append("\r\n");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
for (int k = 0; k < GridView1.HeaderRow.Cells.Count; k++)
{
sBuilder.Append(GridView1.Rows[i].Cells[k].Text.Replace(",", "") + ",");
}
sBuilder.Append("\r\n");
}
Response.Output.Write(sBuilder.ToString());
Response.Flush();
Response.End();
}
After export to .CSV file, the content in the file look like the following:
Default.aspx
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<br />
<asp:Button ID="btntoCsv" runat="server" Text="GridView to CSV" onclick="btntoCsv_Click" />
</form>
</body>
</html>
C# Source Code
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Text;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet ds = new DataSet();
int i = 0;
string sql = null;
string connetionString = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****";
sql = "select * from publishers";
SqlConnection connection = new SqlConnection(connetionString);
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
adapter.SelectCommand = command;
adapter.Fill(ds);
connection.Close();
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
public override void VerifyRenderingInServerForm(Control control)
{
/*Tell the compiler that the control is rendered
* explicitly by overriding the VerifyRenderingInServerForm event.*/
}
protected void btntoCsv_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=gvtocsv.csv");
Response.Charset = "";
Response.ContentType = "application/text";
StringBuilder sBuilder = new System.Text.StringBuilder();
for (int index = 0; index < GridView1.Columns.Count; index++)
{
sBuilder.Append(GridView1.Columns[index].HeaderText + ',');
}
sBuilder.Append("\r\n");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
for (int k = 0; k < GridView1.HeaderRow.Cells.Count; k++)
{
sBuilder.Append(GridView1.Rows[i].Cells[k].Text.Replace(",", "") + ",");
}
sBuilder.Append("\r\n");
}
Response.Output.Write(sBuilder.ToString());
Response.Flush();
Response.End();
}
}
VB.Net Source Code
Imports System.Drawing
Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim adapter As New SqlDataAdapter()
Dim ds As New DataSet()
Dim i As Integer = 0
Dim sql As String = Nothing
Dim connetionString As String = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****"
sql = "select * from stores"
Dim connection As New SqlConnection(connetionString)
connection.Open()
Dim command As New SqlCommand(sql, connection)
adapter.SelectCommand = command
adapter.Fill(ds)
adapter.Dispose()
command.Dispose()
connection.Close()
GridView1.DataSource = ds.Tables(0)
GridView1.DataBind()
End Sub
Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
'Tell the compiler that the control is rendered
'explicitly by overriding the VerifyRenderingInServerForm event.
End Sub
Protected Sub btntocsv_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btntoExcel.Click
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=gvtocsv.csv")
Response.Charset = ""
Response.ContentType = "application/text"
Dim sBuilder As StringBuilder = New System.Text.StringBuilder()
For index As Integer = 0 To GridView1.Columns.Count - 1
sBuilder.Append(GridView1.Columns(index).HeaderText + ","c)
Next
sBuilder.Append(vbCr & vbLf)
For i As Integer = 0 To GridView1.Rows.Count - 1
For k As Integer = 0 To GridView1.HeaderRow.Cells.Count - 1
sBuilder.Append(GridView1.Rows(i).Cells(k).Text.Replace(",", "") + ",")
Next
sBuilder.Append(vbCr & vbLf)
Next
Response.Output.Write(sBuilder.ToString())
Response.Flush()
Response.[End]()
End Sub
End Class