Introduction:
Here I will explain how to export data to excel from datatable or dataset in asp.net using c# and vb.net or export data to excel from gridview in asp.net using c# and vb.net.
Description:
In previous posts I explained export gridview data to csv file in asp.net, export gridview data to pdf file in asp.net, export data from excel to gridview in asp.net, import excel data to sql server in asp.net, asp.net Google currency converter and many articles relating to gridview, asp.net, c#. Now I will explain how to export data to excel from datatable or dataset in asp.net using c# and vb.net.
In previous posts I explained export gridview data to csv file in asp.net, export gridview data to pdf file in asp.net, export data from excel to gridview in asp.net, import excel data to sql server in asp.net, asp.net Google currency converter and many articles relating to gridview, asp.net, c#. Now 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 shown below in aspx page
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1"
runat="server">
<title>Export Data to Excel from datatable or gridview in Asp.net</title>
</head>
<body>
<form id="form1"
runat="server">
<div>
<asp:GridView ID="gvDetails"
AutoGenerateColumns="false"
CellPadding="5"
runat="server">
<Columns>
<asp:BoundField HeaderText="UserId" DataField="UserId" />
<asp:BoundField HeaderText="UserName" DataField="UserName" />
<asp:BoundField HeaderText="Education" DataField="Education" />
<asp:BoundField HeaderText="Location" DataField="Location" />
</Columns>
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
<asp:Button ID="btnExport"
runat="server"
Text="Export to
Excel from Gridview"
onclick="btnExport_Click" />
<asp:Button ID="btnExportTable"
runat="server"
Text="Export to
Excel from Datatable"
onclick="btnExportTable_Click" />
</form>
</body>
</html>
|
Now
in code behind we need to write the code as like shown below
C#.NET Code
using System;
using System.Data;
using System.IO;
using System.Web.UI;
public partial class ExportExceldatatoDatatable
: 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("UserId", typeof(Int32));
dt.Columns.Add("UserName",
typeof(string));
dt.Columns.Add("Education",
typeof(string));
dt.Columns.Add("Location",
typeof(string));
dt.Rows.Add(1, "SureshDasari",
"B.Tech", "Chennai");
dt.Rows.Add(2, "MadhavSai",
"MBA", "Nagpur");
dt.Rows.Add(3, "MaheshDasari",
"B.Tech", "Nuzividu");
dt.Rows.Add(4, "Rohini", "MSC", "Chennai");
dt.Rows.Add(5, "Mahendra",
"CA", "Guntur");
dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur");
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();
}
}
|
If
you observe above code I added one function that is VerifyRenderingInServerForm this function is used to avoid the
error like “control must be placed in
inside of form tag”. If we set VerifyRenderingInServerForm
function then compiler will think that controls rendered before exporting and
our functionality will work perfectly.
VB.NET Code
Imports System.Data
Imports System.IO
Imports System.Web.UI
Partial Class
_Default
Inherits System.Web.UI.Page
Protected Sub
Page_Load(ByVal sender As Object, ByVal e As
EventArgs) Handles Me.Load
If Not
IsPostBack Then
gvDetails.DataSource = BindDatatable()
gvDetails.DataBind()
End If
End Sub
Protected Function
BindDatatable() As DataTable
Dim dt As
New DataTable()
dt.Columns.Add("UserId", GetType(Int32))
dt.Columns.Add("UserName",
GetType(String))
dt.Columns.Add("Education",
GetType(String))
dt.Columns.Add("Location",
GetType(String))
dt.Rows.Add(1, "SureshDasari",
"B.Tech", "Chennai")
dt.Rows.Add(2, "MadhavSai",
"MBA", "Nagpur")
dt.Rows.Add(3, "MaheshDasari",
"B.Tech", "Nuzividu")
dt.Rows.Add(4, "Rohini", "MSC", "Chennai")
dt.Rows.Add(5, "Mahendra",
"CA", "Guntur")
dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur")
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
New StringWriter()
Dim htw As
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 i As
Integer = 0 To
gvDetails.HeaderRow.Cells.Count - 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
For Each
dtcol As DataColumn In
dt.Columns
Response.Write(str + dtcol.ColumnName)
str = vbTab
Next
Response.Write(vbLf)
For Each
dr As DataRow In
dt.Rows
str = ""
For j As
Integer = 0 To
dt.Columns.Count - 1
Response.Write(str &
Convert.ToString(dr(j)))
str = vbTab
Next
Response.Write(vbLf)
Next
Response.[End]()
End Sub
End Class
|
Demo
 |
 |
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
8 comments :
How to export to Excel 2013 using windows form application? I am using Microsoft.Office.Interop.Excel
Well done....
It helps
sir can you help me out for the cell to cell mapping of sql and excel sheet.? i have already a format of excel file and i would like to have fill data through mapping
Thank for your valuable source code its working
Can Any help in importing data from excel to sql server 2008 R2
There is not error, but i am not able to export the Xls file, Browser is not showing up to save or open the file.
No issue with browser because for other website it allows me to export.
Sir can you tell me the code for how to create multi sheet excel dynamically
On Response.End() Giving Excepetion Thread was being aborted
How to give color/formatting for the header row when exporting from data table?
Note: Only a member of this blog may post a comment.