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:
C#.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
|
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.
heellllooooooooooo.....,
ReplyDeletehere is the CreateCSVFile() function in vs.net
Thanks you.Added CreateCSVFile function.
ReplyDelete