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.
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:
In previous articles I explained export gridview data to csv file in
asp.net,
export gridview data to pdf file in
asp.net,
export webpage with images to pdf in
asp.net, group columns in asp.net gridview header row and many articles
relating gridview,asp.net, c#, vb.net. Now I will explain
how to export data from gridview to excel in asp.net using c#, vb.net.
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 Email
|
|||
43 comments :
great!
this is very helpful assignment for database.
very nice
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
hi,sir this kohila,
the code is not work when we use other table controls in form tag......
please help me.
This is very helpfull to me,, OK Thanks ..
Its working for my project thanks
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
Hi,
I have added the following code, but it is not working...
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
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
how do we can add print functionality using asp or c#,net
Hi,
I get the following error:
RegisterForEventValidation can only be called during Render();
Can you advise what I'm doing wrong???
Thanks in advance.
NICE ARTICLE VERY HELPFUL.......
Very nice article bro Really It's Work
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
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..
How to set width for each column?
How to set Title of Grid programatically? asp.net
How to generate the excel and save it in a folder in server?
please give me code for above program by using LINQ
Grate
bt sir i want to set the excel cell property as text format how can i do this..???
hello sir this tanmay i want to export data to excel without using any loop can it be possible
please help me
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?
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
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);
Hello,
How can i rename sheet and also add new Sheet?
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?
How to create the VerifyRenderingInServerForm function. Please can you explain.
Please tell me how should i import data from gridview to excel in desktop application
pls explain this code
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
{
}
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
#region GetNewRectoInsert
private void GetNewRectoInsert()
{
myDtNewRec = (DataTable)Session["tmpDataTable"];
DataView dv = new DataView(myDtNewRec);
dv.RowFilter = "new = 0";
myDtNewRec = dv.ToTable();
}
#endregion
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;
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;
}
#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"]);
}
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.
hi
How to download multiple excel from multiple grid in a single button click.
export gridview to excel how to add a formula column
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
Note: Only a member of this blog may post a comment.