Monday, 24 September 2012

How to export gridview data to CSV file or Export Gridview data to CSV file using C# or VB.NET | Export Dataset/Datatable to Excel or CSV?

                                Hi Friends, in this article I will explain about How to export GridView data to CSV file or Export Gridview data to CSV file using C# or VB.NET | Export Dataset/Datatable to Excel or CSV?
                               In this article I will explain about how to take the data from database and Display in GridView and save as Excel file.
I will explain using the below example.
                               Take GridView and button and set the GridView id as StudentGridView and button as Button1.

ASP.NET:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>How to export gridview data to CSV file or Export Gridview data to CSV file using C# or VB.NET | Export Dataset/Datatable to Excel or CSV?</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="ExporttoExcel" runat="server" Text="excel" OnClick="ExporttoExcel_Click" />
        <asp:GridView ID="StudentGridView" runat="server">
        </asp:GridView>
    </div>
    </form>
</body>
</html>

VB.NET:
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Text

Partial Public Class Export2Excel
    Inherits System.Web.UI.Page
    Protected Sub ExporttoExcel_Click(sender As Object, e As EventArgs)
        Dim Con As String = ConfigurationManager.ConnectionStrings("con").ToString()
        Dim conn As New SqlConnection(Con)
        Dim da As New SqlDataAdapter("select * from user_details", conn)
        Dim ds As New DataSet()
        da.Fill(ds, "Student")
        StudentGridView.DataSource = ds.Tables("Student").DefaultView
        StudentGridView.DataBind()
        Dim dt As DataTable = ds.Tables("Student")
        Response.ContentType = "Application/x-msexcel"
        Response.AddHeader("content-disposition", "attachment;filename=test.csv")
        Response.Write(CreateCSVFile(dt))
        Response.[End]()
    End Sub
    Protected Function CreateCSVFile(dt As DataTable) As String
        Dim sbldr As New StringBuilder()
        If dt.Columns.Count <> 0 Then
            For Each dtcol As DataColumn In dt.Columns
                sbldr.Append(dtcol.ColumnName & ","c)
            Next
            sbldr.Append(vbCr & vbLf)
            For Each dtrow As DataRow In dt.Rows
                For Each column As DataColumn In dt.Columns
                    sbldr.Append(dtrow(column).ToString() & ","c)
                Next
                sbldr.Append(vbCr & vbLf)
            Next
        End If
        Return sbldr.ToString()
    End Function
End Class


C#.NET:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;

public partial class Export2Excel : System.Web.UI.Page
{
    protected void ExporttoExcel_Click(object sender, EventArgs e)
    {
        string Con = ConfigurationManager.ConnectionStrings["con"].ToString();
        SqlConnection conn = new SqlConnection(Con);
        SqlDataAdapter da = new SqlDataAdapter("select * from user_details", conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "Student");
        StudentGridView.DataSource = ds.Tables["Student"].DefaultView;
        StudentGridView.DataBind();
        DataTable dt = ds.Tables["Student"];
        Response.ContentType = "Application/x-msexcel";
        Response.AddHeader("content-disposition", "attachment;filename=test.csv");
        Response.Write(CreateCSVFile(dt));
        Response.End();
    }
    protected string CreateCSVFile(DataTable dt)
    {
        StringBuilder sbldr = new StringBuilder();
        if (dt.Columns.Count != 0)
        {
            foreach (DataColumn dtcol in dt.Columns)
            {
                sbldr.Append(dtcol.ColumnName + ',');
            }
            sbldr.Append("\r\n");
            foreach (DataRow dtrow in dt.Rows)
            {
                foreach (DataColumn column in dt.Columns)
                {
                    sbldr.Append(dtrow[column].ToString() + ',');
                }
                sbldr.Append("\r\n");
            }
        }
        return sbldr.ToString();
    }
}


The above explains the first open the connection and bind to the GridView and export to the CSV file using the CreateCSVFile from the datatable.page AspdotnetRoja.
"If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook page Aspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter on AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on RSSfeed Aspdotnet-Kishore for free updates directly to your Email inbox . Watch my blog for more articles."

2 comments:

  1. heellllooooooooooo.....,
    here is the CreateCSVFile() function in vs.net

    ReplyDelete
  2. Thanks you.Added CreateCSVFile function.

    ReplyDelete

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.