Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

How to Export Data from Gridview to Excel in Asp.net using C#, VB.NET

Sep 27, 2013
Introduction:

Here I will explain how to export data from
gridview to excel in asp.net using c#, vb.net or bind data to gridview and export asp.net gridview data to excel in asp.net using c#, vb.net.
Description:


To export gridview data to excel we need to write the code like as shown below


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Export Gridview Data to Excel 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"
onclick="btnExport_Click" />
</form>
</body>
</html>
Now in code behind add following namespaces

C# Code


using System;
using System.Data;
using System.IO;
using System.Web.UI;
After that add following code in code behind like as shown below


protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridview();
}
}
protected void BindGridview()
{
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");
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
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;
BindGridview();
//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();
}
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 ExportGridviewDatainVB
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

If Not IsPostBack Then
BindGridview()
End If
End Sub
Protected Sub BindGridview()
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")
gvDetails.DataSource = dt
gvDetails.DataBind()
End Sub
Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
' Verifies that the control is rendered

End Sub
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
BindGridview()
'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
End Class
Demo

Whenever we run application we will see the screen like as shown below



Once we click on Export to Excel button we will see data in excel file like as shown below


Download sample code attached


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 RSS subscribe by email Subscribe by Email

27 comments :

anuj said...

great!

Prashant Yerekar said...

this is very helpful assignment for database.

Anonymous said...

very nice

Avil Anil said...

nice one can yuo please help me with a code to do user based authorisation means how to disable and enable textboxes depends on roles users are logged in

kohila vani said...

hi,sir this kohila,
the code is not work when we use other table controls in form tag......
please help me.

Sudha Karan said...

This is very helpfull to me,, OK Thanks ..

Raghul Daggi said...

Its working for my project thanks

programmer2012 said...

Hi Suresh,
Thanks alot for providing very usefull articles. This code is working fine for my project, but If there is any value of gridview record in other language (arabic), then in excel file just it is showing some symbols..What to be done to get exactly ararbic values from grideview to excel file??

Thanks

programmer2012 said...

Hi,
I have added the following code, but it is not working...
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;

PRIYADHARSHINI KRISHNAMOORTHY said...

Hi,
When Export an image from gridview to excel, In excel its shows the original image size.i want to fix the image height & width to 100px.
In gridview the image are showing in a fixed size.But ,eporting to excel the size of the image is changing to its original size.how to fix the image size in excel

sanjay takke said...

how do we can add print functionality using asp or c#,net

Carlos Tomaz said...

Hi,

I get the following error:

RegisterForEventValidation can only be called during Render();

Can you advise what I'm doing wrong???

Thanks in advance.

SATISH RAJNALE said...

NICE ARTICLE VERY HELPFUL.......

Sagar Gore said...

Very nice article bro Really It's Work

Unknown said...

Its Ok... the code works.
But How can we get the data in excel with standard excel format and not that of the grid we are exporting

Anonymous said...

Hi, This is Upendra Pratap Shahi....
this work but when attached this file to the mail and try to preview this then got an error for content error type..

Anonymous said...

How to set width for each column?

Anonymous said...

How to set Title of Grid programatically? asp.net

Ullas Krishnan said...

How to generate the excel and save it in a folder in server?

gisijala.dinesh said...

please give me code for above program by using LINQ

Tanmay Nehete said...

Grate
bt sir i want to set the excel cell property as text format how can i do this..???

Tanmay Nehete said...

hello sir this tanmay i want to export data to excel without using any loop can it be possible
please help me

Anonymous said...

I tried your code, it works but give me this error when try to open the Excel file, "the file you are trying to open is in a different format than specified by the file extension", can you please help?

Shaikh Akhtar said...

It is nice Program But i want to ask one thing when i am doing the same code m getting some Null refrences error u wrote in design of gridview
AutoGenerateColumns="false"
But when i delete it works fine but when i put AutoGenerateColumns="false" then will get an error "Object reference not set to an instance of an object."
Can u tell me why this error comes

Anonymous said...

I try this code but i got following error...plz help me...
"RegisterForEventValidation can only be called during Render();"
at this line
GVPurity.RenderControl(htw);

Anonymous said...

Hello,

How can i rename sheet and also add new Sheet?

Anonymous said...

it shows HttpException near "GridView1.RenderControl(htw);" that Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server. but i have placed inside a form tag with runat=server. what to do?

Give your Valuable Comments

Other Related Posts

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