Net-informations.com
SiteMap  | About    

Gridview export to CSV

From the previous lesson, we saw how to export GridView data to an Excel file.

Export to Excel

In this lesson we are going to export GridView data to a .CSV file.

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();
}

export gridview to csv

After export to .CSV file, the content in the file look like the following:

gridview to csv

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






net-informations.com (C) 2016    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.