Introduction:
Sometime it is need to export data to excel from gridview or
datatable. We can export data through code in asp.net (using C# & vb.net).
In this article I will explain how to export data to excel from
datatable or dataset in asp.net using c# and vb.net.
To export data to excel from datatable or gridview we need to
write the code like as given below:
Full Source Code for sample application:
Design Section:
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Export Data to Excel from datatable
or gridview in Asp.net</title>
</head>
<body>
<form id="form1"
runat="server">
<fieldset style="width:300px">
<legend><strong>Export
Data to Excel from Gridview</strong></legend>
<div style="width:300px; padding:0.3em">
<asp:GridView ID="gvDetails"
CellPadding="5"
runat="server"
Width="100%"
BorderStyle="Solid">
<HeaderStyle BackColor="#A00909"
Font-Bold="true"
ForeColor="White"
/>
</asp:GridView><br />
<asp:Button ID="btnExport"
runat="server"
Text="Export from
Gridview"
onclick="btnExport_Click" />
<asp:Button ID="btnExportTable"
runat="server"
Text="Export from
Datatable"
onclick="btnExportTable_Click" />
</div></fieldset>
</form>
</body>
</html>
Asp.Net Code Behind File(using C#)
Write following code in code behind file:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Data;
using System.Web.UI.WebControls;
using System.IO;
public partial
class gridvalue
: System.Web.UI.Page
{
protected void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
gvDetails.DataSource =
BindDatatable();
gvDetails.DataBind();
}
}
protected DataTable BindDatatable()
{
DataTable
dt = new DataTable();
dt.Columns.Add("EmpId",
typeof(Int32));
dt.Columns.Add("EmpName",
typeof(string));
dt.Columns.Add("Designation",
typeof(string));
dt.Rows.Add(1, "Anil
Minhas", "HR");
dt.Rows.Add(2, "Ankush
rana", "Designer");
dt.Rows.Add(3, "Aman
Kamboj", "Designer");
dt.Rows.Add(4, "Vijay
Saklani", "Developer");
dt.Rows.Add(6, "Ajay",
"Developer");
dt.Rows.Add(7, "Mayank",
"Designer");
dt.Rows.Add(8, "Mannu",
"QA");
return
dt;
}
public override void
VerifyRenderingInServerForm(Control control)
{
/* Verifies
that the control is rendered */
}
// Export data to
Excel from Gridview
protected void btnExport_Click(object
sender, EventArgs e)
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment;
filename={0}", "Customers.xls"));
Response.ContentType = "application/ms-excel";
StringWriter
sw = new StringWriter();
HtmlTextWriter
htw = new HtmlTextWriter(sw);
gvDetails.AllowPaging = false;
gvDetails.DataSource = BindDatatable();
gvDetails.DataBind();
//Change the
Header Row back to white color
gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Applying
stlye to gridview header cells
for (int i = 0; i < gvDetails.HeaderRow.Cells.Count;
i++)
{
gvDetails.HeaderRow.Cells[i].Style.Add("background-color", "#df5015");
}
gvDetails.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
//Export data to
excel from datatable
protected void btnExportTable_Click(object
sender, EventArgs e)
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment;
filename={0}", "Customers.xls"));
Response.ContentType = "application/ms-excel";
DataTable
dt = BindDatatable();
string
str = string.Empty;
foreach
(DataColumn dtcol in
dt.Columns)
{
Response.Write(str +
dtcol.ColumnName);
str = "\t";
}
Response.Write("\n");
foreach (DataRow
dr in dt.Rows)
{
str = "";
for
(int j = 0; j < dt.Columns.Count; j++)
{
Response.Write(str + Convert.ToString(dr[j]));
str = "\t";
}
Response.Write("\n");
}
Response.End();
}
}
ASP.Net Code using Vb.Net:
Imports System.Data
Imports System.Web.UI.WebControls
Imports System.IO
Partial Public
Class gridvalue
Inherits
System.Web.UI.Page
Protected Sub Page_Load(ByVal
sender As Object,
ByVal e As EventArgs)
If Not IsPostBack Then
gvDetails.DataSource =
BindDatatable()
gvDetails.DataBind()
End If
End Sub
Protected Function BindDatatable() As
DataTable
Dim dt As DataTable = New DataTable()
dt.Columns.Add("EmpId",
Type.GetType(Int32))
dt.Columns.Add("EmpName",
Type.GetType(Of
String))
dt.Columns.Add("Designation",
Type.GetType(Of
String))
dt.Rows.Add(1, "Anil
Minhas", "HR")
dt.Rows.Add(2, "Ankush
rana", "Designer")
dt.Rows.Add(3, "Aman
Kamboj", "Designer")
dt.Rows.Add(4, "Vijay
Saklani", "Developer")
dt.Rows.Add(6, "Ajay",
"Developer")
dt.Rows.Add(7, "Mayank",
"Designer")
dt.Rows.Add(8, "Mannu",
"QA")
Return
dt
End Function
Public Overrides Sub
VerifyRenderingInServerForm(ByVal control As Control) ' Verifies
that the control is rendered */
End Sub
' Export data to
Excel from Gridview
Protected Sub btnExport_Click(ByVal
sender As Object,
ByVal e As EventArgs)
Response.ClearContent()
Response.Buffer = True
Response.AddHeader("content-disposition", String.Format("attachment;
filename={0}", "Customers.xls"))
Response.ContentType = "application/ms-excel"
Dim sw As StringWriter = New StringWriter()
Dim htw
As HtmlTextWriter
= New HtmlTextWriter(sw)
gvDetails.AllowPaging = False
gvDetails.DataSource = BindDatatable()
gvDetails.DataBind()
'Change the
Header Row back to white color
gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF")
'Applying
stlye to gridview header cells
Dim i As Integer
For i =
0 To gvDetails.HeaderRow.Cells.Count - 1 Step i + 1
gvDetails.HeaderRow.Cells(i).Style.Add("background-color",
"#df5015")
Next
gvDetails.RenderControl(htw)
Response.Write(sw.ToString())
Response.End()
End Sub
'Export data to
excel from datatable
Protected Sub btnExportTable_Click(ByVal
sender As Object,
ByVal e As EventArgs)
Response.ClearContent()
Response.Buffer = True
Response.AddHeader("content-disposition", String.Format("attachment;
filename={0}", "Customers.xls"))
Response.ContentType = "application/ms-excel"
Dim dt As DataTable =
BindDatatable()
Dim str
As String = String.Empty
Dim
dtcol As DataColumn
For Each dtcol In
dt.Columns
Response.Write(str +
dtcol.ColumnName)
str = "\t"
Next
Response.Write("\n")
Dim dr As DataRow
For Each dr In dt.Rows
str = ""
Dim
j As Integer
For
j = 0 To dt.Columns.Count - 1 Step j + 1
Response.Write(str + Convert.ToString(dr(j)))
str = "\t"
Next
Response.Write("\n")
Next
Response.End()
End Sub
End Class
0 comments:
Post a Comment