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.

2 comments:

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

    ReplyDelete
  2. Thanks you.Added CreateCSVFile function.

    ReplyDelete

© 2012-2018 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.