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

43 comments :

anuj said...

great!

Unknown said...

this is very helpful assignment for database.

Anonymous said...

very nice

Unknown 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

Unknown said...

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

Unknown said...

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

Mynotes oracle DBA 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;

Unknown 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

Unknown said...

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

Anonymous 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.

Unknown said...

NICE ARTICLE VERY HELPFUL.......

Unknown 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

Unknown said...

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

Unknown 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?

Unknown 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?

Anonymous said...

How to create the VerifyRenderingInServerForm function. Please can you explain.

Unknown said...

Please tell me how should i import data from gridview to excel in desktop application

Unknown said...

pls explain this code

Unknown said...

if (BtnAddnew.Text == "Add New")
{
grdDetails.Visible = true;
try
{
//if (grdDetails.Rows.Count == 0)
//{
// Session["dtMyTable"] = createtable();
// Session["tmpDataTable"] = ((DataTable)Session["dtMyTable"]);
//}

//commented by sekhar j.v. as on 1.11.2012 for incorporating other department subsidy
//fillSchememappingGrid((DataTable)Session["tmpDataTable"],ddldept.SelectedValue.ToString().Trim(),ddltypeofscheme.SelectedValue.ToString().Trim(),txtschnameeng.Text.ToString(),txtschnametel.Text.ToString(),txtunitcost.Text.ToString(),txtsubsidy.Text.ToString(),txtbnkloan.Text.ToString(),txtbeneficiarycontribution.Text.ToString(),Session["uid"].ToString(), "0");
if (ddlLocation.SelectedItem.Text == "Other")
{
location = txtLocation.Text.ToString();
}
else
{
location = ddlLocation.SelectedItem.Text.ToString();
}
fillTaskmappingGrid((DataTable)Session["tmpDataTable"], txtdate.Text.ToString(), location, ddlClient.SelectedValue.ToString(), ddlProject.SelectedValue.ToString(), ddltasktype.SelectedItem.Text.ToString(), ddltaskactivity.SelectedItem.Text.ToString(), ddltaskmode.SelectedItem.Text.ToString(), txtdesc.Text.ToString(), ddlhrs.SelectedItem.Text.ToString(), ddlmin.SelectedItem.Text.ToString(), ddlacthrs.SelectedItem.Text.ToString(), ddlactmins.SelectedItem.Text.ToString(), txtAssignedby.Text.ToString(), ddlstatus.SelectedItem.Text.ToString(), txtRemarks.Text.ToString(), Session["user"].ToString(), "0");

//end of incorporation

this.grdDetails.DataSource = ((DataTable)Session["tmpDataTable"]).DefaultView;
this.grdDetails.DataBind();

Clear();
lblresult.Text = "";
}
catch (Exception ex)
{
// lblresult.Text = ex.ToString();
}
finally
{

}

Unknown said...

private void fillTaskmappingGrid(DataTable tmpTable, string txtDate, string sltdLocation, string Client, string Project, string TaskType, string TaskActivity, string TaskMode, string taskDesc, string EstdHrs, string EstdMins, string actlHrs, string actlMins,string assignedby,string status,string remarks, string Created_by, string news)
{
dtr = tmpTable.NewRow();


dtr["new"] = news;

//dtr["inttaskId"] = "0";
dtr["workdate"] = cmf.convertDateIndia(txtDate.Trim()).ToString("yyyy-MM-dd");
dtr["location"] = sltdLocation.Trim();


tmpTable.Rows.Add(dtr);


}
#endregion

Unknown said...

#region GetNewRectoInsert
private void GetNewRectoInsert()
{
myDtNewRec = (DataTable)Session["tmpDataTable"];
DataView dv = new DataView(myDtNewRec);
dv.RowFilter = "new = 0";
myDtNewRec = dv.ToTable();
}
#endregion

Unknown said...

public int bulkInsertSchemeMaster(DataTable myDtNewRec)
{
con.OpenConnection();

int i = 0;

try
{
SqlBulkCopy bulkCopy = new SqlBulkCopy(con.GetConnection);

SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("workdate", "workdate");


bulkCopy.ColumnMappings.Add(mapping16);





bulkCopy.DestinationTableName = ("Daily_TaskSheet");
bulkCopy.WriteToServer(myDtNewRec);
i = 1;

}
catch (Exception ex)
{
i = 0;
}
finally
{
con.CloseConnection();
}

return i;

Unknown said...

if (BtnSave.Text == "Submit")
{

if (((DataTable)Session["tmpDataTable"]).Rows.Count > 0)
{

GetNewRectoInsert();

int status = tasksht.bulkInsertSchemeMaster(myDtNewRec);
if (status == 1)
{
lblresult.Text = "Inserted Successfully";
((DataTable)Session["tmpDataTable"]).Rows.Clear();
((DataTable)Session["tmpDataTable"]).Rows.Count.ToString();
grdDetails.Visible = false;
}

Unknown said...

#region createtable
protected DataTable createtable()
{
Session["dtMyTable"] = new DataTable("tblnamedb");
((DataTable)Session["dtMyTable"]).Columns.Add("new", typeof(string));
((DataTable)Session["dtMyTable"]).Columns.Add("workdate", typeof(string));
((DataTable)Session["dtMyTable"]).Columns.Add("created_by", typeof(string));

return ((DataTable)Session["dtMyTable"]);
}

Unknown said...

Hi, Suresh ,It helped me alot but the problem is after exporting to excel im getting links under excel header columns, please tell me how to avoid underlines / links.

Rajesh said...

hi

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Naveen Muruga said...

How to download multiple excel from multiple grid in a single button click.

Ukani Dharmendra said...

export gridview to excel how to add a formula column

Anonymous said...

Extender control 'txtInterviewFromDate_CalendarExtender' is not a registered extender control. Extender controls must be registered using RegisterExtenderControl() before calling RegisterScriptDescriptors()

i have this problem pls suggest me

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 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.